Tuesday, February 16, 2016

How to remove dbms_scheduler jobs

To delete an obsolete scheduler job, use the procedure dbms_scheduler.drop_job procedure.
In a recent case, a large number of obsolete dbms_Scheduler jobs were laying around in the database, and needed a cleanup.
Most of them were not enabled, and all stakeholders deemed it safe to remove the jobs.

To loop through the jobs owned by the schema, I used the following simple script:
connect scott/tiger
set serveroutput on
set trimspool on
spool /tmp/drop_mva_scheduler_jobs.log
DECLARE

  CURSOR C1 IS
  SELECT JOB_NAME
  FROM USER_SCHEDULER_JOBS
  WHERE ENABLED='FALSE';

BEGIN
 FOR l_rec IN C1 LOOP
   dbms_output.put_line('Now dropping job: ' || l_rec.job_name);
   dbms_scheduler.drop_job(job_name=>'' || l_rec.job_name || '' );
 END LOOP;
END;
/
exit


Sources: Oracle Documentation

No comments:

Post a Comment