TIMESTAMP WITH TIME ZONE data (Oracle Database 11gR1 to 11gR2 Change)

posted Sep 24, 2010, 12:03 PM by Sachchida Ojha

When time zone file is upgraded to a new version due to Daylight Saving Time changes, TIMESTAMP WITH TIMEZONE (TSTZ) data could become stale. In previous releases, database administrators ran the SQL script utltzuv2.sql to detect TSTZ data affected by the time zone version changes and then had to carry out extensive manual procedures to update the TSTZ data.

In Oracle Database 11g Release 2, TSTZ data can be updated transparently with minimal manual procedures and system downtime using the newly provided DBMS_DST PL/SQL packages. For convenience, time zone files from version 1 to 11 are automatically installed in the directory of $ORACLE_HOME/ORACORE/ZONEINFO/. Database administrators have the option of staying with the current time zone version or upgrade to the latest. Oracle recommends upgrading the server to the latest timezone version.

Time zone files are also installed on the clients. Starting in Oracle Database 11g Release 2, there is no longer a need for clients to upgrade their time zone files immediately. Upgrades can be done at a time when it is most convenient to the system administrator. However, there could be a small performance penalty when the client and server use different time zone versions.