我的JOB执行时间,我说了算
如果JOB的interval直接用sysdate指定,如: sysdate + 5/1440,oracle自动生成的下一次的执行时间,会有几秒的延迟.下面是一个测试:
1.创建表test_job_a,用于记录JOB开始执行时间和结束时间
SQL> create table test_job_a (
2 start_date date,
3 end_date date );
2.创建JOB,JOB的首次执行时间指定在11:20:00,interval为5 mins
SQL> variable x number;
SQL> begin
2 dbms_job.submit ( job => :x,
3 what =>'declare
4 start_date date :=sysdate;
5 begin
6 for i in 1 .. 10 loop
7 dbms_lock.sleep(12);
8 end loop;
9 insert into test_job_a values (start_date, sysdate);
10 commit;
11 end;',
12 next_date => to_date('2010-01-16 11:20:00', 'yyyy-mm-dd hh24:mi:ss'),
13 interval => 'sysdate + 5/1440');
14 commit;
15* end;
3.时间查看记录结果:
SQL> select * from test_job_a order by start_date;
START_DATE END_DATE
------------------------- -------------------------
2010-01-16 11:20:02 2010-01-16 11:22:02
2010-01-16 11:25:07 2010-01-16 11:27:07
2010-01-16 11:30:12 2010-01-16 11:32:12
2010-01-16 11:35:17 2010-01-16 11:37:17
2010-01-16 11:40:22 2010-01-16 11:42:22
2010-01-16 11:45:27 2010-01-16 11:47:27
2010-01-16 11:50:32 2010-01-16 11:52:32
2010-01-16 11:55:37 2010-01-16 11:57:37
4.原因:
(1) JOB的调用有时间延迟.
(2) 根据实际的执行时间来自动计算下一次的执行时间.
如:start_date : 2010-01-16 11:55:37,JOB执行结束后,可以通过查询user_jobs视图的next_date列到JOB的下次执行时间: 2010-01-16 12:00:37
SQL> select next_date from user_jobs;
NEXT_DATE
-------------------
2010-01-16 12:00:37
解决方法:
用trunc(sysdate, 'mi') + 5/1440指定时间间隔
把test_job_a表的内容清空,修改上面JOB的interval,再次记录JOB的执行时间.
记录结果如下:
SQL> select * from test_job_a order by start_date;
START_DATE END_DATE
------------------------- -------------------------
2010-01-16 14:00:04 2010-01-16 14:02:04
2010-01-16 14:05:04 2010-01-16 14:07:04
2010-01-16 14:10:04 2010-01-16 14:12:04
2010-01-16 14:15:04 2010-01-16 14:17:04
SQL> select next_date from user_jobs;
NEXT_DATE
-------------------
2010-01-16 14:10:00
SQL> /
NEXT_DATE
-------------------
2010-01-16 14:15:00
JOB interval写法总结:
1day = 24hour= 1440mins
若JOB的执行时间间隔为分钟:
trunc(sysdate,'mi') + n/1444 (0如每隔5mins执行一次: trunc(sysdate,'mi') + 5/1440
若JOB的执行时间间隔为小时:
trunc(sysdate, 'hh') + n/24[ + m/1440] (0如每隔2小时,且执行时间为15mins执行一次: trunc(sysdate, 'hh') + 2/24 + 15/1440
若JOB的执行时间间隔为天:
trunc(sysdate) + n [+ m/24 [+ x/1440]]
如每天的8点15执行: trunc(sysdate) + 1 + 8/24 + 15/1440
其它情况就需要用到日期函数了:
1) 如每月5号的8点执行JOB: trunc(last_day(sysdate)) + 5 + 8/24
2) 如周一到周五的8点执行:
trunc(least(next_day(sysdate,2),
next_day(sysdate,3),
next_day(sysdate,4),
next_day(sysdate,5),
next_day(sysdate,6)
)) + 8/24
在oracle中1:代表周 一,2-7依次代表周二…周六