Examples of Using the Scheduler

Example 28-1 Creating a Single Regular Job

The following statement creates a single regular job called my_job1 in the oe schema:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name             => 'oe.my_job1',
   job_type             => 'PLSQL_BLOCK',
   job_action           => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
                            ''sales''); END;',
   start_date           => '15-JUL-03 1.00.00AM US/Pacific',
   repeat_interval      => 'FREQ=DAILY', 
   end_date             => '15-SEP-03 1.00.00AM US/Pacific',
   enabled              =>  TRUE,
   comments             => 'Gather table statistics');
END;
/

This job gathers table statistics on the sales table. It will run for the first time on July 15th and then once a day until September 15. To verify that the job was created, issue the following statement:

SELECT JOB_NAME FROM DBA_SCHEDULER_JOBS WHERE JOB_NAME = 'MY_JOB1';

JOB_NAME
------------------------------
MY_JOB1

Example 28-2 Creating a Set of Regular Jobs

The following example creates a set of regular jobs:

DECLARE
 newjob sys.job;
 newjobarr sys.job_array;
BEGIN
 -- Create an array of JOB object types
 newjobarr := sys.job_array();

 -- Allocate sufficient space in the array
 newjobarr.extend(5);

 -- Add definitions for 5 jobs
 FOR i IN 1..5 LOOP
   -- Create a JOB object type
   newjob := sys.job(job_name => 'TESTJOB' || to_char(i),
                     job_style => 'REGULAR',
                     job_template => 'PROG1',
                     repeat_interval => 'FREQ=MINUTELY;INTERVAL_15',
                     start_date => systimestamp + interval '600' second,
                     max_runs => 2,
                     auto_drop => FALSE,
                     enabled _> TRUE
                    );

   -- Add it to the array
   newjobarr(i) := newjob;
 END LOOP;

 -- Call CREATE_JOBS to create jobs in one transaction
 DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
END;
/

Example 28-3 Creating a Set of Lightweight Jobs

The following example creates a set of lightweight jobs in one transaction:

DECLARE
 newjob sys.job;
 newjobarr sys.job_array;
BEGIN
 newjobarr := sys.job_array();
 newjobarr.extend(5);
 FOR i IN 1..5 LOOP
   newjob := sys.job(job_name => 'lwjob_' || to_char(i),
                     job_style => 'LIGHTWEIGHT',
                     job_template => 'PROG1',
                     repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
                     start_date => systimestamp + interval '10' second,
                     enabled => TRUE
                    );
   newjobarr(i) := newjob;
 end loop;

 DBMS_SCHEDULER.CREATE_JOBS(newjobarr, 'TRANSACTIONAL');
END;
/

Example 28-4 Creating a Job Class

The following statement creates a job class:

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name              =>  'my_class1',
   service                     =>  'my_service1', 
   comments                    =>  'This is my first job class');
END;
/

This creates my_class1 in SYS. It uses a service called my_service1. To verify that the job class was created, issue the following statement:

SELECT JOB_CLASS_NAME FROM DBA_SCHEDULER_JOB_CLASSES
WHERE JOB_CLASS_NAME = 'MY_CLASS1';

JOB_CLASS_NAME
------------------------------
MY_CLASS1

Example 28-5 Creating a Job Class

The following statement creates a job class:

BEGIN
DBMS_SCHEDULER.CREATE_JOB_CLASS (
   job_class_name             =>  'finance_jobs', 
   resource_consumer_group    =>  'finance_group',
   service                    =>  'accounting',
   comments                   =>  'All finance jobs');
END;
/

This creates finance_jobs in SYS. It assigns a resource consumer group called finance_group, and designates service affinity for the accounting service. Note that if the accounting service is mapped to a resource consumer group other than finance_group, jobs in this class run under the finance_group consumer group, because the resource_consumer_group attribute takes precedence.

Example 28-6 Creating a Program

