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.