0

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?

0

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

3

If you found the timezone of your database to be incorrect and you are sure this causes the issue (I have seen this before), you can change it with.

set_time_zone_clause::=

enter image description here

For example:

SQL> select property_value from database_properties where property_name = 'DBTIMEZONE';

PROPERTY_VALUE
--------------------
+01:00

You can set a named time zone like:

SQL> alter database set time_zone = 'Europe/Budapest';

Database altered.

Or a fixed value:

alter database set time_zone = '-05:00';

I used the former. Then restart the database:

SQL> shu immediate
Database closed.
Database dismounted.
startup
ORACLE instance shut down.
SQL> ORACLE instance started.

Total System Global Area 1073737800 bytes
Fixed Size                  8904776 bytes
Variable Size             285212672 bytes
Database Buffers          771751936 bytes
Redo Buffers                7868416 bytes
Database mounted.
Database opened.
SQL> select property_value from database_properties where property_name = 'DBTIMEZONE';

PROPERTY_VALUE
--------------------
Europe/Budapest

SQL>

set_time_zone_clause

This clause has the same semantics in CREATE DATABASE and ALTER DATABASE statements. When used in with ALTER DATABASE, this clause resets the time zone of the database. To determine the time zone of the database, query the built-in function DBTIMEZONE. After setting or changing the time zone with this clause, you must restart the database for the new time zone to take effect.

Keep in mind this may not always work, because:

Oracle Database normalizes all new TIMESTAMP WITH LOCAL TIME ZONE data to the time zone of the database when the data is stored on disk.Oracle Database does not automatically update existing data in the database to the new time zone. Therefore, you cannot reset the database time zone if there is any TIMESTAMP WITH LOCAL TIME ZONE data in the database. You must first delete or export the TIMESTAMP WITH LOCAL TIME ZONE data and then reset the database time zone. For this reason, Oracle does not encourage you to change the time zone of a database that contains data.

For a full description of this clause, refer to set_time_zone_clause in the documentation on CREATE DATABASE.

Also, you should apply the latest DST patch as well, so the database knows when to set the time forward or backward for daylight saving.

1

Scheduler jobs are timezone aware, so if you schedule job A at 13:00 +11:00 and job B at 13:05 'Australia/NSW' then job B will run 5 minutes after job A during the Australian summer and 55 minutes before job A during the Australian winter.

Use select owner, job_name, to_char(start_date,'TZR') tz from all_scheduler_jobs to determine what timezone you have requested the job to run against.

If you are on 12.2, you can use dbms_scheduler.set_attribute(job_name,'NLS_ENV',....); to amend the setting. You'll probably need select dbms_metadata.get_ddl('PROCOBJ',job_name) from dual; to work out the full set of NLS_ENV settings.

If you are before 12.2, I believe you'll need to drop the job and recreate it from a session with the required time zone set.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.