Friday, May 4, 2012

Drop All Database Objects


BEGIN
  FOR cur_rec IN (SELECT table_owner, table_name, constraint_name FROM dba_constraints
                  WHERE  constraint_type = 'R' and table_owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
    EXECUTE IMMEDIATE 'ALTER TABLE ' || cur_rec.table_owner|| '.'  || cur_rec.table_name || ' DROP CONSTRAINT ' || cur_rec.constraint_name;
  END LOOP;
  FOR cur_rec IN (SELECT owner, object_name, object_type
                  FROM   dba_objects where owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
    BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.owner|| '.'  || cur_rec.object_type || ' ' || cur_rec.object_name;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
FOR cur_rec IN (SELECT owner,synonym_name,table_owner FROM  dba_synonyms where table_owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
BEGIN
      EXECUTE IMMEDIATE 'DROP ' || cur_rec.owner || ' SYNONYM ' || cur_rec.table_owner || '.' || cur_rec.synonym_name || ' FORCE';
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
FOR cur_rec IN (SELECT owner, queue_table FROM dba_queue_tables where owner not in ('DBSNMP','ORACLE_OCM','OUTLN','SYS','SYSMAN','SYSTEM','TSMSYS','WMSYS','XDB')) LOOP
    BEGIN
    DBMS_AQADM.DROP_QUEUE_TABLE('' || cur_rec.owner || '' || '.' || '' || cur_rec.queue_table || '',force =>true);
    EXCEPTION
      WHEN OTHERS THEN
        NULL;
    END;
  END LOOP;
END;
/

No comments:

Post a Comment

Followers