On heavily loaded database with lot of jobs scheduled, sometimes we need to time jobs to run at exactly specific intervals in a day. Examples 1. Running a job at every 30 minutes, and timing exactly at 00:10, 00:40, 1:10, 1:40... 2. Running a job at every 6 hours, and timing exactly at 3:00, 9:00, 15:00 ...
Even if a specific instance of job runs at an random time, the next run should fall into timed schedule. It means, for a job that runs at every 6 hours starting with 3:00 (3:00, 9:00, 15:00..), if it's run at 4:30, next run should be at scheduled time 9:00 (not at 4:30+6 i.e 10:30).
Here is how we can derive interval property of a job to get next scheduled date and time.
date_time_of_next_scheduled_run = today_date + time_of_next_scheduled_run time_of_next_scheduled_run = time_of_last_scheduled_run + interval_between_runs time_of_last_scheduled_run = time_of_first_scheduled_run + (number_of_times_run_completed_today * interval_between_runs) number_of_times_run_completed_today = floor((present_time_of_day - time_of_first_scheduled_run)/interval_between_runs)
By substituting all the above, we can get an expression for interval
date_time_of_next_scheduled_run = today_date
+ time_of_first_scheduled_run
+ floor(
(present_time_of_day - time_of_first_scheduled_run)
/interval_between_runs
)
* interval_between_runs
+ interval_between_runs
For example, a schedule for every 30 minutes, and timing at 00:10, 00:40, 1:10, 1:40...,
today_date = trunc(sysdate)
time_of_first_scheduled_run (in seconds) = 00:10 = 10*60
interval_between_runs (in seconds) = 30*60
present_time_of_day (in seconds) = to_char(d,'SSSSS')
date_time_of_next_scheduled_run =
trunc(sysdate) +
( 10*60
+ floor(
(to_char(d,'SSSSS')-(10*60))
/(30*60)
)
*(30*60)
+ (30*60)
)/(24*60*60)
We can test if this working by sample PL/SQL program which prints next scheduled time for every minute.
declare
d date;
begin
for i in 0 .. 150
loop
d := to_date('2014-05-15 00:00', 'YYYY-MM-DD HH24:MI') + i/(24*60);
dbms_output.put(to_char(d, 'YYYY-MM-DD HH24:MI') || ' - ');
dbms_output.put_line( to_char(
trunc(d) + ( 10*60 + floor( (to_char(d,'SSSSS')-(10*60)) /(30*60) ) *(30*60) + (30*60) )/(24*60*60)
, 'YYYY-MM-DD HH24:MI'));
end loop;
end;
/
2014-05-15 00:00 - 2014-05-15 00:10
2014-05-15 00:01 - 2014-05-15 00:10
...
2014-05-15 00:09 - 2014-05-15 00:10
2014-05-15 00:10 - 2014-05-15 00:40
2014-05-15 00:11 - 2014-05-15 00:40
...
2014-05-15 00:39 - 2014-05-15 00:40
2014-05-15 00:40 - 2014-05-15 01:10
2014-05-15 00:41 - 2014-05-15 01:10
...
2014-05-15 01:09 - 2014-05-15 01:10
2014-05-15 01:10 - 2014-05-15 01:40
2014-05-15 01:11 - 2014-05-15 01:40
...
Other examples of interval expression
Schedule for every 6 hours starting with 3:00 (3:00, 9:00, 15:00..)
trunc(sysdate) + ( 3*60*60 + floor( (to_char(sysdate,'SSSSS')-(3*60*60)) /(6*60*60) ) *(6*60*60) + (6*60*60) )/(24*60*60)
Schedule for every 1 hour starting with 0:00 (0:00, 1:00, 2:00, 3:00..)
trunc(sysdate) + ( floor( (to_char(sysdate,'SSSSS')) /(1*60*60) ) *(1*60*60) + (1*60*60) )/(24*60*60)
