Disable Oracle DBMS Scheduler

July 23, 2020 2-minute read

Quick Tip

Sometimes you need to prevent all scheduled jobs to run in order to perform some maintenance or installation. Commonly you set the parameter JOB_QUEUE_PROCESSES to zero but you can also disable the DBMS_SCHEDULER.

Disable DBMS Scheduler

You can disable the Scheduler by setting the SCHEDULER_DISABLED attribute to TRUE:

SQL> exec dbms_scheduler.set_scheduler_attribute ( 'SCHEDULER_DISABLED', 'TRUE' );
PL/SQL procedure successfully completed.

SQL> select value from dba_scheduler_global_attribute where attribute_name = 'SCHEDULER_DISABLED';
VALUE
----------
TRUE

In order to validate we can send to run a job using the option use_current_session as FALSE, as described in the DBMS_SCHEDULER Documentation:

When use_current_session is FALSE, RUN_JOB returns immediately and the job is picked up by the job coordinator and passed on to a job slave for execution. The Scheduler views and logs must be queried for the outcome of the job.

SQL> exec dbms_scheduler.run_job(job_name => 'PURGE_LOG', use_current_session => FALSE);
BEGIN dbms_scheduler.run_job(job_name => 'PURGE_LOG', use_current_session => FALSE); END;

*
ERROR at line 1:
ORA-27492: unable to run job "SYS"."PURGE_LOG": scheduler unavailable
ORA-06512: at "SYS.DBMS_ISCHED", line 242
ORA-06512: at "SYS.DBMS_SCHEDULER", line 566
ORA-06512: at line 1

As we can see we received an error stating scheduler unavailable.

Reenable DBMS Scheduler

In order to enable again the Scheduler, set the SCHEDULER_DISABLED attribute to FALSE:

SQL> exec dbms_scheduler.set_scheduler_attribute ( 'SCHEDULER_DISABLED', 'FALSE');
PL/SQL procedure successfully completed.

SQL> select value from dba_scheduler_global_attribute where attribute_name = 'SCHEDULER_DISABLED';
no rows selected

and now we can send jobs to run in the Scheduler:

SQL> exec dbms_scheduler.run_job(job_name => 'PURGE_LOG', use_current_session => FALSE);

PL/SQL procedure successfully completed.


SQL> select job_name, status, error#, actual_start_date from dba_scheduler_job_run_details where owner = 'SYS' and job_name = 'PURGE_LOG' order by 4 desc;

JOB_NAME                       STATUS                             ERROR# ACTUAL_START_DATE
------------------------------ ------------------------------ ---------- ---------------------------------------------------------------------------
PURGE_LOG                      SUCCEEDED                               0 23-JUL-20 10.58.20.841421 AM PST8PDT
...