Oracle Managing Advanced Scheduler Components

Oracle Tips by Burleson Consulting

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

Creating a Job Class

� Using job classes helps you prioritize jobs by allocating resources differently among the various jobs.

� All job classes are created in the SYS schema. To create a job class you need MANAGE SCHEDULER privilege.

� For users to create jobs that belong to a job class, the job owner must have EXECUTE privileges on the job class.

� There is a default job class, DEFAULT_JOB_CLASS, to which all jobs will belong if you don�t explicitly assign them to a job class.

DBMS_SCHEDULER.CREATE_JOB_CLASS (
JOB_CLASS_NAME => 'ADMIN_JOBS',
RESOURCE_CONSUMER_GROUP => 'ADMIN_GROUP',
LOGGING_LEVEL => DBMS_SCHEDULER.LOGGING_OFF
LOGGING_HISTORY => 30,
COMMENTS => 'Admin related jobs.')

LOGGING_LEVEL - This attribute specifies how much information is logged. The three possible options are:

o DBMS_SCHEDULER.LOGGING_OFF
o DBMS_SCHEDULER.LOGGING_RUNS
o DBMS_SCHEDULER.LOGGING_FULL:

In addition to recording every run of a job, the Scheduler will log every time a job is created, enabled, disabled, altered, and so on.

Note: As a DBA, you can set logging at the job class level in order to audit Scheduler jobs. In this case, an individual user can only increase the amount of logging the individual job level.

LOGGING_HISTORY - Specifies the number of days (default is 30) that the database will retain the logs before purging them.

Oracle will automatically create a daily job called the PURGE_LOG, which cleans the log entries.

Manually Purging a Job Class Log

By default, once a day, the Scheduler will purge all window logs and job logs that are older than 30 days.

DBMS_SCHEDULER.PURGE_LOG(LOG_HISTORY=7,
WHICH_LOG =>'JOB_LOG')

LOG_HISTORY - This specifies how much history (in days) to keep. The valid range is 0-999. If set to 0, no history is kept.

WHICH_LOG - This specifies which type of log. Valid values are: JOB_LOG, WINDOW_LOG, AND JOB_AND_WINDOW_LOG.

You can purge log of a specific job:

DBMS_SCHEDULER.PURGE_LOG (
LOG_HISTORY => 1,
JOB_NAME => 'TEST_JOB1')

You can modify the retention period (the default is 30 days) of the logs for a job class:

DBMS_SCHEDULER.SET_ATTRIBUTE(
'TEST_JOB_CLASS', 'log_history', '7')

In order to clear all window and job logs:

DBMS_SCHEDULER.PURGE_LOG()

Altering a Job Class

DBMS_SCHEDULER.SET_ATTRIBUTE(
NAME => 'ADMIN_JOBS',
ATTRIBUTE => 'START_DATE',
VALUE => '01-JAN-2005 9:00:00 PM US/Pacific')

You can change the START_DATE, END_DATE, and other logging-related attributes as well.

Dropping a Job Class

DBMS_SCHEDULER.DROP_JOB_CLASS('TEST_CLASS')

If you want to drop a job class with jobs in it, you must specify the FORCE=TRUE option in your DROP_JOB_CLASS procedure. When you do this, the jobs in the dropped job class are disabled and moved to the default job class in your database. If the job is already running when you drop its job class, the job will run to completion anyway.

Working with Scheduler Windows

Windows enable the automatic changing of resource plans based on a schedule.

Creating a Window

� Windows are always created in the SYS schema.

� To create a window, you must have the MANAGE

SCHEDULER system privilege.

� A window is automatically enabled upon its creation.

DBMS_SCHEDULER.CREATE_WINDOW (
WINDOW_NAME => 'TEST_WINDOW',
START_DATE => '01-JAN-05 12:00:00AM',
REPEAT_INTERVAL => 'FREQ=DAILY',
RESOURCE_PLAN => 'TEST_RESOURCEPLAN',
DURATION => INTERVAL '60' MINUTE,
END_DATE => '31-DEC-05 12:00:00AM',
WINDOW_PRIORITY => 'HIGH',
COMMENTS => 'Test Window')

START_DATE	Time when the Window will open.
REPEAT_INTERVAL	The next time the window will open again.
RESOURCE_PLAN	Tells us that while this window is open, resource allocation to all the jobs that run in this window will be guided by the resource plan directives in the resource plan TEST_RESOURCEPLAN.
DURATION	Window will remain open for a period of 60 minutes, after which it will close.
END_DATE	Window will open for the last time on December 31, 2005, after which it will be disabled and closed.
WINDOW_PRIORITY	Possible values are: LOW, HIGH. When two Windows overlap, the high priority window will open and the lower priority window does not open.

You can create a window using a saved schedule:

DBMS_SCHEDULER.CREATE_WINDOW (
WINDOW_NAME => 'TEST_WINDOW',
SCHEDULE_NAME => 'TEST_SCHEDULE',
RESOURCE_PLAN => 'TEST_RESOURCEPLAN',
DURATION => interval '160' minute,
COMMENTS => 'Test Window')