Edit D:\app\Administrator\product\11.2.0\dbhome

Edit D:\app\Administrator\product\11.2.0\dbhome_1\RDBMS\ADMIN\utltzuv2.sql

Rem
Rem $Header: utltzuv2.sql 06-mar-2007.11:22:44 yifeng Exp $
Rem
Rem utltzuv2.sql
Rem
Rem Copyright (c) 2003, 2007, Oracle. 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 
Rem      are 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 file 
Rem      timezdif.csv, which contains all the affected time zone names in each 
Rem      version. Please make sure that you have the latest version of the 
Rem      timezdif.csv (the one corresponding to the latest timezone data file), 
Rem      before you run the 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
Rem      in the regular table, the script also finds out how many
Rem      rows might be affected by checking whether the column data
Rem      contain the values for the affected time zone names.
Rem      If the column is in the nested table's storage table, we
Rem      don't scan the data to find out how many rows are affected but
Rem      we still report the table and column info.
Rem      
Rem      The result is stored in the table sys.sys_tzuv2_temptab.
Rem      Before running the script, make sure the table name doesn't
Rem      conflict with any existing table object. It it does,
Rem      change the table name sys.sys_tzuv2_temptab to some other name
Rem      in the script. You can query the table to view the result:
Rem         select * from sys.sys_tzuv2_temptab;   
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.
Rem      
Rem      For example, user scott has a table tztab:
Rem      create table tztab(x number primary key, y timestamp with time zone);
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      create table tztab_back(x number primary key, y varchar2(256));
Rem      insert into tztab_back select x, 
Rem                  to_char(y, 'YYYY-MM-DD HH24.MI.SSXFF TZR') from tztab;
Rem
Rem      After upgrade, you need update the data in the table tztab using
Rem      the value in tztab_back.
Rem      update tztab t set t.y = (select to_timestamp_tz(t1.y, 
Rem        'YYYY-MM-DD HH24.MI.SSXFF TZR') from tztab_back t1 where t.x=t1.x); 
Rem
Rem      Or you can use export utility to export your data before the upgrade
Rem      and them import your data again after the upgrade. 
Rem     
Rem      drop table sys.sys_tzuv2_temptab;
Rem      once you are done with the time zone file upgrade.
Rem    
Rem    NOTES
Rem      * This script needs to be run before upgrading to a new version time 
Rem        zone file. Also, before running this script, please make sure that
Rem        you get the latest version of timezdif.csv file.
Rem      * This script must be run using SQL*PLUS.
Rem      * You must be connected AS SYSDBA to run this script.
Rem      * This script is created only for Oracle 10.1 or higer. A separate 
Rem        script is provided for Oracle 9i.
Rem      * Two files, tzuv2ext_*.log and tzuv2ext_*.bad will be created in
Rem        the directory of $ORACLE_HOME/oracore/zoneinfo when using the
Rem        external table for timezdif.csv file to get the affected time zones. 
Rem        After running the script, please refer to these two files to see if 
Rem        there are any rows in timezdif.csv, which are not loaded. If so,
Rem        it might affect the correct selection of affected tables in the database.
Rem        You can always delete tzuv2ext_*.log and tzuv2ext_*.bad.
Rem      * A Java stored procedure is created to get the system file separator,
Rem        e.g., Windows uses '\' and Unix uses '/'. Before you run this script,
Rem        make sure that the Java source/class object does not conflict with
Rem        any existing va source/class object in the database. If it does, please
Rem        change the Java source/class object name GetFileSeparator to some other
Rem        name in the script. Also make sure that the Java stored procedure does not 
Rem        conflict with any existing function in the database. If it does, please 
Rem        change the function name GET_FILE_SEPARATOR to some other name in the script.
Rem        If renaming is needed, please make sure that you change all the occurences
Rem        to make them consistent.
Rem        
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    yifeng      03/06/07 - 5923970: added functionalities to detect and count the rows 
Rem                           affected by TSTZ changes in typed table and nested tables. 
Rem                           Added functionalities to detect tables and columns of 
Rem                           affected varrays.
Rem    huagli      01/24/07 - 5838646:new line marker changed to '\n' when reading
Rem                           from the external table
Rem                           5844057:HP OpenVMS has a specific logic to provide 
Rem                           the UNIX path equivalent to ORACLE_HOME, which is 
Rem                           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

