0

Does anyone know what is the default value for the default_timezone attribute of the dbms_scheduler? That is, without this value ever being set manually.

For example, on one of my servers, the following statement returns the value 'Europe/Vienna'.

BEGIN
   DBMS_SCHEDULER.get_scheduler_attribute('default_timezone', :VALUE);
END;
/

while /usr/bin/timedatectl on the server returns

      Local time: Do 2023-03-30 13:27:28 CEST
  Universal time: Do 2023-03-30 11:27:28 UTC
        RTC time: Do 2023-03-30 11:27:27
       Time zone: Europe/Berlin (CEST, +0200)
     NTP enabled: n/a
NTP synchronized: no
 RTC in local TZ: no
      DST active: yes
 Last DST change: DST began at
                  So 2023-03-26 01:59:59 CET
                  So 2023-03-26 03:00:00 CEST
 Next DST change: DST ends (the clock jumps one hour backwards) at
                  So 2023-10-29 02:59:59 CEST
                  So 2023-10-29 02:00:00 CET

I did not find anything regarding this in the Oracle documentation.

D. Mika
  • 2,577
  • 1
  • 13
  • 29

1 Answers1

0

Have a look at DBMS_SCHEDULER 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.

  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.

Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110