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.
See Also:
Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_DST
package
As an example, first, prepare the window.
connect / as sysdbaset serveroutput onEXEC DBMS_DST.BEGIN_PREPARE(14); A prepare window has been successfully started. PL/SQL procedure successfully completed.
Note that the argument 14 causes the time zone version 14 to be used in this statement. After this window is successfully started, you can check the status of the DST in DATABASE_PROPERTIES
as shown in the following example:
SELECT property_name, SUBSTR(property_value, 1, 30) valueFROM database_propertiesWHERE property_name LIKE 'DST_%'ORDER BY property_name;
You will see the output similar to the following:
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.
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 ranUPGRADE_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 logscott.mlog$_t
, and that there is a single materialized view onSCOTT.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.