Rem=========================================================================
Rem Check any existing table with this name sys.sys_tzuv2_temptab
Rem=========================================================================  
DROP TABLE sys.sys_tzuv2_temptab
/
CREATE TABLE sys.sys_tzuv2_temptab
(
 table_owner  VARCHAR2(30),
 table_name   VARCHAR2(30),
 column_name  VARCHAR2(30),
 rowcount     NUMBER,
 nested_tab   VARCHAR2(3)
)
/

Rem========================================================================
Rem Check any existing table with this name sys.sys_tzuv2_temptab1
Rem========================================================================
DROP TABLE sys.sys_tzuv2_temptab1
/
CREATE TABLE sys.sys_tzuv2_temptab1
(
 time_zone_name VARCHAR2(60)
)
/

Rem========================================================================
Rem Check any existing table with this name sys.sys_tzuv2_va_temptab1 
Rem========================================================================
DROP TABLE sys.sys_tzuv2_va_temptab1
/
CREATE TABLE sys.sys_tzuv2_va_temptab1
(
 dependent NUMBER,
 parent NUMBER
)
/

Rem========================================================================
Rem Check any existing table with this name sys.sys_tzuv2_va_temptab
Rem========================================================================
DROP TABLE sys.sys_tzuv2_va_temptab
/
CREATE TABLE sys.sys_tzuv2_va_temptab
(
 table_owner  VARCHAR2(30),
 table_name   VARCHAR2(30),
 column_name  VARCHAR2(30),
 nested_tab   VARCHAR2(3)
)
/

DECLARE

dbv             VARCHAR2(10);
  dbtzv           VARCHAR2(5);
  numrows         NUMBER;
  TYPE cursor_t   IS REF CURSOR;
  cursor_tstz     cursor_t;
  cursor_nt_tstz  cursor_t;
  cursor_va1_tstz cursor_t;
  cursor_va2_tstz cursor_t;
  tstz_owner      VARCHAR2(30);
  tstz_tname      VARCHAR2(30);
  tstz_qcname     VARCHAR2(4000);
  tz_version      NUMBER;
  oracle_home     VARCHAR(4000);
  tz_count        INTEGER;
  tz_numrows      INTEGER;
  plsql_block     VARCHAR2(200);
  file_separator  VARCHAR2(3);
  platform        VARCHAR2(120);
  start_objname   VARCHAR2(30);
  start_objnum    NUMBER;    
  va_name         VARCHAR2(30);

BEGIN

--========================================================================
  -- Make sure that only version 10 or higher uses this script
  --========================================================================
        
  SELECT substr(version,1,6) INTO dbv FROM v$instance;
        
  IF dbv = '8.1.7.'
  THEN
    DBMS_OUTPUT.PUT_LINE('TIMEZONE data type was not supported in ' ||
                         'Release 8.1.7.');
    DBMS_OUTPUT.PUT_LINE('No need to validate TIMEZONE data.');
    RETURN;
  END IF;
        
  IF dbv in ('9.0.1.','9.2.0.')
  THEN
   DBMS_OUTPUT.PUT_LINE('Please contact Oracle support to get the script ' || 
                        'for Release 9.0.1 or 9.2.0.');
   RETURN;
  END IF;
 
  --========================================================================
  -- Get $ORACLE_HOME
  --========================================================================
  
  EXECUTE IMMEDIATE 'SELECT platform_name FROM v$database'
  INTO platform;      
        
  plsql_block := 'BEGIN SYS.DBMS_SYSTEM.GET_ENV(:1, :2); END;';

