要在scheduler的window结束的时候,也让job停止,需要对job加’stop_on_window_close’的属性。
测试如下:
1.建立测试表t1:
drop table t1;
create table t1 as select 'First record at: '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') as mycol from dual;
1
2
3
droptablet1;
createtablet1asselect'First record at: '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')asmycolfromdual;
2. 建立需要定期执行的存储过程:
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;
/
1
2
3
4
5
6
7
8
9
10
createorreplaceproceduretestcase_for_aais
v_ret_textclob;
begin
insertintot1select'Start at: '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')fromdual;
commit;
dbms_lock.sleep(400);
insertintot1select'End at: '||to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')fromdual;
commit;
end;
/
本存储过程是先插入一条当前时间的记录到测试表t1,停400秒,再插入一条结束时间的记录到测试表t1.
3.建立job和window:
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;
/
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分钟之后,也不会停止。可以查询到:
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
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 *fromt1;
RESULT
----------------------------------------------------------
Startat:2013-10-2313:50:03
Endat:2013-10-2313:56:43
Elapsed:00:00:00.00
sys@ORA10G(192.168.1.100)>
sys@ORA10G(192.168.1.100)>selectx.window_name,x.log_date,x.operationfromdba_scheduler_window_logxorderby2;
WINDOW_NAMELOG_DATEOPERATION
----------------------------------------------------------------------------------------------------------------------
TESTCASE_FOR_AA_WINDOW23-OCT-1301.48.23.884000PM+08:00CREATE
TESTCASE_FOR_AA_WINDOW23-OCT-1301.50.01.000000PM+08:00OPEN
TESTCASE_FOR_AA_WINDOW23-OCT-1301.53.00.997000PM+08:00CLOSE
可以看到window close的时间还是设置的3分钟。
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)>
1
2
3
4
5
6
7
8
sys@ORA10G(192.168.1.100)>selectjob_name,log_date,operation,status,additional_infofromdba_scheduler_job_logwherejob_name='TESTJOB_TESTCASE_FOR_AA';
JOB_NAMELOG_DATEOPERATIONSTATUSADDITIONAL_INFO
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TESTJOB_TESTCASE_FOR_AA23-OCT-1301.56.43.082000PM+08:00RUNSUCCEEDED
Elapsed:00:00:00.00
sys@ORA10G(192.168.1.100)>
可以看到job是13:56分才完成的,即job的执行完成后的时间。
设置’stop_on_window_close’的属性
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)>
1
2
3
4
5
6
sys@ORA10G(192.168.1.100)>execdbms_scheduler.set_attribute('testjob_testcase_for_aa','stop_on_window_close',TRUE);
PL/SQLproceduresuccessfullycompleted.
Elapsed:00:00:00.00
sys@ORA10G(192.168.1.100)>
4. 修改job的启动时间再次测试:
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)>
1
2
3
4
5
6
7
8
9
sys@ORA10G(192.168.1.100)>begin
2sys.dbms_scheduler.set_attribute(name=>'SYS.TESTCASE_FOR_AA_WINDOW',attribute=>'repeat_interval',value=>'Freq=daily;ByHour=14;ByMinute=21;BySecond=00');
3end;
4/
PL/SQLproceduresuccessfullycompleted.
Elapsed:00:00:00.01
sys@ORA10G(192.168.1.100)>
5. 发现job只有start的记录,没有end的记录,因此3分钟时间到,job还没运行完,就被停止了。
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)>
1
2
3
4
5
6
7
8
9
10
sys@ORA10G(192.168.1.100)>select *fromt1;
RESULT
----------------------------------------------------------
Startat:2013-10-2313:50:03
Endat:2013-10-2313:56:43
Startat:2013-10-2314:21:01
Elapsed:00:00:00.00
sys@ORA10G(192.168.1.100)>
6. 查scheduler相关视图:
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
1
2
3
4
5
6
7
8
9
10
sys@ORA10G(192.168.1.100)>selectx.window_name,x.log_date,x.operationfromdba_scheduler_window_logxorderby2;
WINDOW_NAMELOG_DATEOPERATION
----------------------------------------------------------------------------------------------------------------------
TESTCASE_FOR_AA_WINDOW23-OCT-1301.48.23.884000PM+08:00CREATE
TESTCASE_FOR_AA_WINDOW23-OCT-1301.50.01.000000PM+08:00OPEN
TESTCASE_FOR_AA_WINDOW23-OCT-1301.53.00.997000PM+08:00CLOSE
TESTCASE_FOR_AA_WINDOW23-OCT-1302.20.01.856000PM+08:00UPDATE
TESTCASE_FOR_AA_WINDOW23-OCT-1302.21.01.006000PM+08:00OPEN
TESTCASE_FOR_AA_WINDOW23-OCT-1302.24.00.995000PM+08:00CLOSE
window还是3分钟关闭了。
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)>
1
2
3
4
5
6
7
8
9
10
11
sys@ORA10G(192.168.1.100)>selectjob_name,log_date,operation,status,additional_infofromdba_scheduler_job_logwherejob_name='TESTJOB_TESTCASE_FOR_AA';
JOB_NAMELOG_DATEOPERATIONSTATUSADDITIONAL_INFO
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TESTJOB_TESTCASE_FOR_AA23-OCT-1312.01.42.234000PM+08:00RUNSUCCEEDED
TESTJOB_TESTCASE_FOR_AA23-OCT-1301.56.43.082000PM+08:00RUNSUCCEEDED
TESTJOB_TESTCASE_FOR_AA23-OCT-1302.24.01.053000PM+08:00RUNSTOPPEDREASON="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转换一下,不然没法看:
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)>
1
2
3
4
5
6
7
8
9
10
11
12
13
sys@ORA10G(192.168.1.35)>declare
2valueboolean;
3begin
4dbms_scheduler.get_attribute('testjob_testcase_for_aa','stop_on_window_close',value);
5dbms_output.PUT_LINE('Check Result: '||casevaluewhentruethen'True'else'False'end);
6end;
7/
CheckResult:True
PL/SQLproceduresuccessfullycompleted.
Elapsed:00:00:00.00
sys@ORA10G(192.168.1.35)>