Datetime Data Types and Time Zone Support (2024)

This example illustrates updating DST behavior by using the online method of the procedure. It upgrades an Oracle database to time zone version 14. 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 to the database as the user scott and execute the following statements:

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 time zone version 14. It is assumed that you have the necessary privileges. These privileges are controlled with the DBMS_DST package.

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

Next, you can execute DBMS_DST.FIND_AFFECTED_TABLES to find all the tables in the database that are affected if you upgrade from version 3 to version 14. 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_tables) or you can create your own. In this example, we 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 shown below.

Connect to the database as the user SYS and execute the following statements:

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

Then, you can execute FIND_AFFECTED_TABLES to see which tables are impacted during the upgrade:

connect / as sysdbaBEGIN DBMS_DST.FIND_AFFECTED_TABLES(affected_tables => 'my_affected_tables', log_errors => TRUE, log_errors_table => 'my_error_table');END;/

Then, check the affected tables:

SELECT * FROM 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 my_error_table; TABLE_OWNER TABLE_NAME COLUMN_NAME ROWID ERROR_NUMBER----------- ---------- ----------- ------------------ ------------SCOTT T TS AAAPW3AABAAANzoAAB 1878SCOTT T TS AAAPW3AABAAANzoAAE 1883

These errors can be corrected as described in "Upgrade Error Handling". 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) valueFROM database_propertiesWHERE property_name LIKE 'DST_%'ORDER BY property_name; PROPERTY_NAME VALUE------------------------ --------DST_PRIMARY_TT_VERSION 3DST_SECONDARY_TT_VERSION 0DST_UPGRADE_STATE NONE

Next, you can use the upgrade window to upgrade the affected data. To do this, first set the TIMEZONE_VERSION_UPGRADE_ONLINE initialization parameter to true:

ALTER SYSTEM SET TIMEZONE_VERSION_UPGRADE_ONLINE = true;

Execute DBMS_DST.BEGIN_UPGRADE. In Oracle RAC, all instances must be running. BEGIN_UPGRADE upgrades all dictionary tables in one transaction, so the invocation will either succeed or fail as one whole. During the procedure's execution, all tables with TSTZ data are marked as an upgrade in progress. You cannot add tables containing TSTZ columns to the database, nor can you add TSTZ columns to existing tables. See Oracle Database Upgrade Guide for more information.

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 DBA_TSTZ_TABLES, and is illustrated later in this example.

See Also

Managing Memory

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 converts the data using a default conversion and does not signal an error. See "Upgrade Error Handling" 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 are populated into my_error_table.

EXEC DBMS_DST.BEGIN_UPGRADE(14);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) valueFROM database_propertiesWHERE property_name LIKE 'DST_%'ORDER BY property_name; PROPERTY_NAME VALUE------------------------ ------------DST_PRIMARY_TT_VERSION 14DST_SECONDARY_TT_VERSION 3DST_UPGRADE_STATE NEEDRESTART

Then, note that all tables containing TSTZ data display YES for UPGRADE_IN_PROGRESS:

SELECT owner, table_name, upgrade_in_progress FROM dba_tstz_tables; OWNER TABLE_NAME UPGRADE_IN_PROGRESS----- ------------------------- -------------------SYS WRI$_OPTSTAT_AUX_HISTORY YESSYS WRI$_OPTSTAT_OPR YESSYS OPTSTAT_HIST_CONTROL$ YESSYS SCHEDULER$_JOB YESSYS KET$_AUTOTASK_STATUS YESSYS AQ$_ALERT_QT_S YESSYS AQ$_KUPC$DATAPUMP_QUETAB_S YESDBSNMP MGMT_DB_FEATURE_LOG YESWMSYS WM$VERSIONED_TABLES YESSYS WRI$_OPTSTAT_IND_HISTORY YESSYS OPTSTAT_USER_PREFS$ YESSYS FGR$_FILE_GROUP_FILES YESSYS SCHEDULER$_WINDOW YESSYS WRR$_REPLAY_DIVERGENCE YESSCOTT T YESIX AQ$_ORDERS_QUEUETABLE_S YES...

Note that the upgrade is in progress for table SCOTT.T. If you access SCOTT.T, the database will transparently apply conversion operators to ensure to properly reflect the new time zone rules on this table while the upgrade is in progress.

After BEGIN_UPGRADE has successfully executed, the database continues to operate with the old time zone file. When it is a convenient time to reboot the database, shut down the database and then restart it in normal mode.

Confirm that the new time zone version is the primary version by running the following query:

SELECT * FROM V$TIMEZONE_FILE;FILENAME VERSION CON_ID-------------------- ---------- ----------timezlrg_14.dat 14 0

Now you can perform an upgrade of all tables containing TSTZ data by using DBMS_DST.UPGRADE_DATABASE. All tables must be upgraded, otherwise, you will not be able to end the upgrade window using the END_UPGRADE procedure.

Consider the following choices for running the DBMS_DST.UPGRADE_DATABASE procedure:

Oracle recommends that you use the following code to perform the upgrade. It instructs the database to resolve any errors encountered and relieves you of having to resolve the errors manually.
```
VAR numfail number;BEGIN DBMS_DST.UPGRADE_DATABASE(:numfail); DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:' || :numfail);END;/
```
However, if you prefer to view and resolve errors manually, you can use the following code:
```
VAR numfail number;BEGIN DBMS_DST.UPGRADE_DATABASE(:numfail, parallel => TRUE, log_errors => TRUE, log_errors_table => 'my_error_table', log_triggers_table => 'SYS.DST$TRIGGER_TABLE', error_on_overlap_time => TRUE, error_on_nonexisting_time => TRUE);DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:' || :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 using the following query:
```
SELECT * FROM my_error_table; TABLE_OWNER TABLE_NAME COLUMN_NAME ROWID ERROR_NUMBER----------- ---------- ----------- ------------------ ------------SCOTT T TS AAAO2XAABAAANrgAAD 1878SCOTT T TS AAAO2XAABAAANrgAAE 1878 
```
In the output, you can see the errors having number 1878. This error means that an error has occurred 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:
```
VAR numfail number;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 => 'my_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('Number of tables failed to upgrade:' || :numfail);END;/VAR numfail number;BEGIN DBMS_DST.UPGRADE_TABLE(:numfail, table_list => 'SCOTT.MYMV_T', parallel => TRUE, continue_after_errors => FALSE, log_errors => TRUE, log_errors_table => 'my_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('Number of tables failed to upgrade:' || :numfail);END;/ 
```
The atomic_upgrade parameter enables you to combine the upgrade of the table with its materialized view log.

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

VAR numfail number;BEGIN DBMS_DST.END_UPGRADE(:numfail); DBMS_OUTPUT.PUT_LINE('Number of tables failed to upgrade:' || :numfail);END;/

The upgrade window ends if all the affected tables are upgraded successfully, else the output shows how many tables did not upgrade successfully.