要在scheduler的window结束的时候，也让job停止，需要对job加’stop_on_window_close’的属性。
测试如下：
1.建立测试表t1：
|
1 2 3 |
drop table t1; create table t1 as select 'First record at: '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as mycol from dual; |
2. 建立需要定期执行的存储过程：
|
1 2 3 4 5 6 7 8 9 10 |
create or replace procedure testcase_for_aa is v_ret_text clob; begin insert into t1 select 'Start at: '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; commit; dbms_lock.sleep(400); insert into t1 select 'End at: '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual; commit; end; / |
本存储过程是先插入一条当前时间的记录到测试表t1，停400秒，再插入一条结束时间的记录到测试表t1.
3.建立job和window：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
begin dbms_scheduler.create_window ( window_name => 'testcase_for_aa_window', resource_plan => null, repeat_interval => 'freq=daily; byhour=13; byminute=50; bysecond=00', duration => interval '3' minute ); end; / begin dbms_scheduler.create_job ( job_name => 'testjob_testcase_for_aa', job_type => 'stored_procedure', job_action => 'testcase_for_aa', schedule_name => 'testcase_for_aa_window', auto_drop => false, enabled => true); end; / |
注：在没设置’stop_on_window_close’的属性前，job就算是到了window的窗口之后，即3分钟之后，也不会停止。可以查询到：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
sys@ORA10G(192.168.1.100)> select * from t1; RESULT ---------------------------------------------------------- Start at: 2013-10-23 13:50:03 End at: 2013-10-23 13:56:43 Elapsed: 00:00:00.00 sys@ORA10G(192.168.1.100)> sys@ORA10G(192.168.1.100)> select x.window_name,x.log_date,x.operation from dba_scheduler_window_log x order by 2; WINDOW_NAME LOG_DATE OPERATION ------------------------------ --------------------------------------------------------------------------- ------------- TESTCASE_FOR_AA_WINDOW 23-OCT-13 01.48.23.884000 PM +08:00 CREATE TESTCASE_FOR_AA_WINDOW 23-OCT-13 01.50.01.000000 PM +08:00 OPEN TESTCASE_FOR_AA_WINDOW 23-OCT-13 01.53.00.997000 PM +08:00 CLOSE |
可以看到window close的时间还是设置的3分钟。
|
1 2 3 4 5 6 7 8 |
sys@ORA10G(192.168.1.100)> select job_name,log_date,operation,status,additional_info from dba_scheduler_job_log where job_name='TESTJOB_TESTCASE_FOR_AA'; JOB_NAME LOG_DATE OPERATION STATUS ADDITIONAL_INFO ------------------------------ --------------------------------------------------------------------------- -------------------- ------------------------------------------------------------ ------------------------------------------------------------------------------- TESTJOB_TESTCASE_FOR_AA 23-OCT-13 01.56.43.082000 PM +08:00 RUN SUCCEEDED Elapsed: 00:00:00.00 sys@ORA10G(192.168.1.100)> |
可以看到job是13:56分才完成的，即job的执行完成后的时间。
设置’stop_on_window_close’的属性
|
1 2 3 4 5 6 |
sys@ORA10G(192.168.1.100)> exec dbms_scheduler.set_attribute('testjob_testcase_for_aa','stop_on_window_close',TRUE); PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 sys@ORA10G(192.168.1.100)> |
4. 修改job的启动时间再次测试：
|
1 2 3 4 5 6 7 8 9 |
sys@ORA10G(192.168.1.100)> begin 2 sys.dbms_scheduler.set_attribute(name => 'SYS.TESTCASE_FOR_AA_WINDOW', attribute => 'repeat_interval', value => 'Freq=daily;ByHour=14;ByMinute=21;BySecond=00'); 3 end; 4 / PL/SQL procedure successfully completed. Elapsed: 00:00:00.01 sys@ORA10G(192.168.1.100)> |
5. 发现job只有start的记录，没有end的记录，因此3分钟时间到，job还没运行完，就被停止了。
|
1 2 3 4 5 6 7 8 9 10 |
sys@ORA10G(192.168.1.100)> select * from t1; RESULT ---------------------------------------------------------- Start at: 2013-10-23 13:50:03 End at: 2013-10-23 13:56:43 Start at: 2013-10-23 14:21:01 Elapsed: 00:00:00.00 sys@ORA10G(192.168.1.100)> |
6. 查scheduler相关视图：
|
1 2 3 4 5 6 7 8 9 10 |
sys@ORA10G(192.168.1.100)> select x.window_name,x.log_date,x.operation from dba_scheduler_window_log x order by 2; WINDOW_NAME LOG_DATE OPERATION ------------------------------ --------------------------------------------------------------------------- ------------- TESTCASE_FOR_AA_WINDOW 23-OCT-13 01.48.23.884000 PM +08:00 CREATE TESTCASE_FOR_AA_WINDOW 23-OCT-13 01.50.01.000000 PM +08:00 OPEN TESTCASE_FOR_AA_WINDOW 23-OCT-13 01.53.00.997000 PM +08:00 CLOSE TESTCASE_FOR_AA_WINDOW 23-OCT-13 02.20.01.856000 PM +08:00 UPDATE TESTCASE_FOR_AA_WINDOW 23-OCT-13 02.21.01.006000 PM +08:00 OPEN TESTCASE_FOR_AA_WINDOW 23-OCT-13 02.24.00.995000 PM +08:00 CLOSE |
window还是3分钟关闭了。
|
1 2 3 4 5 6 7 8 9 10 11 |
sys@ORA10G(192.168.1.100)> select job_name,log_date,operation,status,additional_info from dba_scheduler_job_log where job_name='TESTJOB_TESTCASE_FOR_AA'; JOB_NAME LOG_DATE OPERATION STATUS ADDITIONAL_INFO ------------------------------ --------------------------------------------------------------------------- -------------------- ------------------------------------------------------------ -------------------------------------------------------------------------- TESTJOB_TESTCASE_FOR_AA 23-OCT-13 12.01.42.234000 PM +08:00 RUN SUCCEEDED TESTJOB_TESTCASE_FOR_AA 23-OCT-13 01.56.43.082000 PM +08:00 RUN SUCCEEDED TESTJOB_TESTCASE_FOR_AA 23-OCT-13 02.24.01.053000 PM +08:00 RUN STOPPED REASON="Stop job called because associated window was closed" Elapsed: 00:00:00.00 sys@ORA10G(192.168.1.100)> sys@ORA10G(192.168.1.100)> |
注意，job是状态是stop，原因是because associated window was closed 。即3分钟的window的时间到了后，job被终止了。
这里有个小技巧，如果要看某个job是否具有stop_on_window_close属性，可以用get_attribute，但是由于这是布尔型的，而dbms_output。put_line只能输出varchar型，所以要用case转换一下，不然没法看：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
sys@ORA10G(192.168.1.35)> declare 2 value boolean; 3 begin 4 dbms_scheduler.get_attribute ('testjob_testcase_for_aa','stop_on_window_close',value); 5 dbms_output.PUT_LINE('Check Result: '||case value when true then 'True' else 'False' end); 6 end; 7 / Check Result: True PL/SQL procedure successfully completed. Elapsed: 00:00:00.00 sys@ORA10G(192.168.1.35)> |