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