Rem Rem $Header: rdbms/admin/utltzuv2.sql /main/12 2010/04/20 16:19:45 huagli Exp $ Rem Rem utltzuv2.sql Rem Rem Copyright (c) 2003, 2010, Oracle and/or its affiliates. Rem All rights reserved. Rem Rem NAME Rem utltzuv2.sql - time zone file upgrade to a new version script Rem Rem DESCRIPTION Rem The contents of the files timezone.dat and timezlrg.dat are Rem usually updated to a new version to reflect the transition rule Rem changes for some time zone region names. The transition rule Rem changes of some time zones might affect the column data of Rem TIMESTAMP WITH TIME ZONE data type. For example, if users Rem enter TIMESTAMP '2003-02-17 09:00:00 America/Sao_Paulo', Rem we convert the data to UTC based on the transition rules in the Rem time zone file and store them on the disk. So '2003-02-17 11:00:00' Rem along with the time zone id for 'America/Sao_Paulo' is stored Rem because the offset for this particular time is '-02:00' . Now the Rem transition rules are modified and the offset for this particular Rem time is changed to '-03:00'. when users retrieve the data, Rem they will get '2003-02-17 08:00:00 America/Sao_Paulo'. There is Rem one hour difference compared to the original value. Rem Rem Refer to $ORACLE_HOME/oracore/zoneinfo/readme.txt for detailed Rem information about time zone file updates. Rem Rem This script should be run before you update your database's Rem time zone file to the latest version. This is a pre-update script. Rem Rem This script first determines the time zone version currently in use Rem before the upgrade. It then queries an external table to get all the Rem affected timezone regions between the current version (version before Rem the update) and the latest one. This external table points to the Rem file of $ORACLE_HOME/oracore/zoneinfo/timezdif.csv, which contains Rem all the affected time zone names in each version. Please make sure Rem that you have the latest version of the timezdif.csv (the one Rem corresponding to the latest time zone data files) before you run this Rem check script. Rem Rem Then, this script scans the database to find out all columns Rem of TIMESTAMP WITH TIME ZONE data type. If the column is defined Rem directly on TIMESTAMP WITH TIME ZONE data type or ADT data type Rem defined on TIMESTAMP WITH TIME ZONE excluding VARRAY, the script Rem also finds out how many rows might be affected by checking whether Rem the column data contain the values for the affected time zone names. Rem The results are stored in table sys.sys_tzuv2_temptab. If the column Rem is defined on VARRAY with TIMESTAMP WITH TIME ZONE embedded, we do NOT Rem scan the data to find out how many rows are affected but we still Rem report the table and column information, which are stroed in table Rem sys.sys_tzuv2_va_tmptab; Rem Rem Before running this script, make sure that the following tempoary Rem table names do not confict with any existing table objects in Rem your database. Rem Rem sys.sys_tzuv2_temptab Rem sys.sys_tzuv2_temptab1 Rem sys.sys_tzuv2_va_temptab Rem sys.sys_tzuv2_va_temptab1 Rem Rem If they do, change the the above table names in the script to other Rem names. Rem Rem If your database has column data that will be affected by the Rem time zone file update, dump the data before you upgrade to the Rem new version. After the upgrade, you need update the data Rem to make sure the data is stored based on the new rules in the Rem new version of time zone files. Rem Rem For example, user scott has a table tztab: Rem Rem CREATE TABLE tztab( Rem x NUMBER PRIMARY KEY, Rem y TIMESTAMP WITH TIME ZONE); Rem Rem INSERT INTO tztab VALUES(1, TIMESTAMP '...'); Rem Rem Before upgrade, you can create a table tztab_back, note Rem column y here is defined as VARCHAR2 to preserve the original Rem value. Rem Rem CREATE TABLE tztab_back( Rem x NUMBER PRIMARY KEY, Rem y VARCHAR2(256)); Rem Rem INSERT INTO tztab_back Rem SELECt x, to_char(y, 'YYYY-MM-DD HH24.MI.SSXFF TZR') Rem FROM tztab; Rem Rem After upgrade, you need update the data in the table tztab using Rem the value in tztab_back. Rem Rem UPDATE tztab t SET Rem t.y = (SELECT TO_TIMESTAMP_TZ(t1.y, 'YYYY-MM-DD HH24.MI.SSXFF TZR') Rem FROM tztab_back t1 Rem WHERE t.x=t1.x); Rem Rem Once you are done with the time zone files upgrade and patch of Rem TIMESTAMP WITH TIME ZONE data. Please drop the following temporary Rem tables: Rem Rem drop table sys.sys_tzuv2_temptab; Rem drop table sys.sys_tzuv2_temptab1; Rem drop table sys.sys_tzuv2_va_temptab; Rem drop table sys.sys_tzuv2_va_temptab1 Rem Rem NOTES Rem 1. This script needs to be run before upgrading to a new version time Rem zone file. Also, before running this script, make sure that you Rem get the latest version of timezdif.csv file. Rem Rem 2. This script must be run using SQL*PLUS. Rem Rem 3. You must be connected AS SYSDBA to run this script. Rem Rem 4. This script is created only for Oracle 10.1 or higer. A separate Rem script is provided for Oracle 9i. Rem Rem 5. tzuv2ext_*.log and tzuv2ext_*.bad will be created in the directory Rem of $ORACLE_HOME/oracore/zoneinfo when using the external table Rem for $ORACLE_HOME/oracore/zoneinfo/timezdif.csv file to get all the Rem affected time zone names. Rem Rem After running the script, please refer to these two files to see Rem if there are any rows in timezdif.csv, which are not loaded in Rem table sys.sys_tzuv2_affected_regions. If so, it might affect the Rem correct selection of affected TIMESTAMP WITH TIME ZONE tables in Rem the database. You can always delete tzuv2ext*.log & tzuv2ext*.bad. Rem Rem Path separator is obtained by querying v$database for platform_id. Rem If it is windows platform, path separator uses '\'. If it is Unix Rem platform, path separator uses '/'. Rem Rem Rem MODIFIED (MM/DD/YY) Rem huagli 04/09/10 - 9559503: fix possible SQL injection issue Rem huagli 01/07/10 - 7256209: enhancements and code clean up Rem yifeng 03/06/07 - 5923970: added functionalities to detect and Rem count the rows affected by TZ ver changes in Rem typed table and nested tables. Added Rem functionalities to detect tables and columns of Rem affected varrays. Rem huagli 01/24/07 - 5838646:new line marker changed to '\n' when Rem reading from the external table Rem 5844057:HP OpenVMS has a specific logic to Rem provide the UNIX path equivalent to ORACLE_HOME, Rem which is ORACLE_HOME_UNIX. Rem huagli 10/11/06 - code hygiene Rem huagli 07/31/06 - time zone update Rem srsubram 12/27/05 - convert in-list into a join query Rem srsubram 11/06/05 - 4616517:execute count query in parallel Rem srsubram 05/12/05 - 4331865:Modify script to work with prior Rem releases Rem lkumar 05/11/04 - Fix lrg 1691434. Rem rchennoj 12/02/03 - Fix query Rem qyu 11/22/03 - qyu_bug-3236585 Rem qyu 11/17/03 - Created Rem SET SERVEROUTPUT ON DECLARE dbv VARCHAR2(10); dbtzv VARCHAR2(5); numrows NUMBER; TYPE cursor_t IS REF CURSOR; cursor_tstz cursor_t; cursor_nt_tstz cursor_t; tstz_owner VARCHAR2(30); tstz_tname VARCHAR2(30); tstz_qcname VARCHAR2(4000); eqtstz_qcname VARCHAR2(4000); tz_version NUMBER; oracle_home VARCHAR(4000); tz_count INTEGER; tz_numrows INTEGER; plsql_block VARCHAR2(200); file_separator VARCHAR2(3); pfid NUMBER; current_user VARCHAR2(30); insert_stmt VARCHAR2(4000); -- constant for double quote DBLQT CONSTANT VARCHAR2(2) := '"'; -- constants defined for platform ID PLATFORM_WINDOWS32 CONSTANT BINARY_INTEGER := 7; PLATFORM_WINDOWS64 CONSTANT BINARY_INTEGER := 8; PLATFORM_WINDOWS64AMD CONSTANT BINARY_INTEGER := 12; PLATFORM_OPENVMS CONSTANT BINARY_INTEGER := 15; BEGIN --======================================================================== -- First make sure that the check script is running by SYS --======================================================================== EXECUTE IMMEDIATE 'SELECT SYS_CONTEXT(''userenv'', ''current_user'') FROM dual' INTO current_user; IF current_user != 'SYS' THEN DBMS_OUTPUT.PUT_LINE('This check script must be run through user "SYS".'); RETURN; END IF; --======================================================================== -- Make sure that only version 10 or higher uses this script --======================================================================== EXECUTE IMMEDIATE 'SELECT substr(version,1,6) FROM v$instance' INTO dbv; IF dbv = '8.1.7.' THEN DBMS_OUTPUT.PUT_LINE('TIMESTAMP WITH TIME ZONE data type was not ' || 'supported in Release 8.1.7.'); DBMS_OUTPUT.PUT_LINE('No need to validate TIMESTAMP WITH TIME ZONE data.'); RETURN; END IF; IF dbv in ('9.0.1.','9.2.0.') THEN DBMS_OUTPUT.PUT_LINE('There are no time zone version changes ' || 'for Release 9.0.1 or 9.2.0. Customers with ' || 'extended maintenance support can be always ' || 'provided with new time zone version files and ' || 'the updated 9i style script if needed.'); RETURN; END IF; --======================================================================== -- Create temporary tables used by the check script --======================================================================== EXECUTE IMMEDIATE 'SELECT count(*) FROM ALL_ALL_TABLES WHERE owner = ''SYS'' and table_name = ''SYS_TZUV2_TEMPTAB''' INTO tz_count; IF tz_count <> 0 THEN EXECUTE IMMEDIATE 'DROP TABLE sys.sys_tzuv2_temptab'; END IF; EXECUTE IMMEDIATE 'CREATE TABLE sys.sys_tzuv2_temptab ( table_owner VARCHAR2(30), table_name VARCHAR2(30), column_name VARCHAR2(4000), rowcount NUMBER, nested_tab VARCHAR2(3) )'; EXECUTE IMMEDIATE 'SELECT count(*) FROM ALL_ALL_TABLES WHERE owner = ''SYS'' and table_name = ''SYS_TZUV2_TEMPTAB1''' INTO tz_count; IF tz_count <> 0 THEN EXECUTE IMMEDIATE 'DROP TABLE sys.sys_tzuv2_temptab1'; END IF; EXECUTE IMMEDIATE 'CREATE TABLE sys.sys_tzuv2_temptab1 ( time_zone_name VARCHAR2(60) )'; EXECUTE IMMEDIATE 'SELECT count(*) FROM ALL_ALL_TABLES WHERE owner = ''SYS'' and table_name = ''SYS_TZUV2_VA_TEMPTAB''' INTO tz_count; IF tz_count <> 0 THEN EXECUTE IMMEDIATE 'DROP TABLE sys.sys_tzuv2_va_temptab'; END IF; EXECUTE IMMEDIATE 'CREATE TABLE sys.sys_tzuv2_va_temptab( table_owner VARCHAR2(30), table_name VARCHAR2(30), column_name VARCHAR2(4000), nested_tab VARCHAR2(3) )'; EXECUTE IMMEDIATE 'SELECT count(*) FROM ALL_ALL_TABLES WHERE owner = ''SYS'' and table_name = ''SYS_TZUV2_VA_TEMPTAB1''' INTO tz_count; IF tz_count <> 0 THEN EXECUTE IMMEDIATE 'DROP TABLE sys.sys_tzuv2_va_temptab1'; END IF; EXECUTE IMMEDIATE 'CREATE TABLE sys.sys_tzuv2_va_temptab1( va_of_tstz_typ VARCHAR2(30) )'; --======================================================================== -- Get $ORACLE_HOME --======================================================================== EXECUTE IMMEDIATE 'SELECT platform_id FROM v$database' INTO pfid; plsql_block := 'BEGIN SYS.DBMS_SYSTEM.GET_ENV(:1, :2); END;'; IF pfid = PLATFORM_OPENVMS THEN EXECUTE IMMEDIATE plsql_block USING 'ORACLE_HOME_UNIX', OUT oracle_home; ELSE EXECUTE IMMEDIATE plsql_block USING 'ORACLE_HOME', OUT oracle_home; END IF; --======================================================================== -- Use an external table created on timezdif.csv file to get the -- affected time zones. In this way, every time when time zone information -- changes, we only need to provide user with the updated timezdif.csv file -- without changing utltzuv2.sql. -- -- 1. Setup the directory for timezdif.csv and log files(log, bad log) -- 2. Check any existing external table with this name -- sys.sys_tzuv2_affected_regions -- 3. Setup the parameters of the external table --======================================================================== -- get the file separator by looking at the platform ID IF pfid = PLATFORM_WINDOWS32 OR pfid = PLATFORM_WINDOWS64 OR pfid = PLATFORM_WINDOWS64AMD THEN file_separator := '\'; ELSE file_separator := '/'; END IF; EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY timezdif_dir AS ''' || oracle_home || file_separator || 'oracore' || file_separator || 'zoneinfo'''; EXECUTE IMMEDIATE 'SELECT count(*) FROM ALL_ALL_TABLES WHERE owner = ''SYS'' and table_name = ''SYS_TZUV2_AFFECTED_REGIONS''' INTO tz_count; IF tz_count <> 0 THEN EXECUTE IMMEDIATE 'DROP TABLE sys.sys_tzuv2_affected_regions'; END IF; EXECUTE IMMEDIATE 'CREATE TABLE sys.sys_tzuv2_affected_regions ( version NUMBER, time_zone_name VARCHAR2(40), from_year NUMBER, to_year NUMBER ) ORGANIZATION EXTERNAL ( TYPE ORACLE_LOADER DEFAULT DIRECTORY timezdif_dir ACCESS PARAMETERS ( records delimited by ''\n'' skip 3 badfile timezdif_dir:''tzuvext%a_%p.bad'' logfile timezdif_dir:''tzuvext%a_%p.log'' fields terminated by '','' lrtrim missing field values are null ( version, time_zone_name, from_year, to_year ) ) LOCATION (''timezdif.csv'') ) REJECT LIMIT UNLIMITED'; EXECUTE IMMEDIATE 'SELECT count(*) FROM sys.sys_tzuv2_affected_regions' INTO tz_numrows; IF tz_numrows = 0 THEN DBMS_OUTPUT.PUT_LINE('The external table ' || 'sys.sys_tzuv2_affected_regions is not populated ' || 'correctly.'); DBMS_OUTPUT.PUT_LINE('Please contact Oracle Support for this issue.'); RETURN; END IF; --====================================================================== -- Check if the TIMEZONE data is consistent with the latest version. --====================================================================== EXECUTE IMMEDIATE 'SELECT version FROM v$timezone_file' INTO tz_version; EXECUTE IMMEDIATE 'SELECT MAX(version) FROM sys_tzuv2_affected_regions' INTO dbtzv; IF tz_version = dbtzv THEN DBMS_OUTPUT.PUT_LINE('The time zone file used for your current RDBMS ' || 'has the same version (ver ' || dbtzv || ') ' || 'as what this check script checks. No need to ' || 'apply the patch of time zone files and ' || 'TIMESTAMP WITH TIME ZONE data.'); DBMS_OUTPUT.PUT_LINE('If you want to check for another time zone ' || 'version other than version ' || dbtzv || ', ' || 'please install the corresponding timezdif.csv ' || 'file and then run this check script for that ' || 'version.'); RETURN; END IF; --====================================================================== -- Get tables with columns defined as type TIMESTAMP WITH TIME ZONE. --====================================================================== OPEN cursor_tstz FOR 'SELECT atc.owner, atc.table_name, atc.qualified_col_name FROM "ALL_TAB_COLS" atc, "ALL_ALL_TABLES" at WHERE data_type LIKE ''TIMESTAMP%WITH TIME ZONE'' AND atc.owner = at.owner AND atc.table_name = at.table_name'; --====================================================================== -- Query the external table to get all the affected time zones based -- on the current database time zone version, and then put them into -- a temporary table, sys_tzuv2_temptab1. --====================================================================== EXECUTE IMMEDIATE 'INSERT INTO sys.sys_tzuv2_temptab1 SELECT DISTINCT time_zone_name FROM sys.sys_tzuv2_affected_regions t WHERE t.version > ' || tz_version; EXECUTE IMMEDIATE 'ANALYZE TABLE sys.sys_tzuv2_temptab1 ' || 'COMPUTE STATISTICS'; --====================================================================== -- Check regular table columns. --====================================================================== insert_stmt := 'INSERT INTO sys.sys_tzuv2_temptab VALUES(:1,:2,:3,:4,:5)'; LOOP BEGIN FETCH cursor_tstz INTO tstz_owner, tstz_tname, tstz_qcname; EXIT WHEN cursor_tstz%NOTFOUND; -- If the qualified column is not in the format of "X"."Y"."Z", -- double enquote the column. IF INSTR(tstz_qcname, '.') = 0 OR INSTR(tstz_qcname, '"') = 0 THEN eqtstz_qcname := DBLQT || tstz_qcname || DBLQT; ELSE eqtstz_qcname := tstz_qcname; END IF; EXECUTE IMMEDIATE 'SELECT /*+ USE_HASH (r t) */ COUNT(1) FROM ' || DBLQT || tstz_owner || DBLQT || '.' || DBLQT || tstz_tname || DBLQT || ' t, ' || 'sys.sys_tzuv2_temptab1 r ' || 'WHERE UPPER(r.time_zone_name) = ' || 'UPPER(TO_CHAR(t.' || eqtstz_qcname || ', ''TZR'')) ' INTO numrows; IF numrows > 0 THEN EXECUTE IMMEDIATE insert_stmt USING tstz_owner, tstz_tname, tstz_qcname, numrows, 'NO'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('OWNER : ' || tstz_owner); DBMS_OUTPUT.PUT_LINE('TABLE : ' || tstz_tname); DBMS_OUTPUT.PUT_LINE('COLUMN : ' || tstz_qcname); DBMS_OUTPUT.PUT_LINE(SQLERRM); END; END LOOP; --====================================================================== -- Check nested table columns. --====================================================================== OPEN cursor_nt_tstz FOR 'SELECT owner, table_name, qualified_col_name ' || 'FROM "ALL_NESTED_TABLE_COLS" ' || 'WHERE data_type LIKE ''TIMESTAMP%WITH TIME ZONE'' '; LOOP BEGIN FETCH cursor_nt_tstz INTO tstz_owner, tstz_tname, tstz_qcname; EXIT WHEN cursor_nt_tstz%NOTFOUND; -- If the qualified column is not in the format of "X"."Y"."Z", -- double enquote the column. IF INSTR(tstz_qcname, '.') = 0 OR INSTR(tstz_qcname, '"') = 0 THEN eqtstz_qcname := DBLQT || tstz_qcname || DBLQT; ELSE eqtstz_qcname := tstz_qcname; END IF; EXECUTE IMMEDIATE 'SELECT /*+ NESTED_TABLE_GET_REFS USE_HASH(r t) */ COUNT(1) FROM ' || DBLQT || tstz_owner || DBLQT || '.' || DBLQT || tstz_tname || DBLQT || ' t, ' || 'sys.sys_tzuv2_temptab1 r ' || 'WHERE UPPER(r.time_zone_name) = ' || 'UPPER(TO_CHAR(t.' || eqtstz_qcname || ', ''TZR'')) ' INTO numrows; IF numrows > 0 THEN EXECUTE IMMEDIATE insert_stmt USING tstz_owner, tstz_tname, tstz_qcname, numrows, 'YES'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('OWNER : ' || tstz_owner); DBMS_OUTPUT.PUT_LINE('TABLE : ' || tstz_tname); DBMS_OUTPUT.PUT_LINE('COLUMN : ' || tstz_qcname); DBMS_OUTPUT.PUT_LINE(SQLERRM); END; END LOOP; --====================================================================== -- Check varrays containing TSTZ data type. Note that we do not check -- TSTZ data embedded in varrays. We just report the table names and -- column names defined over varray types which contain TSTZ data type. --====================================================================== -- get VARRAY types containing TSTZ data type EXECUTE IMMEDIATE 'INSERT INTO sys.sys_tzuv2_va_temptab1 SELECT DISTINCT o.name FROM ( SELECT p_obj# obj# FROM sys.dependency$ START WITH p_obj# in ( SELECT DISTINCT o.obj# FROM sys.obj$ o, sys.attribute$ a WHERE o.oid$ = a.toid AND a.attr_toid = ''0000000000000000000000000000003E'' UNION ALL SELECT DISTINCT o.obj# FROM sys.obj$ o, sys.collection$ c WHERE o.oid$ = c.toid AND c.elem_toid = ''0000000000000000000000000000003E'') CONNECT BY prior d_obj# = p_obj# AND bitand(prior property, 1) = 1 ORDER SIBLINGS BY d_obj#, p_obj# ) t, sys.obj$ o, sys.coltype$ c WHERE t.obj# = o.obj# AND o.oid$ = c.toid AND bitand(c.flags, 8) = 8'; -- Report the tables and column names defined over varray types which -- contain TSTZ data type. EXECUTE IMMEDIATE 'INSERT INTO sys.sys_tzuv2_va_temptab SELECT atc.owner, atc.table_name, atc.qualified_col_name, ''NO'' FROM "ALL_TAB_COLS" atc, "ALL_ALL_TABLES" at, sys.sys_tzuv2_va_temptab1 WHERE data_type = va_of_tstz_typ AND atc.owner = at.owner AND atc.table_name = at.table_name'; EXECUTE IMMEDIATE 'INSERT INTO sys.sys_tzuv2_va_temptab SELECT owner, table_name, qualified_col_name, ''YES'' FROM "ALL_NESTED_TABLE_COLS", sys.sys_tzuv2_va_temptab1 WHERE data_type = va_of_tstz_typ'; DBMS_OUTPUT.PUT_LINE('Query sys.sys_tzuv2_temptab and ' || 'sys.sys_tzuv2_va_temptab tables to see ' || 'if any TIMESTAMP WITH TIME ZONE data are affected ' || 'when upgrading from the current time zone ' || 'version ' || tz_version || ' to a newer version of ' || dbtzv || '.'); EXCEPTION WHEN OTHERS THEN IF INSTR(SQLERRM, 'KUP-04063') != 0 THEN DBMS_OUTPUT.PUT_LINE('Directory for file timezdif.csv is ' || 'not correctly specified!'); DBMS_OUTPUT.PUT_LINE(sqlerrm); ELSIF INSTR(SQLERRM, 'KUP-04040') != 0 THEN DBMS_OUTPUT.PUT_LINE('File timezdif.csv in TIMEZDIF_DIR not found!'); ELSE DBMS_OUTPUT.PUT_LINE(SQLERRM); END IF; END; / Rem========================================================================= SET SERVEROUTPUT OFF Rem=========================================================================