4 Datetime Data Types and Time Zone Support

DATE Data Type

The DATE data type stores date and time information. Although date and time information can be represented in both character and number data types, the DATE data type has special associated properties. For each DATE value, Oracle Database stores the following information: century, year, month, date, hour, minute, and second.

You can specify a date value by:

• Specifying the date value as a literal

• Converting a character or numeric value to a date value with the TO_DATE function

A date can be specified as an ANSI date literal or as an Oracle Database date value.

An ANSI date literal contains no time portion and must be specified in exactly the following format:

DATE 'YYYY-MM-DD'

The following is an example of an ANSI date literal:

DATE '1998-12-25'

Alternatively, you can specify an Oracle Database date value as shown in the following example:

TO_DATE('1998-DEC-25 17:30','YYYY-MON-DD HH24:MI','NLS_DATE_LANGUAGE=AMERICAN')

The default date format for an Oracle Database date value is derived from the NLS_DATE_FORMAT and NLS_DATE_LANGUAGE initialization parameters. The date format in the example includes a two-digit number for the day of the month, an abbreviation of the month name, the last two digits of the year, and a 24-hour time designation. The specification for NLS_DATE_LANGUAGE is included because 'DEC' is not a valid value for MON in all locales.

Oracle Database automatically converts character values that are in the default date format into date values when they are used in date expressions.

If you specify a date value without a time component, then the default time is midnight. If you specify a date value without a date, then the default date is the first day of the current month.

Oracle Database DATE columns always contain fields for both date and time. If your queries use a date format without a time portion, then you must ensure that the time fields in the DATE column are set to midnight. You can use the TRUNC (date) SQL function to ensure that the time fields are set to midnight, or you can make the query a test of greater than or less than (<, <=, >=, or >) instead of equality or inequality (= or !=). Otherwise, Oracle Database may not return the query results you expect.

TIMESTAMP Data Type

The TIMESTAMP data type is an extension of the DATE data type. It stores year, month, day, hour, minute, and second values. It also stores fractional seconds, which are not stored by the DATE data type.

Specify the TIMESTAMP data type as follows:

TIMESTAMP [(fractional_seconds_precision)]

fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field. It can be a number in the range 0 to 9. The default is 6.

For example, '26-JUN-02 09:39:16.78' shows 16.78 seconds. The fractional seconds precision is 2 because there are 2 digits in '78'.

You can specify the TIMESTAMP literal in a format like the following:

TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF'

Using the example format, specify TIMESTAMP as a literal as follows:

TIMESTAMP '1997-01-31 09:26:50.12'

The value of NLS_TIMESTAMP_FORMAT initialization parameter determines the timestamp format when a character string is converted to the TIMESTAMP data type. NLS_DATE_LANGUAGE determines the language used for character data such as MON.

TIMESTAMP WITH TIME ZONE Data Type

TIMESTAMP WITH TIME ZONE is a variant of TIMESTAMP that includes a time zone region name or time zone offset in its value. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time). Specify the TIMESTAMP WITH TIME ZONE data type as follows:

TIMESTAMP [(fractional_seconds_precision)] WITH TIME ZONE

fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field.

You can specify TIMESTAMP WITH TIME ZONE as a literal as follows:

TIMESTAMP '1997-01-31 09:26:56.66 +02:00'

Two TIMESTAMP WITH TIME ZONE values are considered identical if they represent the same instant in UTC, regardless of the TIME ZONE offsets stored in the data. For example, the following expressions have the same value:

TIMESTAMP '1999-01-15 8:00:00 -8:00'
TIMESTAMP '1999-01-15 11:00:00 -5:00'

You can replace the UTC offset with the TZR (time zone region) format element. The following expression specifies America/Los_Angeles for the time zone region:

TIMESTAMP '1999-01-15 8:00:00 America/Los_Angeles'

To eliminate the ambiguity of boundary cases when the time switches from Standard Time to Daylight Saving Time, use both the TZR format element and the corresponding TZD format element. The TZD format element is an abbreviation of the time zone region with Daylight Saving Time information included. Examples are PST for U. S. Pacific Standard Time and PDT for U. S. Pacific Daylight Time. The following specification ensures that a Daylight Saving Time value is returned:

TIMESTAMP '1999-10-29 01:30:00 America/Los_Angeles PDT'

If you do not add the TZD format element, and the datetime value is ambiguous, then Oracle Database returns an error if you have the ERROR_ON_OVERLAP_TIME session parameter set to TRUE. If ERROR_ON_OVERLAP_TIME is set to FALSE (the default value), then Oracle Database interprets the ambiguous datetime as Standard Time.

The default date format for the TIMESTAMP WITH TIME ZONE data type is determined by the value of the NLS_TIMESTAMP_TZ_FORMAT initialization parameter.

TIMESTAMP WITH LOCAL TIME ZONE Data Type

TIMESTAMP WITH LOCAL TIME ZONE is another variant of TIMESTAMP. It differs from TIMESTAMP WITH TIME ZONE as follows: data stored in the database is normalized to the database time zone, and the time zone offset is not stored as part of the column data. When users retrieve the data, Oracle Database returns it in the users' local session time zone. The time zone offset is the difference (in hours and minutes) between local time and UTC (Coordinated Universal Time, formerly Greenwich Mean Time).

Specify the TIMESTAMP WITH LOCAL TIME ZONE data type as follows:

TIMESTAMP [(fractional_seconds_precision)] WITH LOCAL TIME ZONE

fractional_seconds_precision is optional and specifies the number of digits in the fractional part of the SECOND datetime field.

There is no literal for TIMESTAMP WITH LOCAL TIME ZONE, but TIMESTAMP literals and TIMESTAMP WITH TIME ZONE literals can be inserted into a TIMESTAMP WITH LOCAL TIME ZONE column.

The default date format for TIMESTAMP WITH LOCAL TIME ZONE is determined by the value of the NLS_TIMESTAMP_FORMAT initialization parameter.

Inserting Values into Datetime Data Types

You can insert values into a datetime column in the following ways:

• Insert a character string whose format is based on the appropriate NLS format value

• Insert a literal

• Insert a literal for which implicit conversion is performed

• Use the TO_TIMESTAMP, TO_TIMESTAMP_TZ, or TO_DATE SQL function

The following examples show how to insert data into datetime data types.

SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';

SQL> CREATE TABLE table_dt (c_id NUMBER, c_dt DATE);

SQL> INSERT INTO table_dt VALUES(1, '01-JAN-2003');

SQL> INSERT INTO table_dt VALUES(2, DATE '2003-01-01');

SQL> INSERT INTO table_dt VALUES(3, TIMESTAMP '2003-01-01 00:00:00 America/Los_Angeles');

SQL> INSERT INTO table_dt VALUES(4, TO_DATE('01-JAN-2003', 'DD-MON-YYYY'));

SQL> SELECT * FROM table_dt;

C_ID       C_DT
---------- --------------------
1          01-JAN-2003 00:00:00
2          01-JAN-2003 00:00:00
3          01-JAN-2003 00:00:00
4          01-JAN-2003 00:00:00

SQL> ALTER SESSION SET NLS_TIMESTAMP_FORMAT='DD-MON-YY HH:MI:SSXFF';

SQL> CREATE TABLE table_ts(c_id NUMBER, c_ts TIMESTAMP);

SQL> INSERT INTO table_ts VALUES(1, '01-JAN-2003 2:00:00');

SQL> INSERT INTO table_ts VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

SQL> INSERT INTO table_ts VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');

SQL> SELECT * FROM table_ts;

C_ID        C_TS
----------  -----------------------------
1           01-JAN-03 02:00:00.000000 AM
2           01-JAN-03 02:00:00.000000 AM
3           01-JAN-03 02:00:00.000000 AM

SQL> ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='DD-MON-RR HH:MI:SSXFF AM TZR';

SQL> ALTER SESSION SET TIME_ZONE='-7:00';

SQL> CREATE TABLE table_tstz (c_id NUMBER, c_tstz TIMESTAMP WITH TIME ZONE);

SQL> INSERT INTO table_tstz VALUES(1, '01-JAN-2003 2:00:00 AM -07:00');

SQL> INSERT INTO table_tstz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

SQL> INSERT INTO table_tstz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -8:00');

