JAVA代码oracle删除用户,Oracle: 删除当前用户的所有对象(表、视图、存储过程、包、javasource,job)...

Oracle: 删除当前用户的所有对象(表、视图、存储过程、包、javasource,job)

by yanleigis email:landgis@126.com

说明:该函数在oracle10g专用，使用前要备份数据，数据删除后，数据不能恢复，一定要小心，小心，小心谨慎。

create or replace package pack_deleteall is

procedure deleteall;

end pack_deleteall;

create or replace package body pack_deleteall is

procedure deletetable is

--删除表和视图oracle10g专用 by yanleigis email:landgis@126.com

cursor c1 is

select t.tabtype, t.tname from tab t;

sqlstr varchar2(1024);

begin

for rr in c1 LOOP

begin

sqlstr := 'drop  ' || rr.tabtype || ' ' || rr.tname;

if rr.tabtype = 'TABLE' then

sqlstr := sqlstr || ' purge';

end if;

execute immediate sqlstr;

exception

when others then

begin

sqlstr := 'drop  ' || rr.tabtype || ' ' || rr.tname ||

' cascade constraints';

execute immediate sqlstr;

exception

when others then

begin

sqlstr := 'drop  ' || rr.tabtype || ' "' || rr.tname ||

'" cascade constraints'; --加"

execute immediate sqlstr;

exception

when others then

dbms_output.put_line(sqlstr || sqlcode || ':' || sqlerrm);

end;

end;

end;

END LOOP;

end;

procedure deletesource is

--删除存储过程，包，java source等等oracle10g专用 by yanleigis email:landgis@126.com

cursor c1 is

select b.object_type, b.object_name

from user_objects b

where b.object_type != 'PACKAGE BODY'

and b.object_name != upper('pack_deleteall');

sqlstr varchar2(1024);

begin

for rr in c1 LOOP

begin

sqlstr := 'drop ' || rr.object_type || ' ' || rr.object_name;

execute immediate sqlstr;

exception

when others then

begin

sqlstr := 'drop ' || rr.object_type || ' "' || rr.object_name || '"'; --创建时加'"

execute immediate sqlstr;

exception

when others then

dbms_output.put_line(sqlstr || sqlcode || ':' || sqlerrm);

end;

end;

end loop;

end;

procedure deletejob is

---删除存jjob oracle10g专用 by yanleigis email:landgis@126.com

cursor c1 is

select b.JOB from user_jobs b;

begin

for rr in c1 LOOP

begin

dbms_job.remove(rr.job);

exception

when others then

dbms_output.put_line(sqlcode || ':' || sqlerrm);

end;

end loop;

end;

--删除procedure

procedure deleteall is

begin

deletetable;

deletesource;

deletejob;

commit;

execute immediate 'purge recyclebin';

end;

end pack_deleteall;