4

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

Improve this question
3
  • How do the users "submit" new jobs to the queue? Do the call dbms_scheduler directly, or does that go through some application API?
    – user1822
    Jul 6, 2012 at 9:54
  • It's a direct call to dbms_scheduler.
    – David Castle
    Jul 6, 2012 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.
    – user1822
    Jul 6, 2012 at 12:49

2 Answers 2

Reset to default
5

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.

Improve this answer
1
  • This sounds like exactly what we need. Thanks.
    – David Castle
    Jul 6, 2012 at 11:56
0

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 ...

Improve this answer
1
  • 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, 2012 at 11:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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