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;