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.
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;
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 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;
Other enhancements include:
CREATE EXTERNAL JOB
privilege is necessary to define jobs that run OS commands and executables.INCLUDE
, EXCLUDE
and INTERSECT
to form complex schedules.Here are quick links to articles on scheduler features that span multiple versions.
For more information see:
Hope this helps. Regards Tim...
Back to normal view: https://oracle-base.com/articles/10g/scheduler-enhancements-10gr2