Oracle Scheduler Chain 的用法

阿尔萨斯

浏览: 4168489 次

最近访客更多访客>>

snower_tt

iams13

u012363178

wangyy

博主相关

博客

微博

相册

留言

关于我

文章分类

社区版块

存档分类

2014-10 ( 581)
2014-09 ( 572)
2014-08 ( 545)
更多存档...

Oracle CHAIN，也就是链，是将一串需要完成的作业连在一起，根据每一个步骤完成的不同结果来确定下面的哪一个动作需要被完成。实际上也就是if .. then ..else。比如，可能有如下的定义，step 1成功执行，执行step 2，如果step 1执行失败，执行step 3，如果step 2或step 3 执行成功，退出整个chain，如果step 2 或step 3执行失败，发送邮件并退出。CHAIN的主要步骤包括定义程序(做什么)、定义CHAIN步骤(总步骤)，以及CHAIN的规则(如何做)。本文主要描述了chain的一些步骤并给出示例供大家参考。有关job，program可以参考Oracle在线文档。

一、CHAIN定义及实施的主要步骤
1、创建程序(定义每一步骤需要执行的子程序，调用DBMS_SCHEDULER.create_program)
2、创建CHAIN(调用DBMS_SCHEDULER.create_chain)
3、定义CHAIN步骤(也就每一步的顺序)
4、定义CHAIN规则(定义每一步的执行结果成功或失败后的处理方式)
5、激活CHAIN
6、将chain添加到job
7、CHAIN的单步调试

二、演示CHAIN的用法

--演示环境
scott@CNMMBO> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Release 10.2.0.3.0 - 64bit Production

--创建演示表及序列
CREATE TABLE tb_schduler
(
   id        NUMBER (10) NOT NULL,
   descr     VARCHAR2 (20) NOT NULL,
   cr_date   DATE NOT NULL,
   CONSTRAINT tb_schduler_pk PRIMARY KEY (id)
);

CREATE SEQUENCE tb_schduler_seq;

1、创建程序
--下面定义了3个需要用到的程序program，注意这里的program不等同于procedure或者package，但是可以调用procedure或package
--下面的program主要是用于插入记录到测试表
BEGIN
  DBMS_SCHEDULER.create_program (
    program_name   => 'test_proc_1',
    program_type   => 'PLSQL_BLOCK',  -->这里的类型定义为PLSQL_BLOCK，支持STORED PROCEDURE/EXECUTEABLE
    program_action => 'BEGIN
                         INSERT INTO tb_schduler (id, descr, cr_date)
                         VALUES (tb_schduler_seq.NEXTVAL, ''test_proc_1'', SYSDATE);
                         COMMIT;
                       END;',
    enabled        => TRUE,
    comments       => 'Program for first link in the chain.');

  DBMS_SCHEDULER.create_program (
    program_name   => 'test_proc_2',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN
                         INSERT INTO tb_schduler (id, descr, cr_date)
                         VALUES (tb_schduler_seq.NEXTVAL, ''test_proc_2'', SYSDATE);
                         COMMIT;
                       END;',
    enabled        => TRUE,
    comments       => 'Program for second link in the chain.');

  DBMS_SCHEDULER.create_program (
    program_name   => 'test_proc_3',
    program_type   => 'PLSQL_BLOCK',
    program_action => 'BEGIN
                         INSERT INTO tb_schduler (id, descr, cr_date)
                         VALUES (tb_schduler_seq.NEXTVAL, ''test_proc_3'', SYSDATE);
                         COMMIT;
                       END;',
    enabled        => TRUE,
    comments       => 'Program for last link in the chain.');
END;
/

2、创建chain
--创建chain比较简单，通常只需要定义一个chain名字即可，主要是用于关联后续定义rule及step
BEGIN
  DBMS_SCHEDULER.create_chain (
    chain_name          => 'test_chain_1',   -->定义chain的名字
    rule_set_name       => NULL,             -->可以指定规则集的名字
    evaluation_interval => NULL,
    comments            => 'A test chain.');
END;
/

3、定义chain步骤
--下面定义chain的每一个步骤以及其对应的program_name，也就是每一步需要做什么
BEGIN
  DBMS_SCHEDULER.define_chain_step (
    chain_name   => 'test_chain_1',   --->chain的名字
    step_name    => 'chain_step_1',   --->步骤地名字
    program_name => 'test_proc_1');   --->当前步骤应执行的相应程序

  DBMS_SCHEDULER.define_chain_step (
    chain_name   => 'test_chain_1',
    step_name    => 'chain_step_2',
    program_name => 'test_proc_2');

  DBMS_SCHEDULER.define_chain_step (
    chain_name   => 'test_chain_1',
    step_name    => 'chain_step_3',
    program_name => 'test_proc_3');
END;
/

4、定义chain规则
--用于定义chain根据执行结果应该如何跳转的问题，每个CHAIN 规则都拥有condition和action 属性，
--当满足condition 时则执行action中指定的step。使用DBMS_SCHEDULER.DEFINE_CHAIN_RULE 过程
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;
/

5、激活chain
BEGIN
  DBMS_SCHEDULER.enable ('test_chain_1');
END;
/

6、将chain添加到job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'test_chain_1_job',
    job_type        => 'CHAIN',
    job_action      => 'test_chain_1',
    repeat_interval => 'freq=minutely; interval=2',
    start_date      => SYSTIMESTAMP,
    end_date        => SYSTIMESTAMP + (1/48),
    enabled         => FALSE);   --->值为TRUE用于激活JOB 
