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;