9i/10g -> 11g timezone issue

Here's a quick way documented on Metalink ID 396670.1. They take no responsibility of the code and neither do I!

Please make sure you review the code and use it at your own risk.

There are 3 scripts:
1. prepare_zuv9.sql
2. restore_zuv9.sql
3. clean_zuv9.sql

You can use Script 1 to prepare look for all tables that might have potential issue with upgrading to 11g. (Note: if the tables are huge, you might want to use merge or come up with other solutions to make sure your outage window remains small)

Once you upgrade the second script is used to restore the data back to the tables and the 3rd script to clean up all these temp tables.




Here are the scripts.


prepare_zuv9.sql

set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'CREATE TABLE '|| r1.table_owner || '.' || 'BACKUP_';
stmt := stmt || r1.object_id || '_' || r1.column_id || ' (ORIG_ROWID ';
stmt := stmt || 'ROWID PRIMARY KEY, SAVED_VALUE VARCHAR2(256))';
execute immediate stmt;
ln := 'Backup table ' || r1.table_owner || '.' || 'BACKUP_';
ln := ln || r1.object_id || '_' || r1.column_id || ' for ' || r1.table_owner;
ln := ln || '.' || r1.table_name || '(' || r1.column_name || ') created';
dbms_output.put (ln);
stmt := 'INSERT INTO ' || r1.table_owner || '.' || 'BACKUP_' || r1.object_id;
stmt := stmt || '_' || r1.column_id || ' SELECT ROWID, TO_CHAR(' ;
stmt := stmt || r1.column_name || ', ''YYYY-MM-DD HH24:MI:SSXFF TZR'') ';
stmt := stmt || 'FROM ' || r1.table_owner || '.' || r1.table_name||' WHERE ';
stmt := stmt || 'UPPER(TO_CHAR(' || r1.column_name || ',''TZR'')) ';
stmt := stmt || 'IN (SELECT UPPER(TIME_ZONE_NAME) FROM ';
stmt := stmt || 'SYS.SYS_TZUV2_AFFECTED_REGIONS)';
execute immediate stmt;
dbms_output.put_line (', ' || SQL%ROWCOUNT || ' row(s) inserted.');
end loop;
end;
/



restore_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'UPDATE ' || r1.table_owner || '.' || r1.table_name || ' T ';
stmt := stmt || 'SET T.' || r1.column_name||'=(SELECT TO_TIMESTAMP_TZ';
stmt := stmt || '(T1.SAVED_VALUE, ''YYYY-MM-DD HH24:MI:SSXFF TZR'') FROM ';
stmt := stmt || r1.table_owner || '.BACKUP_' || r1.object_id || '_';
stmt := stmt || r1.column_id || ' T1 WHERE T.ROWID=T1.ORIG_ROWID) ';
stmt := stmt || 'WHERE EXISTS (SELECT ORIG_ROWID FROM ' || r1.table_owner ;
stmt := stmt || '.BACKUP_' || r1.object_id || '_'|| r1.column_id;
stmt := stmt || ' T1 WHERE T.ROWID=T1.ORIG_ROWID)';
execute immediate stmt;
ln := SQL%ROWCOUNT || ' row(s) in column ' || r1.table_owner || '.';
ln := ln || r1.table_name || '(' || r1.column_name || ') updated.';
dbms_output.put_line (ln);
end loop;
end;
/


clean_zuv9.sql
set serveroutput on
declare
stmt varchar2(1000);
ln varchar2(1000);
cursor c1 is select z.table_owner,
z.table_name,
z.column_name,
c.column_id,
o.object_id
from sys.sys_tzuv2_temptab z,
dba_tab_cols c,
dba_objects o
where o.object_name = z.table_name
and o.object_type = 'TABLE'
and o.owner = z.table_owner
and z.table_owner = c.owner
and z.table_name = c.table_name
and z.column_name= c.column_name;
begin
for r1 in c1 loop
stmt := 'DROP TABLE ' || r1.table_owner || '.BACKUP_';
stmt := stmt || r1.object_id || '_' || r1.column_id;
execute immediate stmt;
ln := 'Backup table ' || r1.table_owner || '.BACKUP_';
ln := ln || r1.object_id || '_' || r1.column_id || ' dropped.';
dbms_output.put_line (ln);
end loop;
execute immediate 'drop table sys.sys_tzuv2_temptab';
dbms_output.put_line ('Table SYS.SYS_TZUV2_TEMPTAB dropped.');
execute immediate 'drop table sys.sys_tzuv2_affected_regions';
dbms_output.put_line ('Table SYS.SYS_TZUV2_AFFECTED_REGIONS dropped.');
end;
/



Here's how the RESTORE output would look like:


SQL> @restore_zuv9.sql
415 row(s) in column
QA4.VENDORPREFERENCN_FV(SET_LAST_UPDATE_DATE) updated.
415 row(s) in column
QA4.VENDORPREFERENCES_FV(SET_CREATION_DATE) updated.
518 row(s) in column
QA4.VENDORPREFER_FV(PREFERENCE_START_DATE) updated.
518 row(s) in column
QA4.VENDORPREFERE_FV(PREFERENCE_END_DATE) updated.
518 row(s) in column QA4.VENDOCEATTRBEAN_FV(LAST_UPDATE_DATE)
updated.
18 row(s) in column QA4.TIMER_SERVICE_JOURNAL(CREATION_TIME)
updated.
834 row(s) in column QA4.FR_ALL_NLSTG(SERVICE_START_TIME)
updated.
834 row(s) in column QA4.FR_ALL_VERTICALS_NLSTG(SERVICE_END_TIME)
updated.
1492 row(s) in column QA4.FR_ALL_DATA(SERVICE_START_TIME)
updated.
1492 row(s) in column QA4.FR_ALL_DATA(SERVICE_END_TIME)
updated.
49 row(s) in column QA4.COST_DETAIL(START_DATE) updated.
49 row(s) in column QA4.COST_DETAIL(END_DATE) updated.

PL/SQL procedure successfully completed.

SQL>