Time Of Stored TSLTZ Data Changing Due DBTIMEZONE automatically resets to "+00:00" (Doc ID 726224.1)

Symptoms

Generic description:

After one Day the DBTIMEZONE changes from the original value to a +00:00 value

 Seen TSLTZ (TIMESTAMP WITH LOCAL TIME ZONE)data is normalized to/from the DBTIMEZONE this will impact all stored TSLTZ data.

Example of output

before:
SQL> select dbtimezone from dual;

DBTIME
------
+00:00

SQL> select u.name || '.' || o.name || '.' || c.name TSLTZcolumn
2 from sys.obj$ o, sys.col$ c, sys.user$ u
3 where c.type# = 231
4 and o.obj# = c.obj#
and u.user# = o.owner#;
5
no rows selected

SQL> alter database set time_zone='+10:00';

Database altered.

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

Total System Global Area 536870912 bytes
Fixed Size 2031448 bytes
Variable Size 331350184 bytes
Database Buffers 197132288 bytes
Redo Buffers 6356992 bytes
Database mounted.
Database opened.
After:
------
SQL> select dbtimezone from dual;

DBTIME
------
+10:00

after a day when I do the following:
SQL> select dbtimezone from dual;

DBTIME
------
+00:00

SQL> select sessiontimezone from dual;

SESSIONTIMEZONE
---------------------------------------------------------------------------
+10:00

Example of data problem:

Using a table which is a duplicate of the "sys" view "dba_audit_trail" to which the contents of "dba_audit_trail" is copied for permanent storage.
The data in the timestamp and extended_timestamp columns appears to change, losing the timezone offset, producing incorrect results in audit reports.

Changes

Usually detected after changing the database timezone from one value to another or when using TSLTZ data.

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.