Oracle清空数据库所有对象（慎用）

create or replace PROCEDURE P_CLEANDB AS

 -- 删除表
 CURSOR L1 IS  select 'drop table '||table_name  from cat where table_type='TABLE'  and table_name not like 'BIN%';  
         L101 VARCHAR2(1000); 
  -- 删除视图
  CURSOR L2 IS  select 'drop view '||object_name from user_objects where object_type='VIEW';  
         L201 VARCHAR2(1000); 
  -- 删除函数
  CURSOR L3 IS select 'drop function '||object_name  from user_objects where object_type='FUNCTION';
   L301 VARCHAR2(1000); 
  -- 删除索引
  CURSOR L4 IS select 'drop index '||object_name  from user_objects where object_type='INDEX' AND OBJECT_NAME not like '%$%';
   L401 VARCHAR2(1000); 
  --删除 过程 不包含自己
  CURSOR L5 IS select 'drop procedure '||object_name  from user_objects where object_type='PROCEDURE' AND OBJECT_NAME<>'P_CLEANDB';
  L501 VARCHAR2(1000); 
  --删除序列
    CURSOR L6 IS select 'drop sequence '||SEQUENCE_NAME  from USER_SEQUENCES ;
   L601 VARCHAR2(1000); 
  --删除dblink
   CURSOR L7 IS select 'drop database link  '||object_name from user_objects where object_type='DATABASE LINK';
   L701 VARCHAR2(1000); 
   
   --删除约束
   CURSOR L8 IS select 'alter table '||table_name||' drop constraint   '||CONSTRAINT_NAME from USER_CONSTRAINTS where constraint_type='R' AND CONSTRAINT_NAME not like '%$%';
   L801 VARCHAR2(1000); 
   
   CURSOR L10 IS select 'alter table '||table_name||' drop constraint   '||CONSTRAINT_NAME from USER_CONSTRAINTS where  CONSTRAINT_NAME not like '%$%';
   L1001 VARCHAR2(1000);
   
   CURSOR L9 IS SELECT 'DROP  MATERIALIZED VIEW  '||MVIEW_NAME  FROM   USER_MVIEWS;
   L901 VARCHAR2(1000);
BEGIN  
  
 OPEN L9;  
        LOOP  
            FETCH L9 INTO L901;  
            EXIT WHEN L9%NOTFOUND;                  
                EXECUTE IMMEDIATE L901;  
                COMMIT;  
        END LOOP;  
      CLOSE L9;
  
  
  OPEN L8;  
        LOOP  
            FETCH L8 INTO L801;  
            EXIT WHEN L8%NOTFOUND;                  
                EXECUTE IMMEDIATE L801;  
                COMMIT;  
        END LOOP;  
      CLOSE L8;
      
          
       OPEN L10;  
        LOOP  
            FETCH L10 INTO L1001;  
            EXIT WHEN L10%NOTFOUND; 
                --dbms_output.put_line(L1001);
                EXECUTE IMMEDIATE L1001;  
                COMMIT;  
        END LOOP;  
      CLOSE L10;
      
      
  
   OPEN L4;  
        LOOP  
            FETCH L4 INTO L401;  
            EXIT WHEN L4%NOTFOUND;  
                --DBMS_OUTPUT.PUT_LINE(L401);
                EXECUTE IMMEDIATE L401;  
                COMMIT;  
        END LOOP;  
      CLOSE L4;
  
      
      
      
       OPEN L3;  
        LOOP  
            FETCH L3 INTO L301;  
            EXIT WHEN L3%NOTFOUND;  
                EXECUTE IMMEDIATE L301;  
                COMMIT;  
        END LOOP;  
      CLOSE L3;      
           
       OPEN L5;  
        LOOP  
            FETCH L5 INTO L501;  
            EXIT WHEN L5%NOTFOUND;  
                EXECUTE IMMEDIATE L501;  
                COMMIT;  
        END LOOP;  
      CLOSE L5;
      
       OPEN L6;  
        LOOP  
            FETCH L6 INTO L601;  
            EXIT WHEN L6%NOTFOUND;  
                EXECUTE IMMEDIATE L601;  
                COMMIT;  
        END LOOP;  
      CLOSE L6;
      
     

              
      OPEN L2;  
        LOOP  
            FETCH L2 INTO L201;  
            EXIT WHEN L2%NOTFOUND;  
                EXECUTE IMMEDIATE L201;  
                COMMIT;  
        END LOOP;  
      CLOSE L2;
      
        
   OPEN L1;  
        LOOP  
            FETCH L1 INTO L101;  
            EXIT WHEN L1%NOTFOUND;  
                EXECUTE IMMEDIATE L101;  
                COMMIT;  
        END LOOP;  
      CLOSE L1;
      
            
      
      
END P_CLEANDB;