END;
/

7、手动执行chain
BEGIN
  DBMS_SCHEDULER.run_chain (
    chain_name    =>  'test_chain_1',
    job_name      =>  'test_chain_1_run_job',
    start_steps   =>  'chain_step_1,chain_step_3');  -->可以指定单步或多步以及所有步骤
END;
/

scott@CNMMBO> select * from tb_schduler;

        ID DESCR                CR_DATE
---------- -------------------- -----------------
         1 test_proc_1          20131203 14:36:03
         2 test_proc_3          20131203 14:36:04

--激活job   
scott@CNMMBO> exec dbms_scheduler.enable('test_chain_1_job');

PL/SQL procedure successfully completed.

三、CHAIN相关状态及视图查询

scott@CNMMBO> @job_chains              --->数据字典dba_scheduler_chains

OWNER      CHAIN_NAME      RULE_SET_O RULE_SET_NAME   NUMBER_OF_RULES NUMBER_OF_STEPS ENABL COMMENTS
---------- --------------- ---------- --------------- --------------- --------------- ----- ---------------
SCOTT      TEST_CHAIN_1    SCOTT      SCHED_RULESET$1               4               3 TRUE  A test chain.

scott@CNMMBO> @job_chain_steps         --->数据字典dba_scheduler_chain_steps

OWNER      CHAIN_NAME      STEP_NAME       PROGRAM_OW PROGRAM_NAME    STEP_TYPE
---------- --------------- --------------- ---------- --------------- --------------
SCOTT      TEST_CHAIN_1    CHAIN_STEP_1    SCOTT      TEST_PROC_1     PROGRAM
SCOTT      TEST_CHAIN_1    CHAIN_STEP_2    SCOTT      TEST_PROC_2     PROGRAM
SCOTT      TEST_CHAIN_1    CHAIN_STEP_3    SCOTT      TEST_PROC_3     PROGRAM

scott@CNMMBO> @job_chain_rules         --->数据字典 dba_scheduler_chain_rules

OWNER      CHAIN_NAME      RULE_OWNER RULE_NAME       CONDITION                 ACTION               COMMENTS
---------- --------------- ---------- --------------- ------------------------- -------------------- -------------------------
SCOTT      TEST_CHAIN_1    SCOTT      CHAIN_RULE_1    TRUE                      START "CHAIN_STEP_1" First link in the chain.
SCOTT      TEST_CHAIN_1    SCOTT      CHAIN_RULE_2    "CHAIN_STEP_1" COMPLETED  START "CHAIN_STEP_2" Second link in the chain.
SCOTT      TEST_CHAIN_1    SCOTT      CHAIN_RULE_3    "CHAIN_STEP_2" COMPLETED  START "CHAIN_STEP_3" Third link in the chain.
SCOTT      TEST_CHAIN_1    SCOTT      CHAIN_RULE_4    "CHAIN_STEP_3" COMPLETED  END                  End of the chain.

scott@CNMMBO> @job_log_detail         --->数据字典dba_scheduler_job_run_details
Enter value for input_job_name: test_chain_1_job

    LOG_ID JOB_NAME                  JOB_SUBNAME     STATUS          ACTUAL_START_DATE                             RUN_DURATION
---------- ------------------------- --------------- --------------- --------------------------------------------- --------------------
     39002 TEST_CHAIN_1_JOB          CHAIN_STEP_3    SUCCEEDED       03-DEC-13 02.43.23.735878 PM +08:00           +000 00:00:00
     39001 TEST_CHAIN_1_JOB          CHAIN_STEP_2    SUCCEEDED       03-DEC-13 02.43.22.847659 PM +08:00           +000 00:00:00
     39000 TEST_CHAIN_1_JOB          CHAIN_STEP_1    SUCCEEDED       03-DEC-13 02.43.18.735907 PM +08:00           +000 00:00:00
     39003 TEST_CHAIN_1_JOB                          SUCCEEDED       03-DEC-13 02.43.14.759867 PM +08:00           +000 00:00:09

scott@CNMMBO> select * from tb_schduler;

        ID DESCR                CR_DATE
---------- -------------------- -----------------
         1 test_proc_1          20131203 14:36:03
         2 test_proc_3          20131203 14:36:04
         3 test_proc_1          20131203 14:43:18
         4 test_proc_2          20131203 14:43:22
         5 test_proc_3          20131203 14:43:23

四、移除CHAIN及相关JOB

--对于与chain相关的program，rule，由于存在依赖性，因此需要先删除job，然后可以删除chain，program等
scott@CNMMBO> EXEC DBMS_SCHEDULER.drop_chain (chain_name  => 'test_chain_1');
BEGIN DBMS_SCHEDULER.drop_chain (chain_name  => 'test_chain_1'); END;

*
ERROR at line 1:
ORA-27479: Cannot drop "SCOTT.TEST_CHAIN_1" because other objects depend on it
ORA-06512: at "SYS.DBMS_ISCHED", line 955
ORA-06512: at "SYS.DBMS_ISCHED", line 871
ORA-06512: at "SYS.DBMS_SCHEDULER", line 1446
ORA-06512: at line 1

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_proc_1');
EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_proc_2');
EXEC DBMS_SCHEDULER.drop_program (program_name  => 'test_proc_3');

DROP TABLE tb_schduler;
DROP SEQUENCE tb_schduler_seq;

OracleDatabase PL/SQL Packages and Types Reference
http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_sched.htm#CIHHBGGI