The following statement creates a program in the oe schema:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name          => 'oe.my_program1',
   program_type          => 'PLSQL_BLOCK',
   program_action        => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''oe'',
                             ''sales''); END;',
   number_of_arguments   => 0,
   enabled               => TRUE,
   comments              => 'My comments here');
END;
/

This creates my_program1, which uses PL/SQL to gather table statistics on the sales table. To verify that the program was created, issue the following statement:

SELECT PROGRAM_NAME FROM DBA_SCHEDULER_PROGRAMS 
WHERE PROGRAM_NAME = 'MY_PROGRAM1';

PROGRAM_NAME
-------------------------
MY_PROGRAM1

Example 28-7 Creating a Program

The following statement creates a program in the oe schema:

BEGIN
DBMS_SCHEDULER.CREATE_PROGRAM (
   program_name           => 'oe.my_saved_program1',
   program_action         => '/usr/local/bin/date',
   program_type           => 'EXECUTABLE',
   comments               => 'My comments here');
END;
/

This creates my_saved_program1, which uses an executable.

Example 28-8 Creating a Window

The following statement creates a window called my_window1 in SYS:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW (
   window_name          =>  'my_window1',
   resource_plan        =>  'my_res_plan1',
   start_date           =>  '15-JUL-03 1.00.00AM US/Pacific',
   repeat_interval      =>  'FREQ=DAILY',
   end_date             =>  '15-SEP-03 1.00.00AM US/Pacific',
   duration             =>  interval '80' MINUTE,
   comments             =>  'This is my first window');
END;
/

This window will open once a day at 1AM for 80 minutes every day from May 15th to October 15th. To verify that the window was created, issue the following statement:

SELECT WINDOW_NAME FROM DBA_SCHEDULER_WINDOWS WHERE WINDOW_NAME = 'MY_WINDOW1';

WINDOW_NAME
------------------------------
MY_WINDOW1

Example 28-9 Creating a Window

The following statement creates a window called my_window2 in SYS:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW ( 
   window_name       => 'my_window2',
   schedule_name     => 'my_stats_schedule',
   resource_plan     => 'my_resourceplan1',
   duration          => interval '60' minute,
   comments          => 'My window');
END; 
/

Example 28-10 Creating a Window Group

The following statement creates a window group called my_window_group1:

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP ('my_windowgroup1');
END;
/

Then, you could add three windows (my_window1, my_window2, and my_window3) to my_window_group1 by issuing the following statements:

BEGIN
DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
   group_name   =>  'my_window_group1',
   window_list  =>  'my_window1, my_window2');

DBMS_SCHEDULER.ADD_WINDOW_GROUP_MEMBER (
   group_name   =>  'my_window_group1',
   window_list  =>  'my_window3');
END;
/

To verify that the window group was created and the windows added to it, issue the following statement:

SELECT * FROM DBA_SCHEDULER_WINDOW_GROUPS;

WINDOW_GROUP_NAME    ENABLED   NUMBER_OF_WINDOWS   COMMENTS
-----------------    -------   -----------------   ---------------
MY_WINDOW_GROUP1     TRUE                      3   This is my first window group

Example 28-11 Setting the Repeat Interval Attribute

The following example resets the frequency my_emp_job1 will run to daily:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'my_emp_job1',
   attribute      =>   'repeat_interval',
   value          =>   'FREQ=DAILY');
END;
/

To verify the change, issue the following statement:

SELECT JOB_NAME, REPEAT_INTERVAL FROM DBA_SCHEDULER_JOBS
WHERE JOB_NAME =  'MY_EMP_JOB1';

JOB_NAME             REPEAT_INTERVAL
----------------     ---------------
MY_EMP_JOB1          FREQ=DAILY

Example 28-12 Setting the Comments Attribute

The following example resets the comments for my_saved_program1:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'my_saved_program1',
   attribute      =>   'comments',
   value          =>   'For nightly table stats');
