五、使用Chains
5.1 创建Chains
5.1.1 创建CHAIN对象
SQL> desc dbms_scheduler.create_chain;
Parameter Type Mode Default?
------------------- ---------------------- ---- --------
CHAIN_NAME VARCHAR2 IN
RULE_SET_NAME VARCHAR2 IN Y
EVALUATION_INTERVAL INTERVAL DAY TO SECOND IN Y
COMMENTS VARCHAR2 IN Y
在创建时,甚至可以简单到只指定一个CHAIN 的名称,其它均为空即可,例如:
SQL> exec dbms_scheduler.create_chain('my_chain1');
PL/SQL procedure successfully completed.
定义好的Chains,可以通过 *_SCHEDULER_CHAINS 视图查看,例如:
SQL> select chain_name from user_scheduler_chains;
CHAIN_NAME
------------------------------
MY_CHAIN1
5.1.2 创建Chain Step
SQL> begin
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'my_chain1',
step_name => 'my_step1',
program_name => 'p_p1');
end;
/
PL/SQL procedure successfully completed.
下面接着为my_chain1 添加两个step,操作如下:
SQL> begin
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'my_chain1',
step_name => 'my_step2',
program_name => 'p_p2');
DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
chain_name => 'my_chain1',
step_name => 'my_step3',
program_name => 'p_p3');
end;
/
PL/SQL procedure successfully completed.
要查询定义的Chain Steps,则是通过 *_SCHEDULER_CHAIN_STEPS 视图,例如:
SQL> select chain_name,step_name,program_name from user_scheduler_chain_steps;
CHAIN_NAME STEP_NAME PROGRAM_NAME
-------------------- -------------------- --------------------
MY_CHAIN1 MY_STEP1 P_P1
MY_CHAIN1 MY_STEP2 P_P2
MY_CHAIN1 MY_STEP3 P_P3
5.1.3 创建Chain Rule
DBMS_SCHEDULER.DEFINE_CHAIN_RULE 过程的语法如下:
SQL> desc dbms_scheduler.define_chain_rule;
Parameter Type Mode Default?
---------- -------- ---- --------
CHAIN_NAME VARCHAR2 IN
CONDITION VARCHAR2 IN
ACTION VARCHAR2 IN
RULE_NAME VARCHAR2 IN Y
COMMENTS VARCHAR2 IN Y
TRUE
FALSE
stepname [NOT] SUCCEEDED
stepname [NOT] FAILED
stepname [NOT] STOPPED
stepname [NOT] COMPLETED
stepname ERROR_CODE IN (integer, integer, integer ...)
stepname ERROR_CODE NOT IN (integer, integer, integer ...)
stepname ERROR_CODE = integer
stepname ERROR_CODE != integer
stepname ERROR_CODE <> integer
stepname ERROR_CODE > integer
stepname ERROR_CODE >= integer
stepname ERROR_CODE < integer
stepname ERROR_CODE <= integer
甚至于,还可以制定成下列逻辑语法:
expression AND expression
expression OR expression
NOT (expression)
比如说,我们希望条件为step1 成功运行,那么可以指定condition 参数值如下:
'step1 completed'
Action 参数相对简单一些,这个参数用来指定当满足condition 参数时,CHAIN 执行的操作。
SQL> BEGIN
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'my_chain1',
condition => 'TRUE',
action => 'START my_step1',
rule_name => 'my_rule1');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'my_chain1',
condition => 'my_step1 completed',
action => 'START my_step2',
rule_name => 'my_rule2');
DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
chain_name => 'my_chain1',
condition => 'my_step2 completed',
action => 'end 0',
rule_name => 'my_rule3');
END;
/
PL/SQL procedure successfully completed.
5.1.4 运行Chains
SQL> BEGIN
DBMS_SCHEDULER.RUN_CHAIN (
chain_name => 'my_chain1',
start_steps => 'my_step1');
END;
/
PL/SQL procedure successfully completed.
SQL> select * from jss_t2;
TP DT
------------------------------ ------------
p_p1 inserted 03-SEP-09
p_p2 inserted 03-SEP-09
SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'chain_job_1',
job_type => 'CHAIN',
job_action => 'my_chain1',
repeat_interval => 'freq=daily;interval=1',
enabled => TRUE);
END;
/
PL/SQL procedure successfully completed.
然后,dba 就可以通过定期观察 *_scheduler_job_run_details 视图来确认chain 的执行情况了。
5.2 管理Chains
5.2.1 修改Chains属性
SQL> select chain_name,comments from user_scheduler_chains;
CHAIN_NAME COMMENTS
-------------------- --------------------------
MY_CHAIN1
SQL> exec dbms_scheduler.set_attribute('my_chain1','comments','change it for a test!');
PL/SQL procedure successfully completed.
SQL> select chain_name,comments from user_scheduler_chains;
CHAIN_NAME COMMENTS
-------------------- --------------------------
MY_CHAIN1 change it for a test !
5.2.2 设置Chain Step运行属性
SQL> desc dbms_scheduler.alter_chain;
Parameter Type Mode Default?
---------- -------- ---- --------
CHAIN_NAME VARCHAR2 IN
STEP_NAME VARCHAR2 IN
ATTRIBUTE VARCHAR2 IN
VALUE BOOLEAN IN
(1)PAUSE:设置该参数值为TRUE 时,当step 运行时,其运行状态就会变更为PAUSED;
(2)SKIP:设置该参数值为TRUE 时,当step 满足运行条件时,并不是执行step 中的program,而是直接跳过,注意当SKIP 参数值设置为TRUE,并且PAUSE 参数值也被设置为TRUE,那么将会以PAUSE 的状态优先;
(3)RESTART_ON_RECOVERY:设置该参数值为TRUE 时,如果由于数据库shutdown 导致step 被停止,那么当下次数据库启动时,step 会自动重新运行。
5.2.3 删除Chain Rules
SQL> desc dbms_scheduler.drop_chain_rule;
Parameter Type Mode Default?
---------- -------- ---- --------
CHAIN_NAME VARCHAR2 IN
RULE_NAME VARCHAR2 IN
FORCE BOOLEAN IN Y
举个简单的示例,比如删除前面定义的my_rule3,执行过程如下:
SQL> exec dbms_scheduler.drop_chain_rule('my_chain1','my_rule3',true);
PL/SQL procedure successfully completed.
5.2.4 删除Chain Steps
SQL> desc dbms_scheduler.drop_chain_step;
Parameter Type Mode Default?
---------- -------- ---- --------
CHAIN_NAME VARCHAR2 IN
STEP_NAME VARCHAR2 IN
FORCE BOOLEAN IN Y
例如,删除之前定义的my_step3,执行过程如下:
SQL> exec dbms_scheduler.drop_chain_step('my_chain1','my_step3',true);
PL/SQL procedure successfully completed.
5.2.5 删除Chains
SQL> exec dbms_scheduler.drop_chain('my_chain1',true);
PL/SQL procedure successfully completed.
这种情况下解决方案有两种:
(1)手动删除所有相关的chain_step 和chain_rule,然后再执行chain 的删除,
(2)附加force 参数并指定参数值为true,这样ORACLE 就会自动替你清除所有依赖的对象了。
转自:http://blog.csdn.net/tianlesoftware/article/details/4715218