Scheduler (DBMS_SCHEDULER) Enhancements in Oracle 10g Database Release 2
The new scheduler introduced in Oracle 10g Release 1 was a radical departure from that of previous Oracle versions. The functionality of the scheduler has been extended in Oracle 10g Release 2 to include the following.
Related articles.
- Scheduler in Oracle Database 10g
- Scheduler Enhancements in Oracle Database 11g Release 1
- Scheduler Enhancements in Oracle Database 11g Release 2
- Remote Scheduler Agent Installation for Oracle 11g Release 2
- Services in Oracle Database 10g
Jobs That Raise Events
Individual jobs are instructed to signal specific events by setting the RAISE_EVENTS attribute. Events are placed on the scheduler event queue, which is available to applications that subscribe to the queue. The following code sets up a test user and grants the necessary privileges.
CONN sys/password AS SYSDBA DROP USER test CASCADE; CREATE USER test IDENTIFIED BY test QUOTA UNLIMITED ON USERS; GRANT CONNECT TO test; GRANT CREATE JOB TO test; GRANT AQ_USER_ROLE TO test;
Access to the scheduler event queue is controlled using the ADD_EVENT_QUEUE_SUBSCRIBER and REMOVE_EVENT_QUEUE_SUBSCRIBER procedures of the DBMS_SCHEDULER package.
CONN test/test EXEC DBMS_SCHEDULER.add_event_queue_subscriber;
Once the user has subscribed to the scheduler event queue, the RAISE_EVENTS attribute can be set for existing jobs. The following example creates an empty job that runs every minute for 1 hour. Once the job is created it is instructed to raise the JOB_SUCCEEDED event.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'event_raising_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
-- Does nothing.
NULL;
END;',
start_date => SYSTIMESTAMP,
end_date => SYSTIMESTAMP + (1/24), -- 1 hour
repeat_interval => 'freq=minutely; bysecond=0',
enabled => TRUE);
DBMS_SCHEDULER.set_attribute(
name => 'event_raising_job',
attribute => 'raise_events',
value => DBMS_SCHEDULER.job_succeeded);
END;
/Multiple event types can be raised by adding event type constants together in the SET_ATTRIBUTE procedure. For example, to raise events for JOB_SUCCEEDED and JOB_FAILED do the following.
BEGIN
DBMS_SCHEDULER.set_attribute(
name => 'event_raising_job',
attribute => 'raise_events',
value => DBMS_SCHEDULER.job_succeeded + DBMS_SCHEDULER.job_failed);
END;
/Events are dequeued from the scheduler event queue using the DBMS_AQ package.
SET SERVEROUTPUT ON
DECLARE
l_dequeue_options DBMS_AQ.dequeue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_queue_msg sys.scheduler$_event_info;
BEGIN
l_dequeue_options.consumer_name := 'TEST';
DBMS_AQ.dequeue(queue_name => 'SYS.SCHEDULER$_EVENT_QUEUE',
dequeue_options => l_dequeue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
DBMS_OUTPUT.put_line ('event_type : ' || l_queue_msg.event_type);
DBMS_OUTPUT.put_line ('object_owner : ' || l_queue_msg.object_owner);
DBMS_OUTPUT.put_line ('object_name : ' || l_queue_msg.object_name);
DBMS_OUTPUT.put_line ('event_timestamp: ' || l_queue_msg.event_timestamp);
DBMS_OUTPUT.put_line ('error_code : ' || l_queue_msg.error_code);
DBMS_OUTPUT.put_line ('event_status : ' || l_queue_msg.event_status);
DBMS_OUTPUT.put_line ('log_id : ' || l_queue_msg.log_id);
DBMS_OUTPUT.put_line ('run_count : ' || l_queue_msg.run_count);
DBMS_OUTPUT.put_line ('failure_count : ' || l_queue_msg.failure_count);
DBMS_OUTPUT.put_line ('retry_count : ' || l_queue_msg.retry_count);
END;
/
event_type : JOB_SUCCEEDED
object_owner : TEST
object_name : EVENT_RAISING_JOB
event_timestamp: 26-OCT-2005 16:42:00.169000 +01:00
error_code : 0
event_status : 0
log_id : 1901
run_count : 1
failure_count : 0
retry_count : 0
PL/SQL procedure successfully completed.
SQL>The following code removes the job and unsubscribes the user from the scheduler event queue.
EXEC DBMS_SCHEDULER.drop_job('test.event_raising_job');
EXEC DBMS_SCHEDULER.remove_event_queue_subscriber;Event-Based Jobs
In addition to raising events, it is possible to define jobs that are run in response to events, know as event-based jobs. The following code sets up a test user and grants the necessary privileges.
CONN sys/password AS SYSDBA DROP USER test CASCADE; CREATE USER test IDENTIFIED BY test QUOTA UNLIMITED ON USERS; GRANT CONNECT TO test; GRANT CREATE TABLE TO test; GRANT CREATE SEQUENCE TO test; GRANT CREATE TYPE TO test; GRANT AQ_ADMINISTRATOR_ROLE TO test; GRANT CREATE JOB TO test;
The job in this example inserts records into the following table.
CONN test/test DROP TABLE scheduler_test; DROP SEQUENCE scheduler_test_seq; CREATE TABLE scheduler_test ( id NUMBER(10) NOT NULL, created_date DATE NOT NULL, CONSTRAINT scheduler_test_pk PRIMARY KEY (id) ); CREATE SEQUENCE scheduler_test_seq;
Next we must create an event queue to signal the job. First define an object type to act as the payload for the queue.
CREATE OR REPLACE TYPE t_event_queue_payload AS OBJECT ( event_name VARCHAR2(30) ); /
Next, create the queue table (EVENT_QUEUE_TAB) using the payload object, define the queue (EVENT_QUEUE) and start it. Event queues used to signal jobs must support multiple consumers.
BEGIN
-- Create a queue table to hold the event queue.
DBMS_AQADM.create_queue_table(
queue_table => 'event_queue_tab',
queue_payload_type => 't_event_queue_payload',
multiple_consumers => TRUE,
comment => 'Queue Table For Event Messages');
-- Create the event queue.
DBMS_AQADM.create_queue (
queue_name => 'event_queue',
queue_table => 'event_queue_tab');
-- Start the event queue.
DBMS_AQADM.start_queue (queue_name => 'event_queue');
END;
/The following code defines an event-based job that inserts records into the test table. The QUEUE_SPEC parameter identifies the event queue the job listens to, while the EVENT_CONDITION parameter allows conditional triggering of the job based on the message content.
BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'event_based_job',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN
INSERT INTO scheduler_test (id, created_date)
VALUES (scheduler_test_seq.NEXTVAL, SYSDATE);
COMMIT;
END;',
start_date => SYSTIMESTAMP,
event_condition => 'tab.user_data.event_name = ''give_me_a_prod''',
queue_spec => 'event_queue',
enabled => TRUE);
END;
/Before testing the job, confirm the test table is empty using the following query.
SELECT * FROM scheduler_test; no rows selected SQL>
Then trigger the job by putting a message on the event queue using the DBMS_AQ package.
DECLARE
l_enqueue_options DBMS_AQ.enqueue_options_t;
l_message_properties DBMS_AQ.message_properties_t;
l_message_handle RAW(16);
l_queue_msg t_event_queue_payload;
BEGIN
l_queue_msg := t_event_queue_payload('give_me_a_prod');
DBMS_AQ.enqueue(queue_name => 'event_queue',
enqueue_options => l_enqueue_options,
message_properties => l_message_properties,
payload => l_queue_msg,
msgid => l_message_handle);
COMMIT;
END;
/Checking the test table again reveals the job has run successfully.
SELECT * FROM scheduler_test;
ID CREATED_DATE
---------- --------------------
1 26-OCT-2005 15:20:13
1 row selected.
SQL>The following code removes the example objects.
-- Remove the job.
EXEC DBMS_SCHEDULER.drop_job('event_based_job');
-- Stop the event queue.
EXEC DBMS_AQADM.stop_queue (queue_name => 'event_queue');
-- Drop the event queue.
EXEC DBMS_AQADM.drop_queue (queue_name => 'event_queue');
-- Remove the queue table.
EXEC DBMS_AQADM.drop_queue_table(queue_table => 'event_queue_tab');
DROP TYPE t_event_queue_payload;
DROP TABLE scheduler_test;
DROP SEQUENCE scheduler_test_seq;
PURGE RECYCLEBIN;Job Chains
Job chains allow multiple jobs to be linked together to form dependency chains. The following code sets up a test user and grants the necessary privileges.
CONN sys/password AS SYSDBA
DROP USER test CASCADE;
CREATE USER test IDENTIFIED BY test QUOTA UNLIMITED ON USERS;
GRANT CONNECT TO test;
GRANT CREATE TABLE TO test;
GRANT CREATE SEQUENCE TO test;
GRANT CREATE JOB TO test;
BEGIN
DBMS_RULE_ADM.grant_system_privilege(
privilege => DBMS_RULE_ADM.create_rule_set_obj,
grantee => 'TEST',
grant_option => FALSE);
DBMS_RULE_ADM.grant_system_privilege(
privilege => DBMS_RULE_ADM.create_evaluation_context_obj,
grantee => 'TEST',
grant_option => FALSE);
DBMS_RULE_ADM.grant_system_privilege(
privilege => DBMS_RULE_ADM.create_rule_obj,
grantee => 'TEST',
grant_option => FALSE);
END;
/The jobs in this example insert records into the following table.
CONN test/test DROP TABLE scheduler_test; DROP SEQUENCE scheduler_test_seq; CREATE TABLE scheduler_test ( id NUMBER(10) NOT NULL, description VARCHAR2(20) NOT NULL, created_date DATE NOT NULL, CONSTRAINT scheduler_test_pk PRIMARY KEY (id) ); CREATE SEQUENCE scheduler_test_seq;
First, create three programs (test_program_1-3) to represent each of the three links in the job chain.
BEGIN
DBMS_SCHEDULER.create_program (
program_name => 'test_program_1',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_1'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Program for first link in the chain.');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_2',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_2'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Program for second link in the chain.');
DBMS_SCHEDULER.create_program (
program_name => 'test_program_3',
program_type => 'PLSQL_BLOCK',
program_action => 'BEGIN
INSERT INTO scheduler_test (id, description, created_date)
VALUES (scheduler_test_seq.NEXTVAL, ''test_program_3'', SYSDATE);
COMMIT;
END;',
enabled => TRUE,
comments => 'Program for last link in the chain.');
END;
/Next, create a chain called test_chain_1 using the CREATE_CHAIN procedure of the DBMS_SCHEDULER package.
BEGIN
DBMS_SCHEDULER.create_chain (
chain_name => 'test_chain_1',
rule_set_name => NULL,
evaluation_interval => NULL,
comments => 'A test chain.');
END;
/Information about existing chains is displayed using the %_SCHEDULER_CHAINS views.
SET LINESIZE 200
COLUMN owner FORMAT A10
COLUMN chain_name FORMAT A15
COLUMN rule_set_owner FORMAT A10
COLUMN rule_set_name FORMAT A15
COLUMN comments FORMAT A15
SELECT owner,
chain_name,
rule_set_owner,
rule_set_name,
number_of_rules,
number_of_steps,
enabled,
comments
FROM dba_scheduler_chains;
OWNER CHAIN_NAME RULE_SET_O RULE_SET_NAME NUMBER_OF_RULES NUMBER_OF_STEPS ENABL COMMENTS
---------- --------------- ---------- --------------- --------------- --------------- ----- ---------------
TEST TEST_CHAIN_1 TEST SCHED_RULESET$1 4 3 FALSE A test chain.
1 row selected.
SQL>Next, define the steps (chain_step_1-3) in the chain, associating them with the three programs (test_program_1-3).
BEGIN
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_1',
program_name => 'test_program_1');
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_2',
program_name => 'test_program_2');
DBMS_SCHEDULER.define_chain_step (
chain_name => 'test_chain_1',
step_name => 'chain_step_3',
program_name => 'test_program_3');
END;
/Information about existing chain steps is displayed using the %_SCHEDULER_CHAIN_STEPS views.
SET LINESIZE 200
COLUMN owner FORMAT A10
COLUMN chain_name FORMAT A15
COLUMN step_name FORMAT A15
COLUMN program_owner FORMAT A10
COLUMN program_name FORMAT A15
SELECT owner,
chain_name,
step_name,
program_owner,
program_name,
step_type
FROM dba_scheduler_chain_steps
ORDER BY owner, chain_name, step_name;
OWNER CHAIN_NAME STEP_NAME PROGRAM_OW PROGRAM_NAME STEP_TYPE
---------- --------------- --------------- ---------- --------------- --------------
TEST TEST_CHAIN_1 CHAIN_STEP_1 TEST TEST_PROGRAM_1 PROGRAM
TEST TEST_CHAIN_1 CHAIN_STEP_2 TEST TEST_PROGRAM_2 PROGRAM
TEST TEST_CHAIN_1 CHAIN_STEP_3 TEST TEST_PROGRAM_3 PROGRAM
3 rows selected.
SQL>Next, define the chain rules (chain_rule_1-4) that link the chain steps (chain_step_1-3) together. The CONDITION parameter of the first chain rule must always equate to TRUE and the ACTION parameter of the last rule must be set to "END". For a 3 step job chain we need at lease 4 chain rules.
BEGIN
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => 'TRUE',
action => 'START "CHAIN_STEP_1"',
rule_name => 'chain_rule_1',
comments => 'First link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => '"CHAIN_STEP_1" COMPLETED',
action => 'START "CHAIN_STEP_2"',
rule_name => 'chain_rule_2',
comments => 'Second link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => '"CHAIN_STEP_2" COMPLETED',
action => 'START "CHAIN_STEP_3"',
rule_name => 'chain_rule_3',
comments => 'Third link in the chain.');
DBMS_SCHEDULER.define_chain_rule (
chain_name => 'test_chain_1',
condition => '"CHAIN_STEP_3" COMPLETED',
action => 'END',
rule_name => 'chain_rule_4',
comments => 'End of the chain.');
END;
/In this case the next step in the chain is triggered on successful completion of the previous step, but a range of linking conditions are possible including job state and error conditions.
Information about existing chain rules is displayed using the %_SCHEDULER_CHAIN_RULES views.
SET LINESIZE 200
COLUMN owner FORMAT A10
COLUMN chain_name FORMAT A15
COLUMN rule_owner FORMAT A10
COLUMN rule_name FORMAT A15
COLUMN condition FORMAT A25
COLUMN action FORMAT A20
COLUMN comments FORMAT A25
SELECT owner,
chain_name,
rule_owner,
rule_name,
condition,
action,
comments
FROM dba_scheduler_chain_rules
ORDER BY owner, chain_name, rule_owner, rule_name;
OWNER CHAIN_NAME RULE_OWNER RULE_NAME CONDITION ACTION COMMENTS
---------- --------------- ---------- --------------- ------------------------- -------------------- -------------------------
TEST TEST_CHAIN_1 TEST CHAIN_RULE_1 TRUE START "CHAIN_STEP_1" First link in the chain.
TEST TEST_CHAIN_1 TEST CHAIN_RULE_2 "CHAIN_STEP_1" COMPLETED START "CHAIN_STEP_2" Second link in the chain.
TEST TEST_CHAIN_1 TEST CHAIN_RULE_3 "CHAIN_STEP_2" COMPLETED START "CHAIN_STEP_3" Third link in the chain.
TEST TEST_CHAIN_1 TEST CHAIN_RULE_4 "CHAIN_STEP_3" COMPLETED END End of the chain.
4 rows selected.
SQL>Chains are always disabled when they are created, so the chain must be enabled before it is used.
BEGIN
DBMS_SCHEDULER.enable ('test_chain_1');
END;
/Before defining a job to use the chain, confirm the test table is empty using the following query.
SELECT * FROM scheduler_test ORDER BY id; no rows selected SQL>
Next define a job to start the chain. The following job definition has a job type of "CHAIN" and the name of the chain as the job action.
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'test_chain_1_job',
job_type => 'CHAIN',
job_action => 'test_chain_1',
repeat_interval => 'freq=minutely; bysecond=0',
start_date => SYSTIMESTAMP,
end_date => SYSTIMESTAMP + (1/48),
enabled => TRUE);
END;
/The job runs every minute, so check the contents of the test table after a couple of minutes.
SELECT * FROM scheduler_test ORDER BY id;
ID DESCRIPTION CREATED_DATE
---------- -------------------- --------------------
1 test_program_1 27-OCT-2005 09:36:00
2 test_program_2 27-OCT-2005 09:36:00
3 test_program_3 27-OCT-2005 09:36:00
4 test_program_1 27-OCT-2005 09:37:00
5 test_program_2 27-OCT-2005 09:37:00
6 test_program_3 27-OCT-2005 09:37:00
6 rows selected.
SQL>The current status of a running chain is displayed using the %_SCHEDULER_RUNNING_CHAINS views.
SET LINESIZE 200
COLUMN owner FORMAT A10
COLUMN job_name FORMAT A20
COLUMN chain_owner FORMAT A10
COLUMN chain_name FORMAT A15
COLUMN step_name FORMAT A25
SELECT owner,
job_name,
chain_owner,
chain_name,
step_name,
state
FROM dba_scheduler_running_chains
ORDER BY owner, job_name, chain_name, step_name;
OWNER JOB_NAME CHAIN_OWNE CHAIN_NAME STEP_NAME STATE
---------- -------------------- ---------- --------------- ------------------------- -----------
TEST TEST_CHAIN_1_JOB TEST TEST_CHAIN_1 CHAIN_STEP_1 RUNNING
TEST TEST_CHAIN_1_JOB TEST TEST_CHAIN_1 CHAIN_STEP_2 NOT_STARTED
TEST TEST_CHAIN_1_JOB TEST TEST_CHAIN_1 CHAIN_STEP_3 NOT_STARTED
3 rows selected.
SQL>The RUN_CHAIN procedure provides an alternative method of running a chain by creating a one-off job in the background.
BEGIN
DBMS_SCHEDULER.run_chain (
chain_name => 'test_chain_1',
job_name => 'test_chain_1_run_job',
start_steps => 'chain_step_1, chain_step_2, chain_step_3');
END;
/The following code cleans up the test objects.
EXEC DBMS_SCHEDULER.drop_job(job_name => 'test_chain_1_job'); EXEC DBMS_SCHEDULER.drop_chain (chain_name => 'test_chain_1'); EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_1'); EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_2'); EXEC DBMS_SCHEDULER.drop_program (program_name => 'test_program_3'); DROP TABLE scheduler_test; DROP SEQUENCE scheduler_test_seq; PURGE RECYCLEBIN;
Miscellaneous
Other enhancements include:
- Access to external jobs has been separated from internal jobs. The
CREATE EXTERNAL JOBprivilege is necessary to define jobs that run OS commands and executables. - The "freq=" clause of the calendaring syntax now accepts named schedules as well as predefined frequencies, allowing user defined frequencies.
- The calendaring syntax allows schedules to be combined using set operators such as
INCLUDE,EXCLUDEandINTERSECTto form complex schedules.
For more information see:
- Using Events
- Using Chains
- DBMS_SCHEDULER
- DBMS_RULE_ADM
- Overview of Scheduler Concepts
- Scheduler in Oracle Database 10g
- Scheduler Enhancements in Oracle Database 11g Release 1
- Scheduler Enhancements in Oracle Database 11g Release 2
- Remote Scheduler Agent Installation for Oracle 11g Release 2
- Services in Oracle Database 10g
Hope this helps. Regards Tim...
 |