Skip to Main Content
  • Questions

  • Disabling DBMS_SCHEDULER jobs on import

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Peter.

Asked: November 14, 2017 - 4:22 pm UTC

Last updated: January 20, 2020 - 2:54 am UTC

Version: 11.2.0.4.0

Viewed 10K+ times! This question is

You Asked

We refresh our test and development environments from production weekly. Is there a way to automatically disable DBMS_SCHEDULER jobs on import? I have a "reset" script that I run after the refresh, but "overdue" jobs seem to run immediately after import completes.

and Connor said...

Options you could consider:

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

PL/SQL procedure successfully completed.

SQL> exec dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','FALSE');

PL/SQL procedure successfully completed.


or change the database ability to run jobs

SQL> alter system set job_queue_processes = 0;

System altered.

SQL> alter system set job_queue_processes = 4000;

System altered.


Rating

  (4 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

When and Where?

Peter Nosko, November 15, 2017 - 2:31 am UTC

Do you mean in the instance where we're about to do a full db import, just before doing it? If so, won't the import itself override these settings?
Connor McDonald
November 16, 2017 - 1:25 pm UTC

An import will definitely not change init.ora parameters.

init.ora

Peter Nosko, November 19, 2017 - 1:21 am UTC

Just to be absolutely clear, where are the attributes set by dbms_scheduler.set_scheduler_attribute stored? Are they affected by a full database import?
Connor McDonald
November 20, 2017 - 1:38 am UTC

Sorry, I gave you bad advice.

Even if SCHEDULER_DISABLED works across imports, I just discovered it is no longer documented, so use job_queue_processes instead.

Many Thanks

Peter Nosko, November 20, 2017 - 3:11 am UTC

Thanks, Connor.

A question

Abraham Olsen, January 16, 2020 - 11:25 am UTC

I like the use of "
dbms_scheduler.set_scheduler_attribute('SCHEDULER_DISABLED','TRUE')
" to control the scheduler in some cases.

But I cannot find out, how to check if the attribute is TRUE or FALSE.
Is there a data-dictionary view or similar, or a "get" API, where this can be seen?
A "dbms_scheduler.get_scheduler_attribute('SCHEDULER_DISABLED');" could be handy
Connor McDonald
January 20, 2020 - 2:54 am UTC

You mean like this? :-)

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


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database