Has my scheduler been turned off?

Posted by

When the scheduler came into existence in Oracle 10g, there was a cool API call that could be used to temporarily turn the entire scheduler off.  That command was:


SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE')

PL/SQL procedure successfully completed.

However, a quick check of the documentation in later releases, no longer makes any reference to this attribute:

image

I did some testing, and if you call the SET_SCHEDULER_ATTRIBUTE to disable the scheduler in 12c onwards, it still does seem to have effect of turning off the scheduler. So the question is – why is it no longer there in the documentation?

The reason is that the effects of the attribute were never totally clear – for example, would it stop DBMS_JOB? Would it stop running jobs? What if someone issued an explicit RUN_JOB command? What about the automatic task infrastructure that submits scheduler jobs on the database’s behalf?

Since 12c, the supported way to turn off the scheduler is now to set job_queue_processes to zero. You should not use the SCHEDULER_DISABLED attribute.

Because it is no longer supported, that raises an interesting issue. What if someone is still calling it? If you attempt to retrieve the attribute, we return null in all instances:


SQL> variable x varchar2(10)
SQL> exec dbms_scheduler.get_scheduler_attribute('SCHEDULER_DISABLED',:x)

PL/SQL procedure successfully completed.

SQL> print x

X
--------------------------------
(null)

Luckily, we do still expose it in the dictionary, so you can easily double check:


SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE')

PL/SQL procedure successfully completed.

SQL> select * from DBA_SCHEDULER_GLOBAL_ATTRIBUTE;

ATTRIBUTE_NAME                           VALUE
---------------------------------------- ----------------------------------------
MAX_JOB_SLAVE_PROCESSES
LOG_HISTORY                              30
DEFAULT_TIMEZONE                         Australia/Perth
EMAIL_SERVER
EMAIL_SERVER_ENCRYPTION                  NONE
EMAIL_SERVER_CREDENTIAL
EMAIL_SENDER
LAST_OBSERVED_EVENT
EVENT_EXPIRY_TIME
FILE_WATCHER_COUNT                       0
CURRENT_OPEN_WINDOW
SCHEDULER_DISABLED                       TRUE   

2 comments

  1. Hi Connor,

    There is yet another way to stop jobs from running that I have used in several environments.
    Jobs are assigned to job classes which are configured to use dedicated database services.
    For example, I can have a service ‘batch’ and a job class that is configured to run its jobs under that service.
    If I want to prevent those jobs from starting, I can just stop that service.

    It might be a good idea to have more granular control over application jobs but do not stop all jobs; scheduler job classes can be a good option in such scenarios.
    Here are a couple examples from the field:
    1. I had to stop application jobs but I was going to run @?/rdbms/admin/utlrp (utl_recomp.recomp_parallel) which utilizes DBMS_SCHEDULER.
    2. Some of database deployments happened during a downtime window when application jobs had to be stopped. Occasionally developers provided deployment scripts utilizing DBMS_PARALLEL_EXECUTE, which internally uses DBMS_SCHEDULER for its slaves.

    Regards,
    Mikhail.

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.