Writing INTERVAL expression of DBMS_JOB for exactly timed schedules

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)