8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
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.
- All Scheduler Articles
- Scheduler Quick Links : 10gR1, 10gR2, 11gR1, 11gR2, 12cR1, 12cR2, 18c, 19c, 21c
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 JOB
privilege 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
,EXCLUDE
andINTERSECT
to form complex schedules. - External jobs not initiated by SYS now run with the permissions "nobody:nobody" unless configured differently in the "$ORACLE_HOME/rdbms/admin/externaljob.ora" file. See MOS Note 979186.1. External jobs that are initiated by SYS still run as "oracle".
- Job credentials, introduced in 11g, should be used in preference to the previous configuration.
Scheduler Features List
Here are quick links to articles on scheduler features that span multiple versions.
For more information see:
- Using Events
- Using Chains
- DBMS_SCHEDULER
- DBMS_RULE_ADM
- Overview of Scheduler Concepts
- Clarification on Externaljob.ora and Permissions (Doc ID 979186.1)
- All Scheduler Articles
- Scheduler Quick Links : 10gR1, 10gR2, 11gR1, 11gR2, 12cR1, 12cR2, 18c, 19c, 21c
Hope this helps. Regards Tim...