Oracle DBMS_SCHEDULE to run at 8am, 12pm, 4pm and 8pm every day of the week

Question

I am unsure how to tackle using Oracle DBMS_SCHEDULER. I have been asked to run a particular PL/SQL procedure job at:

8AM, 12PM, 4PM and lastly at 8PM every day of the week

and then re-start again the following day at the same times again at: 8AM, 12PM, 4PM and lastly at 8PM.

I understand that I firstly need to create a schedule but don’t know what the frequency setup should be, i.e.:

dbms_scheduler.create_schedule( schedule_name   => 'MY_JOB',
                                repeat_interval => 'FREQ=DAILY;BYHOUR=8;BYMINUTE=00'
                                start_date      => SYSTIMESTAMP
                              );

Wernfried Domscheit · Accepted Answer · 2019-09-26T06:19:36.560

You don't have to create a named SCHEDULE (but you can do if you prefer), you can put the repeat interval directly in the job:

DBMS_SCHEDULER.CREATE_JOB (
   job_name        => ...
  ,start_date      => SYSTIMESTAMP
  ,repeat_interval => 'FREQ=HOURLY;INTERVAL=1;BYHOUR=08,12,16,20;BYMINUTE=00'

Note, for jobs with frequency less than daily you have to consider daylight saving times. Time zone of SYSTIMESTAMP is the time zone of database server's operating system, very often this is set as static UTC offset (e.g. +02:00). In order to take daylight saving times into account this UTC offset changes twice a year.

If jobs must follow daylight savings adjustments, then you must specify a region name for the time zone of the start_date. For example you can do

DBMS_SCHEDULER.CREATE_JOB (
   job_name        => ...
  ,start_date      => SYSTIMESTAMP AT TIME ZONE 'Europe/Zurich'
  ,repeat_interval => 'FREQ=HOURLY;INTERVAL=1;BYHOUR=08,12,16,20;BYMINUTE=00'

Have a look at this PL/SQL block to see the difference.

DECLARE
   next_run_date TIMESTAMP WITH TIME ZONE;
   start_date TIMESTAMP WITH TIME ZONE; 
BEGIN
   DBMS_OUTPUT.PUT_LINE('Static UTC offset:');
   start_date := TIMESTAMP '2019-10-26 00:00:00 +02:00';
   FOR i IN 1..10 LOOP
      DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=HOURLY;INTERVAL=1;BYHOUR=08,12,16,20;BYMINUTE=00', start_date, next_run_date, next_run_date);
      DBMS_OUTPUT.PUT_LINE(next_run_date AT LOCAL);
   END LOOP;

   DBMS_OUTPUT.PUT_LINE('Time zone region name:');
    next_run_date := NULL;
   start_date := TIMESTAMP '2019-10-26 00:00:00 Europe/Zurich';
   FOR i IN 1..10 LOOP
      DBMS_SCHEDULER.EVALUATE_CALENDAR_STRING('FREQ=HOURLY;INTERVAL=1;BYHOUR=08,12,16,20;BYMINUTE=00', start_date, next_run_date, next_run_date);
      DBMS_OUTPUT.PUT_LINE(next_run_date AT LOCAL);
   END LOOP;

END;

Static UTC offset:
2019-10-26 08:00:00.000 +02:00
2019-10-26 12:00:00.000 +02:00
2019-10-26 16:00:00.000 +02:00
2019-10-26 20:00:00.000 +02:00
2019-10-27 07:00:00.000 +01:00
2019-10-27 11:00:00.000 +01:00
2019-10-27 15:00:00.000 +01:00
2019-10-27 19:00:00.000 +01:00
2019-10-28 07:00:00.000 +01:00
2019-10-28 11:00:00.000 +01:00

Time zone region name:
2019-10-26 08:00:00.000 +02:00
2019-10-26 12:00:00.000 +02:00
2019-10-26 16:00:00.000 +02:00
2019-10-26 20:00:00.000 +02:00
2019-10-27 08:00:00.000 +01:00
2019-10-27 12:00:00.000 +01:00
2019-10-27 16:00:00.000 +01:00
2019-10-27 20:00:00.000 +01:00
2019-10-28 08:00:00.000 +01:00
2019-10-28 12:00:00.000 +01:00

See Repeat Intervals and Daylight Savings for more details

Is there any option to for more than 1 upvote? 100s upvotes on this from my side. — Ankit Bajpai, Sep 26 '19 at 06:25
I know about bounites, But I can only give them to you if I have asked this question. I meant to say i liked your answer too much but i cannot do anything apart from just an upvote. — Ankit Bajpai, Sep 26 '19 at 06:31
@AnkitBajpai: anyone can give a bounty, as long as they have enough rep, and two days have passed. You are welcome to add bounties to other people's questions. — halfer, Sep 28 '19 at 08:16
Still looking through your response @WernfriedDomscheit but wanted to ask, what would be required if I wanted to pause this scheduled job as well as restart this paused job again? — tonyf, Sep 30 '19 at 00:26
@tonyf, you can use `DBMS_SCHEDULER.DISABLE` and `DBMS_SCHEDULER.ENABLE` — Wernfried Domscheit, Sep 30 '19 at 05:50

Oracle DBMS_SCHEDULE to run at 8am, 12pm, 4pm and 8pm every day of the week

1 Answers1