Home > Uncategorized > Oracle: How to (comfortably) disable Oracle Scheduler job execution

Oracle: How to (comfortably) disable Oracle Scheduler job execution

Problem description:

You want to disable the execution of all Oracle Scheduler jobs by a simple command. With Oracle Jobs (pre-Oracle-Scheduler-era) you where able to disable any job execution by simply setting the job_queue_processes parameters to 0:

* Disabling job execution:
 
ALTER SYSTEM SET job_queue_processes = 0;
 
* Enabling job execution (job_queue_processes > 0, e.g. 10):
 
ALTER SYSTEM SET job_queue_processes = 10;

Oracle Scheduler has no instance parameter which defines the number of job processes.

 
Problem resolution:

Although there is no entry in the documentation for disabling the Oracle Scheduler, you can stop the Scheduler job execution by using an undocumented attribute called “SCHEDULER_DISABLED” when executing DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE:

  • Disable Oracle Scheduler job execution
    SQL> EXEC dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE');
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>

     

  • Disable Oracle Scheduler job execution
    SQL> EXEC dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>

The current Scheduler job execution state can be checked by querying the Data Dictionary view DBA_SCHEDULER_GLOBAL_ATTRIBUTE:

  • Example for a disabled Scheduler
    SQL> col VALUE FOR a40
    SQL> SELECT * FROM dba_scheduler_global_attribute WHERE attribute_name = 'SCHEDULER_DISABLED';
     
    ATTRIBUTE_NAME		       VALUE
    ------------------------------ ----------------------------------------
    SCHEDULER_DISABLED	       TRUE
     
    SQL>

     

  • Example for an enabled Scheduler
    SQL> col VALUE FOR a40
    SQL> SELECT * FROM dba_scheduler_global_attribute WHERE attribute_name = 'SCHEDULER_DISABLED';
     
    no ROWS selected
     
    SQL>

 
Notes:

Please be informed that the usage of DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(‘SCHEDULER_DISABLED’, ‘TRUE|FALSE’) is currently undocumented and therefore “SCHEDULER_DISABLED” is not a supported scheduler attribute. It should not have a detrimental effect on your system(s), but just in case, as it is with unsupported/undocumented features, we take no responsibility for any damages incurred from the use of the information contained herein.

Categories: Uncategorized Tags:
  1. No comments yet.
  1. No trackbacks yet.

Connect with Facebook