Oracle Scheduler Chain 的用法
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的用法
[sql] view plain copy
--演示环境
scott@CNMMBO>select * from v$version where rownum<2;
BANNER
----------------------------------------------------------------
OracleDatabase 10g Release 10.2.0.3.0 - 64bit Production
--创建演示表及序列
CREATE TABLE tb_schduler
(
id NUMBER (10)NOT NULL,
descr VARCHAR2 (20)NOT NULL,
cr_dateDATE 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/SQLprocedure successfully completed.
三、CHAIN相关状态及视图查询
[sql] view plain copy
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 3TRUE 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 CONDITIONACTION COMMENTS
---------- --------------- ---------- --------------- ------------------------- -------------------- -------------------------
SCOTT TEST_CHAIN_1 SCOTT CHAIN_RULE_1TRUE 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 valuefor 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
[sql] view plain copy
--对于与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;
*
ERRORat line 1:
ORA-27479: Cannotdrop "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;
