Disabling DBMS_SCHEDULER queue

Despite a lot of searching, I can't seem to find a solution to this problem we are having.

Our system enables users to submit certain processes to be run in background while they continue working. These processes are submitted as jobs using DBMS_SCHEDULER and we have a couple of job classes for serial and parallel processing depending on the job the user needs doing.

When we take the system down for maintenance, we stop running jobs and reenter them on the queue, and disable all scheduled jobs. This is fine for a standard maintenance window as without our system online, users have no ability to submit new jobs. After maintenance, all jobs are re-enabled and everyone's happy.

However, there are times when we would like to be able to disable the queue while the system is still live - often to free up some resources in peak times. The only thing we can do at the moment is disable all jobs currently scheduled, but obviously that doesn't stop people submitting new jobs to the queue.

Does anyone know of a way to either

• stop people entering jobs on the queue

• force any new jobs to be entered on the queue in a disabled state (preferred option)?

Many thanks in advance

I can't test it right now but I think you could do this with open/close scheduler window:

• first define a window with DBMS_SCHEDULER.create_window. This window could have a 24 hour duration and repeat every day so that it is always open.

• modify your job submission so that they run under this window (parameter schedule_name of the DBMS_SCHEDULER.create_job procedure).

• when you need to enter maintenance and/or release resource, use DBMS_SCHEDULER.close_window. When your maintenance is done, use DBMS_SCHEDULER.open_window.

The jobs submitted while the window is closed should be queued and run later when the window is opened.

StackExchange Database Administrators Q#20488, answer score: 5