Script to disable all jobs in Oracle

      No Comments on Script to disable all jobs in Oracle

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 *