[bbk5170]第59集第6章 用scheduler自动化 07

Creating Job Chains

1. Create a chain object.
2. Define chain steps.
3. Define chain rules.
4. Starting the chain:
   1. Enable the chain
   2. Create a job that points to the chain.

chain执行步骤，如果chain A 执行成功了，就执行chain B，执行完B之后，还可以根据条件进行switch，执行chain C或者chain D.

chain执行的各个步骤，可以嵌套.一个chain中的子步骤也可以是一个chain.

Example of a Chain

创建chain步骤

1、Creating a Chain Object

2.Defining Chain Steps

/*
        ==============================================================================
        defining chain steps
        ==============================================================================
*/
DBMS_SCHEDULER.DEFINE_CHAIN_EVENT_STEP(
        chain_name      =>      'bulk_load_chain',
        step_name       =>      'load_data_evt',
        event_condition =>      'tab.user_data.object_owner = "HR" and
                                 tab.user_data.object_name  = "DATA.TXT" and
                                 tab.user_data.event_type   = "FILE_ARRIVAL"
                                ',
        queue_spec      =>      'HR.LOAD_JOB_EVENT_Q'
);

DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
        chain_name      =>      'bulk_load_chain',
        step_name       =>      'do_bulk_load',
        program_name    =>      'hr.load_data_prog'
)

DBMS_SCHEDULER.DEFINE_CHAIN_STEP(
        chain_name      =>      'bulk_load_chain',

        step_name       =>      'rebuild_index',
        program_name    =>      'hr.rebuild_indexes'
);

3.Defining Chain Rules

4、Start the Chain

BEGIN
        DBMS_SCHEDULER.ENABLE('bulk_load_chain');
END;
/


BEGIN
        DBMS_SCHEDULER.CREATE_JOB(
                job_name        =>      'bulk_load_chain_job',
                job_type        =>      'CHAIN',
                job_action      =>      'bulk_load_chain',
                repeat_interval =>      'freq=daily;byhour=7;byminutes=5;bysecond=0',
                enabled         =>      TRUE
        );
END;
/

5、Monitoring the chains

SQL> DESC DBA_SCHEDULER_CHAINS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CHAIN_NAME                                NOT NULL VARCHAR2(30)
 RULE_SET_OWNER                                     VARCHAR2(30)
 RULE_SET_NAME                                      VARCHAR2(30)
 NUMBER_OF_RULES                                    NUMBER
 NUMBER_OF_STEPS                                    NUMBER
 ENABLED                                            VARCHAR2(5)
 EVALUATION_INTERVAL                                INTERVAL DAY(3) TO SECOND(0)
 USER_RULE_SET                                      VARCHAR2(5)
 COMMENTS                                           VARCHAR2(240)

SQL> DESC DBA_SCHEDULER_CHAIN_RULES;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CHAIN_NAME                                NOT NULL VARCHAR2(30)
 RULE_OWNER                                NOT NULL VARCHAR2(30)
 RULE_NAME                                          VARCHAR2(30)
 CONDITION                                          VARCHAR2(4000)
 ACTION                                             VARCHAR2(4000)
 COMMENTS                                           VARCHAR2(4000)

SQL> DESC DBA_SCHEDULER_CHAIN_STEPS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 CHAIN_NAME                                NOT NULL VARCHAR2(30)
 STEP_NAME                                 NOT NULL VARCHAR2(30)
 PROGRAM_OWNER                                      VARCHAR2(98)
 PROGRAM_NAME                                       VARCHAR2(98)
 EVENT_SCHEDULE_OWNER                               VARCHAR2(98)
 EVENT_SCHEDULE_NAME                                VARCHAR2(98)
 EVENT_QUEUE_OWNER                                  VARCHAR2(30)
 EVENT_QUEUE_NAME                                   VARCHAR2(30)
 EVENT_QUEUE_AGENT                                  VARCHAR2(30)
 EVENT_CONDITION                                    VARCHAR2(4000)
 CREDENTIAL_OWNER                                   VARCHAR2(30)
 CREDENTIAL_NAME                                    VARCHAR2(30)
 DESTINATION                                        VARCHAR2(128)
 SKIP                                               VARCHAR2(5)
 PAUSE                                              VARCHAR2(5)
 RESTART_ON_RECOVERY                                VARCHAR2(5)
 RESTART_ON_FAILURE                                 VARCHAR2(5)
 STEP_TYPE                                          VARCHAR2(14)
 TIMEOUT                                            INTERVAL DAY(3) TO SECOND(0)

SQL> DESC DBA_SCHEDULER_RUNNING_CHAINS;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 JOB_NAME                                  NOT NULL VARCHAR2(30)
 JOB_SUBNAME                                        VARCHAR2(30)
 CHAIN_OWNER                               NOT NULL VARCHAR2(30)
 CHAIN_NAME                                NOT NULL VARCHAR2(30)
 STEP_NAME                                 NOT NULL VARCHAR2(30)
 STATE                                              VARCHAR2(15)
 ERROR_CODE                                         NUMBER
 COMPLETED                                          VARCHAR2(5)
 START_DATE                                         TIMESTAMP(6) WITH TIME ZONE
 END_DATE                                           TIMESTAMP(6) WITH TIME ZONE
 DURATION                                           INTERVAL DAY(9) TO SECOND(6)
 SKIP                                               VARCHAR2(5)
 PAUSE                                              VARCHAR2(5)
 RESTART_ON_RECOVERY                                VARCHAR2(5)
 RESTART_ON_FAILURE                                 VARCHAR2(5)
 STEP_JOB_SUBNAME                                   VARCHAR2(30)
 STEP_JOB_LOG_ID                                    NUMBER