After much research I have already applied the patches to most of our environments. What we did was:
1. apply the patch to dump the utltzuv2 script into the oracle home2. run utltzuv2 to see if there is any affected data. For all the databases so far there has been no affected data. 3. we also asked our developers to ask their vendors if their application was affected by the dst changes. All our vendors said that their apps were not affected by the dst changes so I assume that if the JVM is installed they are not affected.
>>>> the net effect of the above is that none of the user data in our databases is effected and therefore there is no need to convert data. For the 9i databases there is no need to apply any more patches since the 9i dictionary isn't affected.
4. for the 10g db's I ran the second patch to update the tz files. There are 2 files which get updated in oh/oracore/zoneinfo: timezone.dat and timezlrg.dat. I updated these files because it's possible that some of the dictionary fields might use the affected data types, in particular the dbms scheduler fields.
5. I forget which note I read this in but as far as I can tell the clients only have to be upgraded if they will be reading affected fields. Since none of our data is affected we will not need to upgrade the users' clients. We may upgrade the developer and DBA clients but this is minimal compared to the user community.
There is a sql which can be run (from the server) to verify that the patch has been applied correctly:
select to_timestamp_tz('20070311 00:00:00 US/EASTERN','YYYYMMDD HH24:MI:SS TZR') + to_dsinterval('0 08:00:00') TEST from dual;
result should be: TEST---------------------------------------------------------------------------
11-MAR-07 09.00.00.000000000 AM US/EASTERN Joe Allen, Brandon wrote:
Okay, thanks for setting me straight. After a few hours navigating all the notes, I think I have it figured out now and there are a few key things I'd like to clear up in case anyone else is as confused as I was:1) My count of 159 columns yesterday was wrong. I got that by queryingdba_tab_columns, but I forgot that view also contains VIEW columns. Today, I ran the query from Metalink 402614.1, and it showed that I really have only 56 TSTZ columns in actual tables.
-- //www.freelists.org/webpage/oracle-l
