My Oracle Support Banner

Time Zone Data in the Data Dictionary (SYS) and the Effects of a Time Zone File Update (Doc ID 402614.1)

Last updated on DECEMBER 04, 2019

Applies to:

Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Backup Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
Information in this document applies to any platform.

Purpose

Since Oracle10 the datatype TIMESTAMP WITH TIME ZONE is used in the data dictionary. Whilst this is an improvement, it also means that this data is potentially affected by updates of the time zone files.
This note will discuss the commonly known dictionary columns that use this datatype and provide best practices on how to deal with this data when time zone files are updated.

Scope

Before Oracle10 the dictionary does not use TIMESTAMP types, and therefore this note is only applicable to Oracle10 and up.
This note assumes that the reader is familiar with the standard practices surrounding the updates of the Oracle time zone files, a overview of notes discussing each version of DST update is found in
<Note 412160.1> Updated Time Zones in Oracle Time Zone File patches

Details

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Scope
Details
 Affected dictionary data must be dealt with in the same way as user data
 Data is only affected when using an affected time zone and it is in the affected time frame
 Patch early to make it more likely no dictionary data has to be backed up
 Time Zone data in the dictionary
References

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