IF platform = 'HP Open VMS'
  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
  --========================================================================

--========================================================================
  -- Create a Java stored procedure to get the file separator
  --========================================================================
  
  EXECUTE IMMEDIATE 'CREATE OR REPLACE AND COMPILE JAVA SOURCE
                     NAMED "GetFileSeparator" AS 
                         public class GetFileSeparator {             
                           public static String get() {
                             return System.getProperty("file.separator");
                           }
                         }';
                   
  EXECUTE IMMEDIATE 'CREATE OR REPLACE FUNCTION GET_FILE_SEPARATOR 
                     RETURN VARCHAR2 
                     AS LANGUAGE JAVA
                     NAME ''GetFileSeparator.get() return java.lang.String'';';

plsql_block := 'BEGIN :1 :=  GET_FILE_SEPARATOR(); END;';
  EXECUTE IMMEDIATE plsql_block USING OUT file_separator;
   
  EXECUTE IMMEDIATE 'CREATE OR REPLACE DIRECTORY timezdif_dir AS ''' ||
                    oracle_home || file_separator || 'oracore' ||
                                   file_separator || 'zoneinfo''';

EXECUTE IMMEDIATE
    'SELECT count(*) 
     FROM 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''
                          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('TIMEZONE data is consistent with the latest version ' || 
                          dbtzv || ' transition rules');
     DBMS_OUTPUT.PUT_LINE('No need to validate TIMEZONE data');
     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_TABLES" at ' ||
     'WHERE  data_type LIKE ''TIMESTAMP%WITH TIME ZONE''' ||
     ' AND atc.owner = at.owner AND atc.table_name = at.table_name) ' ||
     'UNION ALL ' ||
     '(SELECT atc.owner, atc.table_name, atc.qualified_col_name ' ||
     'FROM   "ALL_TAB_COLS" atc, "ALL_OBJECT_TABLES" aot ' ||
     'WHERE  data_type LIKE ''TIMESTAMP%WITH TIME ZONE''' ||
     ' AND atc.owner = aot.owner AND atc.table_name = aot.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.
  --======================================================================  
  LOOP
       BEGIN
         FETCH cursor_tstz INTO tstz_owner, tstz_tname, tstz_qcname;
         EXIT WHEN cursor_tstz%NOTFOUND;

EXECUTE IMMEDIATE 
           'SELECT COUNT(1) FROM ' ||
            tstz_owner || '."' || tstz_tname || '" t_alias, ' ||
            ' sys.sys_tzuv2_temptab1 r ' ||
            ' WHERE UPPER(r.time_zone_name) = ' ||
               ' UPPER(TO_CHAR(t_alias.' || tstz_qcname || ', ''TZR'')) ' INTO numrows;
        
         IF numrows > 0 THEN
           EXECUTE IMMEDIATE ' INSERT INTO sys.sys_tzuv2_temptab VALUES (''' ||
             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;

EXECUTE IMMEDIATE 
           'SELECT /*+ NESTED_TABLE_GET_REFS */ COUNT(1) FROM ' ||
            tstz_owner || '."' || tstz_tname || '" t_alias, ' ||
            ' sys.sys_tzuv2_temptab1 r ' ||
            ' WHERE UPPER(r.time_zone_name) = ' ||
               ' UPPER(TO_CHAR(t_alias.' || tstz_qcname || ', ''TZR'')) ' INTO numrows;
        
         IF numrows > 0 THEN
           EXECUTE IMMEDIATE ' INSERT INTO sys.sys_tzuv2_temptab VALUES (''' ||
             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.
  --======================================================================

