7

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.

Cade Roux
  • 88,164
  • 40
  • 182
  • 265

4 Answers4

14

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • 1
    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` . – ceinmart Aug 21 '13 at 19:26
  • Be aware that `dbms_job` is now obsolete and `dbms_scheduler` is its replacement. – Mark Stewart Nov 05 '18 at 16:24
  • Is there a way to preserve the original schedule and not start all jobs like an avalanche? – basin Oct 06 '21 at 13:18
8

== 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');
Tagar
  • 13,911
  • 6
  • 95
  • 110
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.

halfzebra
  • 6,771
  • 4
  • 32
  • 47
Rakesh Nunna
  • 41
  • 3
1
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;
4b0
  • 21,981
  • 30
  • 95
  • 142
mART
  • 31
  • 2