END;
/

To verify the change, issue the following statement:

SELECT PROGRAM_NAME, COMMENTS FROM DBA_SCHEDULER_PROGRAMS;

PROGRAM_NAME        COMMENTS
------------        -----------------------
MY_PROGRAM1         My comments here
MY_SAVED_PROGRAM1   For nightly table stats

Example 28-13 Setting the Duration Attribute

The following example resets the duration of my_window3 to 90 minutes:

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name           =>   'my_window3',
   attribute      =>   'duration',
   value          =>   interval '90' minute);
END;
/

To verify the change, issue the following statement:

SELECT WINDOW_NAME, DURATION FROM DBA_SCHEDULER_WINDOWS
WHERE WINDOW_NAME = 'MY_WINDOW3';

WINDOW_NAME        DURATION
-----------        ---------------
MY_WINDOW3         +000 00:90:00

Example 28-14 Setting the Database Role Attribute

The following example sets the database role of the job my_job to LOGICAL STANDBY.

BEGIN
DBMS_SCHEDULER.SET_ATTRIBUTE (
   name        => 'my_job',
   attribute   => 'database_role',
   value       =>'LOGICAL STANDBY');
END;
/

To verify the change in database role, issue the following command:

SELECT JOB_NAME, DATABASE_ROLE FROM DBA_SCHEDULER_JOB_ROLES
    WHERE JOB_NAME = 'MY_JOB';

JOB_NAME             DATABASE_ROLE
--------             -----------------
MY_JOB               LOGICAL STANDBY

Example 28-15 Setting the Event Expiration Attribute

The following example sets the time in seconds to 3600 when an event expires:

BEGIN
DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE (
   attribute     =>   event_expiry_time,
   value         =>   3600);
END;
/

Example 28-16 Setting Multiple Job Attributes for a Set of Regular Jobs

The following example sets four different attributes for each of the five regular jobs created in Example 28-2, "Creating a Set of Regular Jobs":

DECLARE
 newattr sys.jobattr;
 newattrarr sys.jobattr_array;
 j number;
BEGIN
 -- Create new JOBATTR array
 newattrarr := sys.jobattr_array();

 -- Allocate enough space in the array
 newattrarr.extend(20);
 j := 1;
 FOR i IN 1..5 LOOP
   -- Create and initialize a JOBATTR object type
   newattr := sys.jobattr(job_name => 'TESTJOB' || to_char(i),
                          attr_name => 'MAX_FAILURES',
                          attr_value => 5);
   -- Add it to the array.
   newattrarr(j) := newattr;
   j := j + 1;
   newattr := sys.jobattr(job_name => 'TESTJOB' || to_char(i),
                          attr_name => 'COMMENTS',
                          attr_value => 'Bogus comment');
   newattrarr(j) := newattr;
   j := j + 1;
   newattr := sys.jobattr(job_name => 'TESTJOB' || to_char(i),
                          attr_name => 'END_DATE',
                          attr_value => systimestamp + interval '24' hour);
   newattrarr(j) := newattr;
   j := j + 1;
   newattr := sys.jobattr(job_name => 'TESTJOB' || to_char(i),
                          attr_name => 'SCHEDULE_LIMIT',
                          attr_value => interval '1' hour);
   newattrarr(j) := newattr;
   j := j + 1;
 END LOOP;

 -- Call SET_JOB_ATTRIBUTES to set all 20 set attributes in one transaction
 DBMS_SCHEDULER.SET_JOB_ATTRIBUTES(newattrarr, 'TRANSACTIONAL');
END;
/

Example 28-17 Setting Attributes for a Set of Lightweight Jobs

The following example sets multiple attributes for a set of lightweight jobs. Note that not all regular job attributes are supported for lightweight jobs:

DECLARE
 newattr sys.jobattr;
 newattrarr sys.jobattr_array;
 j number;
