Oracle Scheduler and the Database Resource Manager

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)