Oracle Job Chain

Job Chains

創(chuàng)建chain的順序

1. 創(chuàng)建chain object；

2. 定義chain中的steps；

3. 增加規(guī)則；

4. 啟用chain；

5. 創(chuàng)建指向這個(gè)chain的job。

創(chuàng)建Chain Object

實(shí)驗(yàn)描述：

ENV：create table test_chain(id number);

createtable test_chain1(id number);

createtable test_chain2(id number);

step1：test_chain1表插入一條數(shù)據(jù)；

step2：step1執(zhí)行成功后，test_chain中有超過兩條數(shù)據(jù)時(shí)，插入test_chain2表一條數(shù)據(jù)。

begin

dbms_scheduler.create_chain(chain_name=>'my_chain1',rule_set_name=>null,evaluation_interval=>interval '1' minute,

comments=>'Chain with 30 minuteevaluation interval');

end;

/

PL/SQL procedure successfully completed.

evaluation_interval指的是chain rule的掃描間隔，這里是自定義的。Scheduler在啟動(dòng)job chain時(shí)和每次執(zhí)行完chain step時(shí)都會(huì)去掃描chain rule，這里除了啟動(dòng)job chain時(shí)和chain step結(jié)束時(shí)，還會(huì)每一分鐘自動(dòng)掃描一次來判斷rule的正確性。這個(gè)參數(shù)對(duì)于某種特殊情形，本次實(shí)驗(yàn)就啟用這個(gè)參數(shù)。

定義Chain Steps

創(chuàng)建program

begin

dbms_scheduler.create_program(program_name=>'my_program1',program_type=>'PLSQL_BLOCK',

program_action=>'begin insert intotest_chain1 values (1);commit;end;');

end;

/

PL/SQL procedure successfully completed.

begin

dbms_scheduler.create_program(program_name=>'my_program2',program_type=>'PLSQL_BLOCK',

program_action=>'begin insert intotest_chain2 values (2);commit;end;');

end;

/

PL/SQL procedure successfully completed.

--enable program

SQL> begin 

 2 dbms_scheduler.enable('my_program1,my_program2');

 3  end;

 4  /

創(chuàng)建step

begin

dbms_scheduler.define_chain_step(chain_name=>'my_chain1',step_name=>'my_step1',program_name=>'my_program1');

dbms_scheduler.define_chain_step(chain_name=>'my_chain1',step_name=>'my_step2',program_name=>'my_program2');

end;

/

PL/SQL procedure successfully completed.

Note：定義step時(shí)，program和chain可以不存在，但是啟動(dòng)chain時(shí)，必須保證program

和chain存在。

增加Chain規(guī)則

Rule描述：

step1直接執(zhí)行；

step2執(zhí)行條件：step1執(zhí)行成功，并且(selectcount(*) from test_chain) >=2

begin                               

dbms_scheduler.define_chain_rule(chain_name=>'my_chain1',condition=>'TRUE',action=>'startmy_step1',rule_name=>'rule_1');

end;

/

PL/SQL procedure successfully completed.

begin

dbms_scheduler.define_chain_rule(chain_name=>'my_chain1',condition=>':my_step1.state=''SUCCEEDED''and (select count(*) from test_chain) >=2',

action=>'startmy_step2',rule_name=>'rule_1_2');

end;

/

PL/SQL procedure successfully completed.

啟用Chain

begin

dbms_scheduler.enable('my_chain1');

end;

/

PL/SQL procedure successfully completed.

創(chuàng)建Chain job

Note：直接調(diào)用run_chain運(yùn)行則不需enablechain，創(chuàng)建job則需要。

begin

dbms_scheduler.create_job(job_name=>'chain_job_1',job_type=>'CHAIN',job_action=>'my_chain1',

start_date=>sysdate,repeat_interval=>'freq=minutely;interval=1',enabled=>TRUE);

end;

/

Note：單獨(dú)啟動(dòng)job，注意參數(shù)USE_CURRENT_SESSION=>false

SQL> begin

 2 dbms_scheduler.run_job(job_name=>'chain_job_1',USE_CURRENT_SESSION=>false);

 3  end;

 4  /

SQL> selectjob_name,job_subname,status,req_start_date,actual_start_date fromuser_scheduler_job_run_details where job_name='CHAIN_JOB_1';

JOB_NAME        JOB_SUBNAME          STATUS     REQ_START_DATE                                     ACTUAL_START_DATE

--------------- ------------------------------ ----------------------------------------------------------------------------------------------------

CHAIN_JOB_1                          STOPPED    16-JUN-16 03.44.03.000000 PM +08:00                16-JUN-16 03.44.03.588735 PM+08:00

CHAIN_JOB_1     MY_STEP1             FAILED     16-JUN-16 03.44.03.682508 PM +08:00                16-JUN-16 03.44.03.693946 PM+08:00

CHAIN_JOB_1     MY_STEP1             SUCCEEDED  16-JUN-16 04.17.43.335679 PM +08:00                16-JUN-16 04.17.43.436069 PM+08:00

發(fā)現(xiàn)my_step2還沒有運(yùn)行

SQL> select * from test_chain1;

       ID

----------

        1

my_program1已寫入數(shù)據(jù)

制造my_step2啟動(dòng)的條件

SQL> insert into test_chainvalues(9);

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> selectjob_name,job_subname,status,req_start_date,actual_start_date fromuser_scheduler_job_run_details where job_name='CHAIN_JOB_1';

JOB_NAME        JOB_SUBNAME          STATUS     REQ_START_DATE                                     ACTUAL_START_DATE

--------------- ------------------------------ -------------------------------------------------- --------------------------------------------------

CHAIN_JOB_1                          STOPPED    16-JUN-16 03.44.03.000000 PM +08:00                16-JUN-16 03.44.03.588735 PM+08:00

CHAIN_JOB_1     MY_STEP2             SUCCEEDED  16-JUN-16 04.30.43.064558 PM +08:00                16-JUN-16 04.30.43.152496 PM+08:00

CHAIN_JOB_1     MY_STEP1             FAILED     16-JUN-16 03.44.03.682508 PM +08:00                16-JUN-16 03.44.03.693946 PM+08:00

CHAIN_JOB_1     MY_STEP1             SUCCEEDED  16-JUN-16 04.17.43.335679 PM +08:00                16-JUN-16 04.17.43.436069 PM+08:00

SQL> select * from test_chain2;

       ID

----------

        2