BEGIN
 -- Create new JOBATTR array
 newattrarr := sys.jobattr_array();

 -- Allocate enough space in the array
 newattrarr.extend(10);
 j := 1;
 FOR i IN 1..5 LOOP
   -- Create and initialize JOBATTR object type
   newattr := sys.jobattr(job_name => 'lwjob_' || to_char(i),
                          attr_name => 'END_DATE',
                          attr_value => systimestamp + interval '24' hour);
   -- Add it to array.
   newattrarr(j) := newattr;
   j := j + 1;

   newattr := sys.jobattr(job_name => 'lwjob_' || to_char(i),
                          attr_name => 'RESTARTABLE',
                          attr_value => TRUE);
   newattrarr(j) := newattr;
   j := j + 1;
 END LOOP;

 -- Call SET_JOB_ATTRIBUTES to set all 20 set attributes in one transaction
 DBMS_SCHEDULER.SET_JOB_ATTRIBUTES(newattrarr, 'TRANSACTIONAL');
END;
/

Example 28-18 Creating a Chain

The following example creates a chain where my_program1 runs before my_program2 and my_program3. my_program2 and my_program3 run in parallel after my_program1 has completed.

BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name            =>  'my_chain1',
   rule_set_name         =>  NULL,
   evaluation_interval   =>  NULL,
   comments              =>  NULL);
END;
/
 
--- define three steps for this chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step1', 'my_program1');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step2', 'my_program2');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain1', 'step3', 'my_program3');
END;
/

--- define corresponding rules for the chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE('my_chain1', 'TRUE', 'START step1');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain1', 'step1 COMPLETED', 'Start step2, step3');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain1', 'step2 COMPLETED AND step3 COMPLETED', 'END');
END;
/

Example 28-19 Creating a Chain

The following example creates a chain where first my_program1 runs. If it succeeds, my_program2 runs; otherwise, my_program3 runs.

BEGIN
DBMS_SCHEDULER.CREATE_CHAIN (
   chain_name            => 'my_chain2',
   rule_set_name         => NULL,
   evaluation_interval   => NULL,
   comments              => NULL);
END;
/
 
--- define three steps for this chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step1', 'my_program1');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step2', 'my_program2');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP('my_chain2', 'step3', 'my_program3');
END;
/
 
--- define corresponding rules for the chain.
BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE ('my_chain2', 'TRUE', 'START step1');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain2', 'step1 SUCCEEDED', 'Start step2');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain2', 'step1 COMPLETED AND step1 NOT SUCCEEDED', 'Start step3');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
   'my_chain2', 'step2 COMPLETED OR step3 COMPLETED', 'END');
END;
/

Example 28-20 Creating an Event-Based Schedule

The following example illustrates creating a schedule that can be used to start a job whenever the Scheduler receives an event indicating that a file arrived on the system before 9AM:

BEGIN
DBMS_SCHEDULER.CREATE_EVENT_SCHEDULE (
   schedule_name     =>  'scott.file_arrival',
   start_date        =>  systimestamp,
   event_condition   =>  'tab.user_data.object_owner = ''SCOTT'' 
      and tab.user_data.event_name = ''FILE_ARRIVAL'' 
      and extract hour from tab.user_data.event_timestamp < 9',
   queue_spec        =>  'my_events_q');
END;
/

Example 28-21 Creating an Event-Based Job

The following example creates a job that starts when the Scheduler receives an event indicating that a file arrived on the system:

BEGIN
DBMS_SCHEDULER.CREATE_JOB (
   job_name            =>  my_job,
   program_name        =>  my_program,
   start_date          =>  '15-JUL-04 1.00.00AM US/Pacific',
   event_condition     =>  'tab.user_data.event_name = ''FILE_ARRIVAL''',
   queue_spec          =>  'my_events_q'
   enabled             =>  TRUE,
   comments            =>  'my event-based job');
END;
/