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.

Lanielanier

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.

Balladry

Just a plus, using dbms_job is limited to jobs for the own user... if you want to disable jobs from all users , you must login as administrator and use dbms_ijob . – Erlin

Be aware that dbms_job is now obsolete and dbms_scheduler is its replacement. – Diagnostician

Is there a way to preserve the original schedule and not start all jobs like an avalanche? – Salomie

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

Shultz

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.

Nisan

Answer 4

DECLARE
    CURSOR selection
    IS SELECT job_name FROM dba_scheduler_jobs WHERE owner = '[username]';
    --or make your own selection here
    
BEGIN
    FOR record IN selection
    LOOP
        dbms_scheduler.disable(record.job_name); 
    END LOOP;
END;

Commotion