SQL> SELECT * FROM table_tstz;

C_ID        C_TSTZ
----------  ------------------------------------
1           01-JAN-03 02:00.00:000000 AM -07:00
2           01-JAN-03 02:00:00.000000 AM -07:00
3           01-JAN-03 02:00:00.000000 AM -08:00

SQL> ALTER SESSION SET TIME_ZONE='-07:00';

SQL> CREATE TABLE table_tsltz (c_id NUMBER, c_tsltz TIMESTAMP WITH LOCAL TIME ZONE);

SQL> INSERT INTO table_tsltz VALUES(1, '01-JAN-2003 2:00:00');

SQL> INSERT INTO table_tsltz VALUES(2, TIMESTAMP '2003-01-01 2:00:00');

SQL> INSERT INTO table_tsltz VALUES(3, TIMESTAMP '2003-01-01 2:00:00 -08:00');

SQL> SELECT * FROM table_tsltz;

C_ID        C_TSLTZ
----------  ------------------------------------
1           01-JAN-03 02.00.00.000000 AM
2           01-JAN-03 02.00.00.000000 AM
3           01-JAN-03 03.00.00.000000 AM

Choosing a TIMESTAMP Data Type

Use the TIMESTAMP data type when you need a datetime value to record the time of an event. For example, you can store information about the times when workers punch a timecard in and out of their assembly line workstations. The application can be used across time zones. Consider a banking company with offices around the world. It can record a deposit to an account at 11 a.m. in London and a withdrawal of the same amount from the account at 9 a.m. in New York, by normalizing the time zones. The TIMESTAMP data type uses 7 or 11 bytes of storage.

Use the TIMESTAMP WITH TIME ZONE data type when the datetime value represents a future local time or the time zone information needs to be recorded with the value. Consider a scheduled appointment in a local time. The future local time may need to be adjusted if the time zone definition, such as daylight saving rule, changes. Otherwise, the value can become incorrect. This data type is most immune to such impact.

The TIMESTAMP WITH TIME ZONE data type requires 13 bytes of storage, or two more bytes of storage than the TIMESTAMP and TIMESTAMP WITH LOCAL TIME ZONE data types because it stores time zone information. The time zone is stored as a time zone region name or as an offset from UTC. The data is available for display or calculations without additional processing. A TIMESTAMP WITH TIME ZONE column cannot be used as a primary key. If an index is created on a TIMESTAMP WITH TIME ZONE column, it becomes a function-based index.

The TIMESTAMP WITH LOCAL TIME ZONE data type stores the timestamp without time zone information. It normalizes the data to the database time zone every time the data is sent to and from a client. It requires 11 bytes of storage.

The TIMESTAMP WITH LOCAL TIME ZONE data type is appropriate when the original time zone is of no interest, but the relative times of events are important and daylight saving adjustment does not have to be accurate. The time zone conversion that this data type performs to and from the database time zone is asymmetrical, due to the daylight saving adjustment. Because this data type does not preserve the time zone information, it does not distinguish values near the adjustment in fall, whether they are daylight saving time or standard time. This confusion between distinct instants can cause an application to behave unexpectedly, especially if the adjustment takes place during the normal working hours of a user.

Note that some regions, such as Brazil and Israel, that update their Daylight Saving Transition dates frequently and at irregular periods, are particularly susceptible to time zone adjustment issues. If time information from these regions is key to your application, you may want to consider using one of the other datetime types.

INTERVAL YEAR TO MONTH Data Type

INTERVAL YEAR TO MONTH stores a period of time using the YEAR and MONTH datetime fields. Specify INTERVAL YEAR TO MONTH as follows:

INTERVAL YEAR [(year_precision)] TO MONTH

year_precision is the number of digits in the YEAR datetime field. Accepted values are 0 to 9. The default value of year_precision is 2.

Interval values can be specified as literals. There are many ways to specify interval literals. The following is one example of specifying an interval of 123 years and 2 months. The year precision is 3.

INTERVAL '123-2' YEAR(3) TO MONTH

INTERVAL DAY TO SECOND Data Type

INTERVAL DAY TO SECOND stores a period of time in terms of days, hours, minutes, and seconds. Specify this data type as follows:

