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;