我 的 Weblog

版权声明：转载时请以超链接形式标明文章原始出处和作者信息及本声明
http://snk.blogbus.com/logs/12757104.html

drop all object

需求：为了保证开发环境的统一，db  必须能被重复setup，所以在setup db 前需要清空原有的objective。

------------------------------------------------------------------  

oracle 10g 有回收站，所以drop object 的时候要小心，已经在回收站的 object 是不能被 drop的。

方法一（只支持10g）：

SPOOL Drop_All_Objects.log
--drop 'TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE'
BEGIN
  FOR cur_rec IN (SELECT object_name, object_type
                  FROM   user_objects
                  WHERE  object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')) LOOP
    BEGIN
      IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS PURGE ';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
      END IF;
    END;
  END LOOP;

--drop public synonym

  FOR cur_rec IN (SELECT synonym_name
                  FROM   dba_synonyms
                  WHERE  table_owner = '&&OWNER_NAME' and owner = 'PUBLIC') LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM ' || cur_rec.synonym_name ;
    END;
  END LOOP;
END;
/

SPOOL OFF;
Exit;
 

方法二：
SET SERVEROUTPUT ON SIZE 1000000
BEGIN
  FOR cur_rec IN (SELECT object_name, object_type
                  FROM   user_objects
                  WHERE  object_type IN ('TABLE', 'VIEW', 'PACKAGE', 'PROCEDURE', 'FUNCTION', 'SEQUENCE')) LOOP
    BEGIN
      IF cur_rec.object_type = 'TABLE' THEN
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '" CASCADE CONSTRAINTS';
      ELSE
        EXECUTE IMMEDIATE 'DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"';
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.put_line('FAILED: DROP ' || cur_rec.object_type || ' "' || cur_rec.object_name || '"');
    END;
  END LOOP;
END;
/

方法三：

set trimspool on
set pagesize 0
set line 1000
set feed off
set verify off

spool genera_dropall.sql
select 'prompt Conectando como &&OWNER' || chr(10) || 'connect &&OWNER' DROP_OBJECTS
from dual
union all
select 'spool dropall.log' DROP_OBJECTS
from dual
union all
Select
trim(case
     when object_type = 'TABLE' then 'drop ' || object_type
          || ' ' || owner || '.' || object_name ||' cascade constraints'
     when object_type = 'PACKAGE BODY' then 'prompt PACKAGES BODY'
     when object_type = 'INDEX' then 'prompt INDEXES'
     when object_type = 'DATABASE LINK' then 'drop ' || object_type
          || ' ' || object_name  
     else 'drop ' || object_type || ' ' || owner || '.' || object_name
     end || ';')
from dba_objects
where owner = '&&OWNER'
and object_type not like '%PARTITION%'
union all
select 'drop public synonym '||synonym_name||';'
from dba_synonyms
where table_owner = '&&OWNER'
and owner = 'PUBLIC'
union all
select 'spool off'
from dual
;
spool off

exit