Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
How do the users "submit" new jobs to the queue? Do the call dbms_scheduler directly, or does that go through some application API? –  a_horse_with_no_name Jul 6 '12 at 9:54
    
It's a direct call to dbms_scheduler. –  David Castle Jul 6 '12 at 11:56
    
I though about revoking the execute privilege for those users during that time, But the scheduler window solution from Vincent is much better. –  a_horse_with_no_name Jul 6 '12 at 12:49

2 Answers 2

up vote 4 down vote accepted

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

  1. 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.
  2. modify your job submission so that they run under this window (parameter schedule_name of the DBMS_SCHEDULER.create_job procedure).
  3. 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.

share|improve this answer
    
This sounds like exactly what we need. Thanks. –  David Castle Jul 6 '12 at 11:56

You should use Oracle Resource Manager to throttle the load caused by scheduler jobs in such a way that they do not interfere with the online users.

An other way to make such type of a switch is to tie your jobs via a job class to a service and disable that service. See CREATE_JOB_CLASS Procedure

This is also very convenient if you want to run import from a database that had enabled jobs and you do not want them to start running at import time ...

share|improve this answer
    
Certainly getting the balance of load right day to day is our main aim, this is just something that we occasionally need. –  David Castle Jul 6 '12 at 11:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.