My Oracle Support Banner

Usage of utltzuv7.sql before updating time zone files (Doc ID 550739.1)

Last updated on NOVEMBER 29, 2019

Applies to:

Oracle Database - Enterprise Edition - Version to [Release 9.0.1 to 11.1]
Information in this document applies to any platform.
Information in this document applies to any platform.


NOTE: In the images and/or the document content below, the user information and environment data used represents fictitious data from the Oracle sample or bulit-in schema(s), Public Documentation delivered with an Oracle database product or other training material.  Any similarity to actual environments, actual persons, living or dead, is purely coincidental and not intended in any manner.

The changes to DST transition rules may affect existing data of TIMESTAMP WITH TIME ZONE datatype, because of the way Oracle Database stores this data internally. When users enter timestamps with a time zone, the Oracle Database converts the data to UTC, based on the transition rules in the time zone file. When data is retrieved, the reverse conversion from UTC takes place.
For example, the value TIMESTAMP '2007-11-02 12:00:00 America/Los_Angeles', when the version 2 transition rules were in effect, was stored as UTC value '2007-11-02 20:00:00' (plus the original time zone ID for 'America/Los_Angeles'), because the time in Los Angeles was UTC -8 hours (PST).
Under version 7 transition rules, the offset for this day is -7 hours (PDT), because the Daylight Saving Time will end on a different date (November 4th 2007) for this time zone. When users now retrieve the same timestamp a different offset is used to recalculate the UTC time into the America/Los_Angeles time, and they receive TIMESTAMP '2007-11-02 13:00:00 America/Los_Angeles'. So there is a one-hour difference compared to the original data.

The script utltzuv7.sql is provided by Oracle to run before you update your database's time zone files to the latest version. This script scans the database to find out all columns of TIMESTAMP WITH TIME ZONE data type in regular tables.


There are 2 reasons for which you can decide to run the utltzuv7.sql script:

1. Assist in determining if new time zone files need to be applied

It is important to realize that the utltzuv7.sql script only checks stored TIMESTAMP WITH TIME ZONE data in the database, but it does not do anything with TIMESTAMP WITH LOCAL TIME ZONE data, or any other uses of the time zone information in the database (like PL/SQL functions etc.). For a complete overview of all uses of time zone information in Oracle please see:
<Note 406410.1> How to Determine Whether Time Zone Changes Will Affect Your Database

For a flow on how to see if you are affected see
<Note 549825.1> DSTV7 update (Australia 2008 and more) and the impact on the Oracle Database

2. Determine which TSTZ data needs to be backed up before new time zone files are applied.

The utltzuv7.sql script needs to be used to track down the TSTZ data which needs to be backed up.

The rest of this note will
* show standard output of the utltzuv7.sql when affected data is detected in an Oracle9i,10g or 11g database
* discuss how to use the utltzuv7.sql script to determine which data needs to be "saved" before applying the DSTV7 patch to an Oracle9i,10g or 11g database, and it will discuss how to save and restore this data.


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
 1) Installing utltzuv7.sql
 2) running utltzuv7.sql
 3) Sample output of select * from sys.sys_tzuv2_temptab
 4) Which actions to take?
 5) Manually performing the backup and restore
 6) Automating the backup and restore
 7) In Summary
 8) Further checks of other time zone data.

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