该job需要每小时运行一次,为了和其他任务错开,需要在半点上执行,从网上搜索了很久,也没结果。
其实很简单通过decode函数处理某时间减一秒后的30的倍数,如果是1(即下半点)则去当前整点+一个半小时,否则取当前整点+半小时。把该值写入inverval即可。
decode(trunc(to_number(to_char(sysdate-1/86400,'mi'))/30),0,trunc(sysdate,'hh24')+1/48,trunc(sysdate,'hh24')+3/48)
以下为测试过程。
select sysdate ,to_char(sysdate,'hh24') hh24,
to_number(to_char(sysdate,'hh24')) number_hh24,
trunc(to_number(to_char(sysdate,'mi'))/30) trunc_mi,
decode(trunc(to_number(to_char(to_date('2009-04-18 21:30:01','yyyy-mm-dd hh24:mi:ss')-1/86400,'mi'))/30),0,trunc(to_date('2009-04-18 21:30:01','yyyy-mm-dd hh24:mi:ss'),'hh24')+1/48,trunc(to_date('2009-04-18 21:30:01','yyyy-mm-dd hh24:mi:ss'),'hh24')+3/48) time213001,
decode(trunc(to_number(to_char(to_date('2009-04-18 21:29:59','yyyy-mm-dd hh24:mi:ss')-1/86400,'mi'))/30),0,trunc(to_date('2009-04-18 21:29:59','yyyy-mm-dd hh24:mi:ss'),'hh24')+1/48,trunc(to_date('2009-04-18 21:29:59','yyyy-mm-dd hh24:mi:ss'),'hh24')+3/48) time212959,
decode(trunc(to_number(to_char(to_date('2009-04-18 21:30:00','yyyy-mm-dd hh24:mi:ss')-1/86400,'mi'))/30),0,trunc(to_date('2009-04-18 21:30:00','yyyy-mm-dd hh24:mi:ss'),'hh24')+1/48,trunc(to_date('2009-04-18 21:30:00','yyyy-mm-dd hh24:mi:ss'),'hh24')+3/48) time213000,
decode(trunc(to_number(to_char(sysdate-1/86400,'mi'))/30),0,trunc(sysdate,'hh24')+1/48,trunc(sysdate,'hh24')+3/48) systest
from dual;