Script to disable all jobs in Oracle (DBMS_JOB package)?

Question

I'm looking for a script which disables all the jobs. Right now I highlight them all in Toad, click the take offline button and then commit changes. There has to be a way to do this in PL/SQL.

Answer 1

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.

You could also mark the jobs broken

BEGIN
  FOR x IN (SELECT * FROM user_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

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

will set all the jobs to run in 1 minute.

Answer 2

== 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');

Answer 3

Please run the below query.

set head off
spool job_disable.sql
select 'execute dbms_scheduler.disable('||''''||owner||'.'||job_name||''''||');' from dba_scheduler_jobs where enabled='TRUE';
spool off;
@job_disable.sql

This will disable all the dbms jobs that are enabled.

You can modify the query to enable all the disabled too.