-- -- -- Taken from MOS Note:977512.1 -- Status: 20.6.2012 -- -- This is not an official ORACLE script -- Please download always the newest version from support.oracle.com -- -- conn / as sysdba SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; -- check that the output gives -- PROPERTY_NAME VALUE -- ------------------------------ ------------------------------ -- DST_PRIMARY_TT_VERSION -- DST_SECONDARY_TT_VERSION 0 -- DST_UPGRADE_STATE NONE -- DST_PRIMARY_TT_VERSION should match the value found when selecting SELECT version FROM v$timezone_file; -- the actual commands are listed in BOLD -- the next steps use in the statements -- simply replace it with the actual number ( 11, 15 etc) -- of the RDBMS DST version you want to update to. conn / as sysdba -- this alter session might speed up DBMS_DST on some db's -- see Bug 10209691 alter session set "_with_subquery"=materialize; -- to avoid the issue in note 1407273.1 alter session set "_simple_view_merging"=TRUE; -- start prepare window -- these steps will NOT update any data yet. exec DBMS_DST.BEGIN_PREPARE() -- check for prepare status SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; -- output should be -- PROPERTY_NAME VALUE -- ------------------------------ ------------------------------ -- DST_PRIMARY_TT_VERSION -- DST_SECONDARY_TT_VERSION -- DST_UPGRADE_STATE PREPARE -- truncate logging tables if they exist. TRUNCATE TABLE SYS.DST$TRIGGER_TABLE; TRUNCATE TABLE sys.dst$affected_tables; TRUNCATE TABLE sys.dst$error_table; -- log affected data set serveroutput on BEGIN DBMS_DST.FIND_AFFECTED_TABLES (affected_tables => 'sys.dst$affected_tables', log_errors => TRUE, log_errors_table => 'sys.dst$error_table'); END; / -- check what tables have affected data that cannot be resolved automatically. -- if this gives no rows then there is no problem at all SELECT * FROM sys.dst$affected_tables; -- IF previous select gives rows then you can see -- IF there are any rows with a "problem" and what kind of problem there are in those rows -- note that if there are rows in dst$affected_tables -- this does not mean there need to be rows in dst$error_table SELECT * FROM sys.dst$error_table; -- error_on_overlap_time is error number ORA-1883 -- error_on_nonexisting_time is error number ORA-1878 -- for an explanation of the reported data please see -- "Error Handling when Upgrading Time Zone File and Timestamp with Time Zone Data" -- For the "error_on_overlap_time" and "error_on_nonexisting_time" you do not HAVE to -- take action on this data to upgrade the DST version, but it is advised -- to at least to check the results AFTER the update. -- all "error_on_overlap_time" rows SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883'; -- all "error_on_nonexisting_time" rows SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878'; -- check for all other possible problems SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883'); -- end prepare window, the rows above will stay in those tables. EXEC DBMS_DST.END_PREPARE; -- check if this is ended SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME; -- output should be -- PROPERTY_NAME VALUE -- ---------------------------- ------------------------------ -- DST_PRIMARY_TT_VERSION -- DST_SECONDARY_TT_VERSION 0 -- DST_UPGRADE_STATE NONE