SET TIMING OFF HEAD OFF LINE 300 TRIMSPOOL ON FEEDBACK OFF ECHO OFF spool /tmp/drop_all_script.sql SELECT 'DROP ' ||object_type||' '||object_name ||' CASCADE CONSTRAINTS PURGE;' FROM user_objects WHERE object_type IN ('TABLE') UNION ALL SELECT 'DROP ' ||object_type||' '||object_name ||';' FROM user_objects WHERE object_type NOT IN ('INDEX','TABLE','TABLE PARTITION','TABLE SUBPARTITION', 'INDEX PARTITION','INDEX SUBPARTITION','TRIGGER','PROGRAM','QUEUE','JOB') UNION ALL SELECT 'EXECUTE dbms_aqadm.stop_queue('''||name||''');' FROM user_queues WHERE queue_type='NORMAL_QUEUE' UNION ALL SELECT 'EXECUTE dbms_aqadm.drop_queue('''||name||''', TRUE);' FROM user_queues WHERE queue_type='NORMAL_QUEUE' UNION ALL SELECT 'EXECUTE dbms_aqadm.drop_queue_table('''||queue_table||''', TRUE);' FROM user_queues WHERE queue_type='NORMAL_QUEUE' UNION ALL SELECT 'EXECUTE dbms_scheduler.drop_program('''||program_name||''',TRUE);' FROM user_scheduler_programs UNION ALL SELECT 'EXECUTE dbms_scheduler.drop_job('''||object_name||''',TRUE);' FROM user_objects where object_type='JOB'; spool off SET ECHO ON FEEDBACK ON @/tmp/drop_all_script.sql BEGIN FOR JOB_REC IN (SELECT * FROM USER_SCHEDULER_JOBS WHERE JOB_NAME LIKE 'COLO_JOB%') LOOP DBMS_SCHEDULER.DROP_JOB(JOB_REC.JOB_NAME, TRUE); END LOOP;
FOR CHAIN_REC IN (SELECT * FROM USER_SCHEDULER_CHAINS WHERE CHAIN_NAME LIKE 'COLO%') LOOP DBMS_SCHEDULER.DROP_CHAIN(CHAIN_REC.CHAIN_NAME, TRUE); END LOOP;
FOR PROGRAM_REC IN (SELECT * FROM USER_SCHEDULER_PROGRAMS WHERE PROGRAM_NAME LIKE 'COLO%') LOOP DBMS_SCHEDULER.DROP_PROGRAM(PROGRAM_REC.PROGRAM_NAME, TRUE); END LOOP; END; / !rm /tmp/drop_all_script.sql
SELECT count(*) obj_cnt FROM user_objects;
|