-- This cursor contains all first level dependents on TSTZ type
  OPEN cursor_va1_tstz FOR
     '(SELECT DISTINCT o.name, o.obj# FROM obj$ o, attribute$ a ' ||
     'WHERE o.oid$ = a.toid AND a.attr_toid = ''0000000000000000000000000000003E'')' ||
     'UNION ALL ' || 
     '(SELECT DISTINCT o.name, o.obj# from obj$ o, collection$ c ' ||
     'WHERE o.oid$ = c.toid AND c.elem_toid = ''0000000000000000000000000000003E'')';
  
  -- Table sys.sys_tzuv2_va_temptab1 has all the dependencies on the previous cursor
  LOOP
       BEGIN
         FETCH cursor_va1_tstz INTO start_objname, start_objnum;
         EXIT WHEN cursor_va1_tstz%NOTFOUND;

EXECUTE IMMEDIATE 
           'INSERT INTO sys.sys_tzuv2_va_temptab1 ' ||
           'SELECT d_obj#, p_obj# FROM dependency$ ' || 
           'START WITH p_obj# = ' ||  start_objnum || 
           ' CONNECT BY prior d_obj# = p_obj# ' ||
           'AND bitand(prior property, 1) = 1 ' ||
           'ORDER SIBLINGS BY d_obj#, p_obj# ';

END;
  END LOOP;

-- This cursor has all the varray type names that contains TSTZ in some level
  OPEN cursor_va2_tstz FOR
     'SELECT DISTINCT o.name FROM sys.sys_tzuv2_va_temptab1 t, obj$ o, coltype$ c ' ||
     'WHERE t.parent = o.obj# AND o.oid$ = c.toid AND bitand(c.flags, 8) = 8';

-- Table sys.sys_tzuv2_va_temptab contains all the information about affected tables
  -- and columns (and tell whether the tables are nested tables or not)
  LOOP
       BEGIN
         FETCH cursor_va2_tstz INTO va_name;
         EXIT WHEN cursor_va2_tstz%NOTFOUND;

EXECUTE IMMEDIATE 
           'INSERT INTO sys.sys_tzuv2_va_temptab ' ||
           'SELECT owner, table_name, qualified_col_name, ''NO'' ' ||
           'FROM "ALL_TAB_COLS" ' ||
           'WHERE data_type LIKE ''' || va_name || '''';

EXECUTE IMMEDIATE 
           'INSERT INTO sys.sys_tzuv2_va_temptab ' ||
           'SELECT owner, table_name, qualified_col_name, ''YES'' ' ||
           'FROM "ALL_NESTED_TABLE_COLS" ' ||
           'WHERE data_type LIKE ''' || va_name || '''';   
       END;
  END LOOP;

DBMS_OUTPUT.PUT_LINE('Query sys.sys_tzuv2_temptab and ' ||
                       'sys.sys_tzuv2_va_temptab tables to see ' ||
                       'if any TIMEZONE data is affected by version ' || dbtzv ||
                       ' transition rules');

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;
/

COMMIT
/

DECLARE
  tz_count NUMBER;

BEGIN

--========================================================================
  -- After obtaining the file separator, drop the Java stored procedure
  --========================================================================

EXECUTE IMMEDIATE
    'SELECT count(*) 
     FROM user_objects
     WHERE object_name = ''GetFileSeparator'' and UPPER(object_type) like ''JAVA%'''
  INTO tz_count;
         
  IF tz_count > 0
  THEN
    EXECUTE IMMEDIATE 'DROP JAVA SOURCE "GetFileSeparator"';
  END IF;
  
  EXECUTE IMMEDIATE
    'SELECT count(*) 
     FROM user_objects
     WHERE object_name = ''GET_FILE_SEPARATOR'' and UPPER(object_type) = ''FUNCTION''' 
  INTO tz_count;
  
  IF tz_count > 0
  THEN
    EXECUTE IMMEDIATE 'DROP FUNCTION GET_FILE_SEPARATOR';
  END IF;

END;
/

COMMIT
/

Rem=========================================================================
SET SERVEROUTPUT OFF
Rem=========================================================================

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de