Oracle Opening a Scheduler Window Oracle Tips by Burleson Consulting

This is an excerpt from "Oracle 10g New Features for Administrators" by Ahmed Baraka.

• A window will automatically open at a time specified by its START_TIME attribute.

• Only one window can be open at any given time.

• A window can be manually opened:

DBMS_SCHEDULER.OPEN_WINDOW (
WINDOW_NAME =>'BACKUP_WINDOW',
DURATION => '0 12:00:00')

When you specify the duration, you can specify days, hours, minutes, seconds, in that order.

• You can open an already open window. If you dothis, the duration of the window will    last a timeperiod equal to its duration attribute.

Closing a Window

DBMS_SCHEDULER.CLOSE_WINDOW('BACKUP_WINDOW')

A running job may close upon the closing of its window,if you create a job with the attribute STOP_ON_WINDOW_CLOSE set to TRUE.

Disabling a Window

• You can only disable a window if no job uses that window or if the window    is not open.

• If the window is open, you can disable it by using the DISABLE with the   FORCE=TRUE attribute.

DBMS_SCHEDULER.DISABLE (NAME =>
'BACKUP_WINDOW')

Dropping a Window

• You can drop a window by using the DROP_WINDOW procedure.

• If a job associated with a window is running, a DROP_WINDOW procedure will continue to run through to completion and is disabled after it completes.

• If you set the STOP_ON_WINDOW_CLOSE attribute to TRUE, however, the job will immediately stop when you drop an associated window.

Prioritizing Jobs

• You can prioritize jobs at two levels: class and job.

• The prioritization at the class level is based on the resources allocated to each resource consumer group by the currently active resource plan. The consumer group that a job class maps to can be specified when creating a job class.

• At job level, the job priority ranges from 1 to 5, with 1 being the highest priority and 3 being the default.

• When you have more than one job within the same class scheduled for the same time, the JOB_PRIORITY of the individual jobs determines which job starts first.

DBMS_SCHEDULER.SET_ATTRIBUTE (
NAME => 'test_job',
ATTRIBUTE => 'job_priority',
VALUE => 1)

Window Priorities

If there are more than one window to open at the same time, the Scheduler will close all windows except one, using the following rules of precedence:

o If two windows overlap, the window with the higher priority opens and the   window with the lower priority closes.

o If two windows of the same priority overlap, the active window remains    open.

o If you are at the end of a window and you have other windows defined for    the same time period, the window that has the highest percentage of time    remaining will open.

Window Groups

• A window group is a collection of windows, and is part of the schema.

• The concept of a window group is for convenience only, and its use purely    optional.

Unsetting Component Attributes

DBMS_SCHEDULER.SET_ATTRIBUTE_NULL('test_program
', 'COMMENTS')

Altering Common Component Attributes

• There are some attributes that are common to all Scheduler components.

• Use the procedure SET_SCHEDULER_ATTRIBUTE to set these    common, or global level, attributes.

• These are the global attributes:

DEFAULT_TIMEZONE

If jobs and windows specifications use the calendering syntax but omit the start date, the Scheduler derives the time zone from the DEFAULT_TIMEZONE attribute.

Oracle recommends that you set the DEFAULT_TIMEZONE attribute to a region’s name instead of absolute time zone offset, in order to ensure that daylight saving adjustments are being taken into account.

LOG_HISTORY  - This attribute refers to the number of days the Scheduler will retain job and window logs.

MAX_JOB_SLAVE_PROCESSES - The Scheduler determines the optimal number of job slave processes, based on your processing requirements. However, you can set a limit on the number of job slave processes using the

MAX_JOB_SLAVE_PROCESSES -  attribute, whose default value is NULL, and the range is from 1 to 999.

 

If you like Oracle tuning, see the book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts.  You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.