INTERVAL DAY [(day_precision)] TO SECOND [(fractional_seconds_precision)]

day_precision is the number of digits in the DAY datetime field. Accepted values are 0 to 9. The default is 2.

fractional_seconds_precision is the number of digits in the fractional part of the SECOND datetime field. Accepted values are 0 to 9. The default is 6.

The following is one example of specifying an interval of 4 days, 5 hours, 12 minutes, 10 seconds, and 222 thousandths of a second. The fractional second precision is 3.

INTERVAL '4 5:12:10.222' DAY TO SECOND(3)

Interval values can be specified as literals. There are many ways to specify interval literals.

Inserting Values into Interval Data Types

You can insert values into an interval column in the following ways:

• Insert an interval as a literal. For example:

  INSERT INTO table1 VALUES (INTERVAL '4-2' YEAR TO MONTH);
  

  This statement inserts an interval of 4 years and 2 months.

  Oracle Database recognizes literals for other ANSI interval types and converts the values to Oracle Database interval values.

• Use the NUMTODSINTERVAL, NUMTOYMINTERVAL, TO_DSINTERVAL, and TO_YMINTERVAL SQL functions.

  See Also:

  "Datetime SQL Functions"

Example of Updating Daylight Saving Time Behavior

This example illustrates updating DST behavior to Oracle Database 11g, release 2. First, assume that your current database is using time zone version 3, and also assume you have an existing table t, which contains timestamp with time zone data.

CONNECT scott/tiger
DROP TABLE t;
CREATE TABLE t (c NUMBER, mark VARCHAR(25), ts TIMESTAMP WITH TIME ZONE);
 
INSERT INTO t VALUES(1, 'not_affected', 
                    to_timestamp_tz('22-sep-2006 13:00:00 america/los_angeles',
                                     'dd-mon-yyyy hh24:mi:ss tzr tzd'));
INSERT INTO t VALUES(4, 'affected_err_exist',
                     to_timestamp_tz('11-mar-2007 00:30:00 america/st_johns',
                                     'dd-mon-yyyy hh24:mi:ss tzr tzd'));
INSERT INTO t VALUES(6, 'affected_no_err',
                     to_timestamp_tz('11-mar-2007 01:30:00 america/st_johns',
                                     'dd-mon-yyyy hh24:mi:ss tzr tzd'));
INSERT INTO t VALUES(14, 'affected_err_dup', 
                     to_timestamp_tz('21-sep-2006 23:30:00 egypt',
                                     'dd-mon-yyyy hh24:mi:ss tzr tzd'));
COMMIT;
 

Then, optionally, you can start a prepare window to check the affected data and potential semantic errors where there is an overlap or non-existing time. To do this, you should start a window for preparation to migrate to version 4. It is assumed that you have the necessary privileges. These privileges are controlled with the DBMS_DST package. See PL/SQL Packages and Types Reference for more information.

As an example, first, prepare the window.

conn / AS SYSDBA
set serveroutput on
EXEC DBMS_DST.BEGIN_PREPARE(4);
 
A prepare window has been successfully started.
 
PL/SQL procedure successfully completed.
 

Note that the argument 4 causes version 4 to be used in this statement. After this window is successfully started, you can check the status of the DST in DATABASE_PROPERTIES, as in the following:

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
 

You will see output resembling the following:

PROPERTY_NAME                  VALUE
---------------------------    ---------
DST_PRIMARY_TT_VERSION         3
DST_SECONDARY_TT_VERSION       4
DST_UPGRADE_STATE              PREPARE

Next, you can invoke DBMS_DST.FIND_AFFECTED_TABLES to find all the tables in the database that will be affected if you upgrade from version 3 to version 4. This table contains the table owner, table name, column name, row count, and error count. Here, you have the choice of using the defaults for error tables (sys.dst$error_table) and affected tables (sys.dst$affected_table) or you can create your own. In this example, we will create our own tables by using DBMS_DST.CREATE_ERROR_TABLE and DBMS_DST.CREATE_AFFECTED_TABLE and then pass to FIND_AFFECTED_TABLES, as in the following:

EXEC DBMS_DST.CREATE_AFFECTED_TABLE('scott.my_affected_tables');
EXEC DBMS_DST.CREATE_ERROR_TABLE('scott.my_error_table');
 

