oracle11gtimezonedstjob-schedulingdbms-scheduler

How to schedule an Oracle dbms_scheduler Job timezone and DST safely


I am trying to setup a DBMS_SCHEDULER Job to run exactly at 1 AM on 1st of January every year on Oracle 11g. How to setup its attributes to be absolutely sure it wont get executed in wrong hour, because of timezone differences nor Daylight Savings Time.

I have spent plenty of time going through Oracle documentation, but I have still not reached the level of certainity.

Just btw, here are the rules which I found and consider relevant to the subject:

Job attributes

start_date This attribute specifies the first date on which this job is scheduled to start. If start_date and repeat_interval are left null, then the job is scheduled to run as soon as the job is enabled. For repeating jobs that use a calendaring expression to specify the repeat interval, start_date is used as a reference date. The first time the job will be scheduled to run is the first match of the calendaring expression that is on or after the current date. The Scheduler cannot guarantee that a job will execute on an exact time because the system may be overloaded and thus resources unavailable.

repeat_interval This attribute specifies how often the job should repeat. You can specify the repeat interval by using calendaring or PL/SQL expressions. The expression specified is evaluated to determine the next time the job should run. If repeat_interval is not specified, the job will run only once at the specified start date. See "Calendaring Syntax" for further information.

Rules in Calendaring syntax


Solution

  • I am unsure if this answer truly passes the rules of an answer on this site, but after spending a lot of time googling I came up with the following solution:

    start_date      => CAST(trunc(sysdate, 'YEAR')+2/24 AS TIMESTAMP) at time zone 'Europe/Berlin'
    

    I believe this is closest to safest solution because:

    I would be happier with the solution, if I would be absolutely clear on when the job actually gets executed with the respect of local time of a server, SESSIONTIMEZONE, DBTIMEZONE, start_date Time Zone and a DBMS_SCHEDULER time zone.

    I am also unhappy with the Time Zone specification, since its has 4 abbreviations linked with it - LMT, CET, CEST, CEMT, where CEST seems to me like being completely wrong. My target is to use CET with Daylight savings(winter!=summer).