Oracle Scheduler and the Database Resource Manager

Oracle Tips by Burleson Consulting

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

Simplifying Management Tasks Using the Scheduler

An Introduction to the Job Scheduler

� You may run PL/SQL and Java stored procedure, C functions, regular SQL scripts, and UNIX or Windows scripts.

� You can create time-based or event-based jobs. Events can be application-generated or schedulergenerated.

� The Scheduler consists of the concepts: Program, Job, Schedule, Job class, Resource group, Window and Window Group.

� The Scheduler architecture consists primarily of the job table, job coordinator, and the job workers (or slaves).

Managing the Basic Scheduler Components

Creating Jobs

DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME => 'TEST_JOB1',
JOB_TYPE => 'PLSQL_BLOCK',
JOB_ACTION => 'DELETE FROM PERSONS WHERE
SYSDATE=SYSDATE-1',
START_DATE => '28-JUNE-04 07.00.00 PM
AUSTRALIA/SYDNEY',
REPEAT_INTERVAL => 'FREQ=DAILY;INTERVAL=2',
END_DATE => '20-NOV-04 07.00.00 PM
AUSTRALIA/SYDNEY',
COMMENTS => 'TEST JOB')

JOB_TYPE
Possible values are:
o plsql_block
o stored_procedure
o executable

JOB_ACTION Specifies the exact procedure, command, or script that the job will execute.

START_DATE and END_DATE These parameters specify the date that a new job should start and end. (Many jobs may not have an end_date parameter, since they are ongoing jobs.)

REPEAT_INTERVAL You can specify a repeat interval in one of two ways:
o Use a PL/SQL date/time expression.
o Use a database calendaring expression.

Specifying Intervals

FREQ takes YEARLY, MONTHLY, WEEKLY, DAILY, HOURLY, MINUTELY, and SECONDLY.

FREQ=DAILY; INTERVAL=10	executes a job every 10 days
FREQ=HOURLY; INTERVAL=2	executes a job every other hour
FREQ=WEEKLY; BYDAY=FRI	executes a job every Friday.
FREQ=WEEKLY; INTERVAL=2; BYDAY=FRI	executes a job every other Friday.
FREQ=MONTHLY; BYMONTHDAY=1	executes a job on the last day of the month
FREQ=YEARLY; BYMONTH=DEC; BYMONTHDAY=31	executes a job on the 31st of December.
FREQ=MONTHLY; BYDAY=2FRI	executes a job every second Friday of the month

Refer to PL/SQL Packages and Types Reference 10g Release 1, Chapter 83, Table 83-9 Values for repeat_interval.

Note: You�ll be the owner of a job if you create it in your own schema. However, if you create it in another schema, that schema user will be owner of the job.

Enabling and Disabling Jobs

All jobs are disabled by default when you create them. You must explicitly enable them in order to activate and schedule them.

DBMS_SCHEDULER.ENABLE ('TEST_JOB1')
DBMS_SCHEDULER.DISABLE ('TEST_JOB1')

Dropping a Job

DBMS_SCHEDULER.DROP_JOB (JOB_NAME => 'test_job1')

Running and Stopping a Job

DBMS_SCHEDULER.RUN_JOB('TEST_JOB1')
DBMS_SCHEDULER.STOP_JOB('TEST_JOB1')

In both the STOP_JOB and RUN_JOB procedures, there is a FORCE argument, which is set to FALSE by default. By setting FORCE=TRUE, you can stop or drop a job immediately by using the appropriate procedure. You must have the MANAGE SCHEDULER system privilege to use the FORCE setting.

Creating a Program

DBMS_SCHEDULER.CREATE_PROGRAM(
PROGRAM_NAME => 'TEST_PROGRAM',
PROGRAM_ACTION => 'SCOTT.UPDATE_SCHEMA_STATS',
PROGRAM_TYPE => 'STORED_PROCEDURE',
ENABLED => TRUE)

Note: If you want to create the program in a different user�s schema, you must qualify the program name with the schema name.

TEST_JOB1 job can then be created using the program component as follows:

DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME => 'TEST_JOB1',
PROGRAM_NAME => 'TEST_PROGRAM',
REPEAT_INTERVAL=> 'FREQ=DAILY;BYHOUR=12',
ENABLED => TRUE)

Enabling and Disabling Programs

DBMS_SCHEDULER.ENABLE('TEST_PROGRAM')
DBMS_SCHEDULER.DISABLE('TEST_PROGRAM')

Dropping a Program

DBMS_SCHEDULER.DROP_PROGRAM('TEST_PROGRAM')

Creating a Schedule

DBMS_SCHEDULER.CREATE_SCHEDULE(
SCHEDULE_NAME => 'TEST_SCHEDULE',
START_DATE => SYSTIMESTAMP,
END_DATE => SYSTIMESTAMP + 30,
REPEAT_INTERVAL => 'FREQ=HOURLY;INTERVAL= 12',
COMMENTS => 'EVERY 12 HOURS')

Note the following about creating a Schedule:

o When you create a schedule, Oracle provides access to PUBLIC. Thus, all users can use your schedule, without any explicit grant of privileges to do so.

o You specify the start and end times using the TIMESTAMP WITH TIME ZONE datatype. The Scheduler also supports all NLS_TIMESTAMP_TZ_FORMAT settings.

o You must use a calendering expression to create the repeat interval.

DBMS_SCHEDULER.CREATE_JOB(
JOB_NAME => 'TEST_JOB02',
PROGRAM_NAME => 'TEST_PROGRAM',
SCHEDULE_NAME => 'TEST_SCHEDULE')

Altering a Schedule

You can alter the attributes (except SCHEDULE_NAME) of a schedule by using the SET_ATTRIBUTE procedure of the DBMS_SCHEDULER package.

Dropping a Schedule

DBMS_SCHEDULER.DROP_SCHEDULE(SCHEDULE_NAME => 'TEST_SCHEDULE');

When you drop a schedule by using the FORCE=TRUE attribute, you�ll drop the schedule, even if there are jobs and windows that use the schedule. The Scheduler first disables the dependent jobs/windows before dropping the schedule itself.