It is a good idea to make sure that there are no rows in these tables. You can do this by truncating the tables, as in the following:

TRUNCATE TABLE scott.my_affected_tables;
TRUNCATE TABLE scott.my_error_table;

Then, you can invoke FIND_AFFECTED_TABLES to see which tables will be impacted during the upgrade:

EXEC DBMS_DST.FIND_AFFECTED_TABLES(affected_tables  => 'scott.my_affected_tables',
                                   log_errors       => TRUE,
                                   log_errors_table => 'scott.my_error_table');

Then, check the affected tables:

SELECT * FROM scott.my_affected_tables;
 
TABLE_OWNER   TABLE_NAME   COLUMN_NAM   ROW_COUNT  ERROR_COUNT
-----------   ----------   ----------   ---------  -----------
SCOTT         T            TS                   3            2
 

Then, check the error table:

SELECT * FROM scott.my_error_table;
 
TABLE_OWNER   TABLE_NAME  COLUMN_NAME   ROWID                ERROR_NUMBER
-----------   ----------  -----------   ------------------   ------------
SCOTT         T           TS            AAAPW3AABAAANzoAAB           1878
SCOTT         T           TS            AAAPW3AABAAANzoAAE           1883
 

These errors can be corrected by seeing "Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data". Then, end the prepare window, as in the following statement:

EXEC DBMS_DST.END_PREPARE;
 
A prepare window has been successfully ended.
 
PL/SQL procedure successfully completed.
 

After this, you can check the DST status in DATABASE_PROPERTIES:

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
 
PROPERTY_NAME                 VALUE
------------------------      --------
DST_PRIMARY_TT_VERSION        3
DST_SECONDARY_TT_VERSION      0
DST_UPGRADE_STATE             NONE
 

Next, you can use the upgrade window to upgrade the affected data. To do this, first, start an upgrade window. Note that the database needs to be opened in UPGRADE mode before you can execute DBMS_DST.BEGIN_UPGRADE. In RAC, only one instance can be started. BEGIN_UPGRADE will upgrade all dictionary tables in one transaction, so the invocation will either succeed or fail as one whole. During the procedure's execution, all user tables with TSTZ data will be marked as an upgrade in progress. See Oracle Database Upgrade Guide for more information.

Also, only SYSDBA can start an upgrade window. If you do not open the database in UPGRADE mode and invoke BEGIN_UPGRADE, you will see the following error:

EXEC DBMS_DST.BEGIN_UPGRADE(4);
BEGIN DBMS_DST.BEGIN_UPGRADE(4); END;
 
*
ERROR at line 1:
ORA-56926: database must be in UPGRADE mode in order to start an upgrade window
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_DST", line 1021
ORA-06512: at line 1
 

So, BEGIN_UPGRADE upgrades system tables that contain TSTZ data and marks user tables (containing TSTZ data) with the UPGRADE_IN_PROGRESS property. This can be checked in ALL_TSTZ_TABLES, and is illustrated later in this example.

There are two parameters in BEGIN_UPGRADE that are for handling semantic errors: error_on_overlap_time (error number ORA-1883) and error_on_nonexisting_time (error number ORA-1878). If the parameters use the default setting of FALSE, Oracle will convert the data using a default conversion and will not signal an error. See "Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data" for more information regarding what they mean, and how to handle errors.

The following call can automatically correct semantic errors based on some default values when you upgrade the dictionary tables. If you do not ignore semantic errors, and you do have such errors in the dictionary tables, BEGIN_UPGRADE will fail. These semantic errors will be populated into sys.dst$error_table.

EXEC DBMS_DST.BEGIN_UPGRADE(4);
An upgrade window has been successfully started.
 
PL/SQL procedure successfully completed.
 

After this, you can check the DST status in DATABASE_PROPERTIES, as in the following:

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;
 
PROPERTY_NAME               VALUE
------------------------    --------
DST_PRIMARY_TT_VERSION             4
DST_SECONDARY_TT_VERSION           3
DST_UPGRADE_STATE            UPGRADE
 

Then, check which user tables are marked with UPGRADE_IN_PROGRESS:

SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES;
 
OWNER   TABLE_NAME                      UPGRADE_IN_PROGRESS
-----   -------------------------       -------------------
SYS     WRI$_OPTSTAT_AUX_HISTORY        NO
SYS     WRI$_OPTSTAT_OPR                NO
SYS     OPTSTAT_HIST_CONTROL$           NO
SYS     SCHEDULER$_JOB                  NO
SYS     KET$_AUTOTASK_STATUS            NO
SYS     AQ$_ALERT_QT_S                  NO
SYS     AQ$_KUPC$DATAPUMP_QUETAB_S      NO
DBSNMP  MGMT_DB_FEATURE_LOG             NO
WMSYS   WM$VERSIONED_TABLES             NO
SYS     WRI$_OPTSTAT_IND_HISTORY        NO
SYS     OPTSTAT_USER_PREFS$             NO
SYS     FGR$_FILE_GROUP_FILES           NO
SYS     SCHEDULER$_WINDOW               NO
SYS     WRR$_REPLAY_DIVERGENCE          NO
SCOTT   T                               YES
IX      AQ$_ORDERS_QUEUETABLE_S         YES
...
 

In this output, dictionary tables (in the SYS schema) have already been upgraded by BEGIN_UPGRADE. User tables, such as SCOTT.T, have not been and are in progress.

Now you can perform an upgrade of user tables using DBMS_DST.UPGRADE_DATABASE. All tables need to be upgraded, otherwise, you will not be able to end the upgrade window using the END_UPGRADE procedure. Before this step, you must restart the database in normal mode. An example of the syntax is as follows:

