Oracle® Database Administrator's Guide 11g Release 1 (11.1) Part Number B28310-01 |
|
|
View PDF |
This section discusses the following topics:
This section contains several examples of creating jobs. To create a job, you use the CREATE_JOB
or the CREATE_JOBS
procedures.
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; /
See Also:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_JOB
procedure and "Creating Jobs" for further informationThis section contains several examples of creating job classes. To create a job class, you use the CREATE_JOB_CLASS
procedure.
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.
See Also:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_JOB_CLASS
procedure and "Creating Job Classes" for further informationThis section contains several examples of creating programs. To create a program, you use the CREATE_PROGRAM
procedure.
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.
See Also:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_PROGRAM
procedure and "Creating Programs" for further informationThis section contains several examples of creating windows. To create a window, you use the CREATE_WINDOW
procedure.
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; /
See Also:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_WINDOW
procedure and "Creating Windows" for further informationThis section contains an example of creating a window group. To create a window group, you use the CREATE_WINDOW_GROUP
procedure.
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
See Also:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_WINDOW_GROUP
and ADD_WINDOW_GROUP_MEMBER
procedures and "Creating Window Groups" for further informationThis section contains several examples of setting attributes. To set attributes, you use SET_ATTRIBUTE
and SET_SCHEDULER_ATTRIBUTE
procedures.
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; /
See Also:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theSET_SCHEDULER_ATTRIBUTE
procedure and "Task 2E: Setting Scheduler Attributes"This section contains examples of creating chains. To create chains, you use the CREATE_CHAIN
procedure. After creating a chain, you add steps to the chain with the DEFINE_CHAIN_STEP
procedure and define the rules with the DEFINE_CHAIN_RULE
procedure.
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; /
See Also:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_CHAIN
, DEFINE_CHAIN_STEP
, and DEFINE_CHAIN_RULE
procedures and "Task 2E: Setting Scheduler Attributes"This section contains examples of creating event-based jobs and event schedules. To create event-based jobs, you use the CREATE_JOB
procedure. To create event-based schedules, you use the CREATE_EVENT_SCHEDULE
procedure.
These examples assume the existence of an application that, when it detects the arrival of a file on a system, enqueues an event onto the queue my_events_q
.
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; /
See Also:
Oracle Database PL/SQL Packages and Types Reference for detailed information about theCREATE_JOB
and CREATE_EVENT_SCHEDULE
proceduresIn an Oracle Data Guard environment, the Scheduler includes additional support for two database roles: primary and logical standby. You can configure a job to run only when the database is in the primary role or only when the database is in the logical standby role. To do so, you set the database_role
attribute. This example explains how to enable a job to run in both database roles. The method used is to create two copies of the job and assign a different database_role
attribute to each.
By default, a job runs when the database is in the role that it was in when the job was created. You can run the same job in both roles using the following steps:
Copy the job
Enable the new job
Change the database_role
attribute of the new job to the required role
The example starts by creating a job called primary_job
on the primary database. It then makes a copy of this job and sets its database_role
attribute to 'LOGICAL
STANDBY
'. If the primary database then becomes a logical standby, the job continues to run according to its schedule.
When you copy a job, the new job is disabled, so you must enable the new job.
BEGINDBMS_SCHEDULER.CREATE_JOB ( job_name => 'primary_job', program_name => 'my_prog', schedule_name => 'my_sched'); DBMS_SCHEDULER.COPY_JOB('primary_job','standby_job'); DBMS_SCHEDULER.ENABLE(name=>'standby_job', commit_semantics=>'ABSORB_ERRORS'); DBMS_SCHEDULER.SET_ATTRIBUTE('standby_job','database_role','LOGICAL STANDBY'); END; /
After you execute this example, the data in the DBA_SCHEDULER_JOB_ROLES
view is as follows:
SELECT JOB_NAME, DATABASE_ROLE FROM DBA_SCHEDULER_JOB_ROLES WHERE JOB_NAME IN ('PRIMARY_JOB','STANDBY_JOB'); JOB_NAME DATABASE_ROLE -------- ---------------- PRIMARY_JOB PRIMARY STABDBY_JOB LOGICAL STANDBY
Note:
For a physical standby database, any changes made to Scheduler objects or any database changes made by Scheduler jobs on the primary database are applied to the physical standby like any other database changes.