Oracle Jobs – How to Fix Jobs Running One Hour Apart After Time Change

jobsoraclescheduled-taskstimezone

I have an Oracle 12c database in which I have detected that the scheduled jobs are running one hour apart after the change of time on the server, checking in the database I see that the SYSTIMESTAMP differ with the DBTIMEZONE

SQL> select dbtimezone, sessiontimezone from dual;

DBTIMEZONE

SESSIONTIMEZONE

-04: 00
-05: 00

In the operating system I run:
date + "% Z% z"

CST -0500

Is it correct that these values ​​of SYSTIMESTAMP and DBTIMEZONE are different?, when the time change is made is it necessary to modify something in the database?

Best Answer

Forget anything about DBTIMEZONE, it has no practical use. The only purpose of DBTIMEZONE is: It defines the (internal) time zone to store TIMESTAMP WITH LOCAL TIME ZONE values - nothing else. Thus you cannot modify it when you have a table with TIMESTAMP WITH LOCAL TIME ZONE column and such column contains data.

SYSTIMESTAMP (and SYSATE) is returned in the time zone of database server's operating system!

Thus they can be different of course.

Handling of time zones and daylight saving times for SCHEDULER JOBS is a big complicated. Have a look at Calendaring Syntax documentation:

The calendaring syntax does not allow you to specify a time zone. Instead the Scheduler retrieves the time zone from the start_date argument. If jobs must follow daylight savings adjustments, then you must specify a region name for the time zone of the start_date. For example specifying the start_date time zone as 'US/Eastern' in New York ensures that daylight saving adjustments are automatically applied. If instead, the time zone of the start_date is set to an absolute offset, such as '-5:00', then daylight savings adjustments are not followed and your job execution is off by an hour for half the year.

When start_date is NULL, the Scheduler determines the time zone for the repeat interval as follows:

  1. It checks whether or not the session time zone is a region name. The session time zone can be set by either:

    • Issuing an ALTER SESSION statement, for example:
    • SQL> ALTER SESSION SET time_zone = 'Asia/Shanghai';
    • Setting the ORA_SDTZ environment variable.
    • Setting the Registry value HKLM\SOFTWARE\Oracle\KEY_{Oracle Home Name}\ORA_SDTZ (Windows only).

  2. If the session time zone is an absolute offset instead of a region name, the Scheduler uses the value of the DEFAULT_TIMEZONE Scheduler attribute. For more information, see the SET_SCHEDULER_ATTRIBUTE Procedure.

  3. If the DEFAULT_TIMEZONE attribute is NULL, the Scheduler uses the time zone of SYSTIMESTAMP when the job or window is enabled.

See also https://stackoverflow.com/questions/29271224/how-to-handle-day-light-saving-in-oracle-database/29272926#29272926

Related Question