VAR numfail number
BEGIN
   DBMS_DST.UPGRADE_DATABASE(:numfail,
            parallel                  => TRUE,
            log_errors                => TRUE,
            log_errors_table          => 'SYS.DST$ERROR_TABLE',
            log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
            error_on_overlap_time     => TRUE,
            error_on_nonexisting_time => TRUE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

If there are any errors, you should correct them and use UPGRADE_TABLE on the individual tables. In that case, you may need to handle tables related to materialized views, such as materialized view base tables, materialized view log tables, and materialized view container tables. There are a couple of considerations to keep in mind when upgrading these tables. First, the base table and its materialized view log table have to be upgraded atomically. Next, the materialized view container table has to be upgraded after all its base tables and the materialized view log tables have been upgraded. In general, Oracle recommends that you handle semantic errors by letting Oracle Database take the default action.

For the sake of this example, let us assume there were some errors in SCOTT.T after you ran UPGRADE_DATABASE. In that case, you can check these errors by issuing:

SELECT * FROM scott.error_table;
 
TABLE_OWNER   TABLE_NAME  COLUMN_NAME   ROWID                ERROR_NUMBER
-----------   ----------  -----------   ------------------   ------------
SCOTT         T           TS            AAAO2XAABAAANrgAAD           1878
SCOTT         T           TS            AAAO2XAABAAANrgAAE           1878
 

From this output, you can see that error number 1878 has occurred. This error means that an error has been thrown for a non-existing time.

To continue with this example, assume that SCOTT.T has a materialized view log scott.mlog$_t, and that there is a single materialized view on SCOTT.T. Then, assume that this 1878 error has been corrected.

Finally, you can upgrade the table, materialized view log and materialized view as follows:

BEGIN
  DBMS_DST.UPGRADE_TABLE(:numfail,
           table_list                => 'SCOTT.t, SCOTT.mlog$_T',
           parallel                  => TRUE,
           continue_after_errors     => FALSE,
           log_errors                => TRUE,
           log_errors_table          => 'SYS.DST$ERROR_TABLE',
           error_on_overlap_time     => FALSE,
           error_on_nonexisting_time => TRUE,
           log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
           atomic_upgrade            => TRUE);
 
 DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

BEGIN
  DBMS_DST.UPGRADE_TABLE(:numfail, 
           table_list                => 'SCOTT.MYMV_T',
           parallel                  => TRUE,
           continue_after_errors     => FALSE,
           log_errors                => TRUE, 
           log_errors_table          => 'SYS.DST$ERROR_TABLE',
           error_on_overlap_time     => FALSE,
           error_on_nonexisting_time => TRUE,
           log_triggers_table        => 'SYS.DST$TRIGGER_TABLE',
           atomic_upgrade            => TRUE);
 
  DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/ 

The atomic_upgrade parameter enables you to combine the upgrade of the table with its materialized view log. See PL/SQL Packages and Types Reference for more information.

After all the tables are upgraded, you can invoke END_UPGRADE to end an upgrade window, as in the following:

BEGIN
  DBMS_DST.END_UPGRADE(:numfail OUT BINARY_INTEGER);
END;
/

If no other table was upgraded successfully, the END_UPGRADE statement will fail.

See PL/SQL Packages and Types Reference for more information regarding the DBMS_DST package.

Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data

There are three major semantic errors that can occur during an upgrade. The first is when an existing time becomes a non-existing time, the second is when a time becomes duplicated, and the third is when a duplicate time becomes a non-duplicate time.

As an example of the first case, consider the change from Pacific Standard Time (PST) to Pacific Daylight Saving Time (PDT) in 2007. This change takes place on Mar-11-2007 at 2AM according to version 4 when the clock moves forward one hour to 3AM and produces a gap between 2AM and 3AM. In version 2, this time change occurs on Apr-01-2007. If you upgrade the time zone file from version 2 to version 4, any time in the interval between 2AM and 3AM on Mar-11-2007 is not valid, and will raise an error (ORA-1878) if ERROR_ON_NONEXISTING_TIME is set to TRUE. Therefore, there is a non-existing time problem. When ERROR_ON_NONEXISTING_TIME is set to FALSE, which is the default value for this parameter, the error will not be reported and Oracle preserves UTC time in this case. For example, "Mar-11-2007 02:30 PST" in version 2 becomes "Mar-11-2007 03:30 PDT" in version 4 as they both are translated to the same UTC timestamp.

An example of the second case occurs when changing from PDT to PST. For example, in version 4 for 2007, the change occurs on Nov-04-2007, when the time falls back from 2AM to 1AM. This means that times in the interval <1AM, 2AM> on Nov-04-2007 can appear twice, once with PST and once with PDT. In version 2, this transition occurs on Oct-28-2007 at 2AM. Thus, any timestamp within <1AM, 2AM> on Nov-04-2007, which is uniquely identified in version 2, will result in an error (ORA-1883) in version 4, if ERROR_ON_OVERLAP_TIME is set to TRUE. If you leave this parameter on its default setting of FALSE, then the UTC timestamp value is preserved and no error will be raised. In this situation, if you change the version from 2 to 4, timestamp "Nov-04-2007 01:30 PST" in version 2 becomes "Nov-04-2007 01:30 PST" in version 4.

The third case happens when a duplicate time becomes a non-duplicate time. Consider the transition from PDT to PST in 2007, for example, where <1AM, 2AM> on Oct-28-2007 in version 2 is the overlapped interval. Then both "Oct-28-2007 01:30 PDT" and "Oct-28-2007 01:30 PST" are valid timestamps in version 2. If ERROR_ON_OVERLAP_TIME is set to TRUE, an ORA-1883 error will be raised during an upgrade from version 2 to version 4. If ERROR_ON_OVERLAP_TIME is set to FALSE (the default value of this parameter), however, the LOCAL time will be preserved and no error will be reported. In this case, both "Oct-28-2007 01:30 PDT" and "Oct-28-2007 01:30 PST" in version 2 convert to the same "Oct-28-2007 01:30 PDT" in version 4. Note that setting ERROR_ON_OVERLAP_TIME to FALSE can potentially cause some time sequences to be reversed. For example, a job (Job A) scheduled at "Oct-28-2007 01:45 PDT" in version 2 is supposed to be executed before a job (Job B) scheduled at "Oct-28-2007 01:30 PST". After the upgrade to version 4, Job A is scheduled at "Oct-28-2007 01:45 PDT" and Job B remains at "Oct-28-2007 01:30 PDT", resulting in Job B being executed before Job A. Even though unchained scheduled jobs are not guaranteed to be executed in a certain order, this issue should be kept in mind when setting up scheduled jobs.

See PL/SQL Packages and Types Reference for more information regarding how to use these parameters.