Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

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.

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:

For more information see:

Hope this helps. Regards Tim...

Back to the Top.