Script to disable all jobs in Oracle Database (all editions)

If you want to prevent all jobs from running, you can change the initialization parameter JOB_QUEUE_PROCESSES. If you set that to 0, Oracle won’t run any jobs scheduled using DBMS_JOB.

For dbms_job jobs:

alter system set job_queue_processes=0 scope=both;

For some maintenance may be better/ You may normally want to have some jobs offline and don’t want to put them online when you’ll be done with maintenance.

For dbms_scheduler jobs:

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');

and after maintenance is complete:

exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

You could also mark the jobs broken

Normal user:

BEGIN
  FOR x IN (SELECT * FROM all_jobs)
  LOOP
    dbms_job.broken( x.job, true );
  END LOOP;
END;

Dba user:

BEGIN
  FOR x IN (SELECT * FROM all_jobs)
  LOOP
    dbms_job.broken( x.job, true );
  END LOOP;
END;

which will cause them not to be run (but will allow any jobs created after that point to run normally). To unbreak the jobs

Normal user:

BEGIN
  FOR x IN (SELECT * FROM all_jobs)
  LOOP
    dbms_job.broken( x.job, false, SYSDATE + interval '1' minute);
  END LOOP;
END;

Dba user:

BEGIN
  FOR x IN (SELECT * FROM all_jobs)
  LOOP
    dbms_ijob.broken( x.job, false, SYSDATE + interval '1' minute);
  END LOOP;
END;

will set all the jobs to run in 1 minute.

Leave a Reply

Your email address will not be published. Required fields are marked *