KUP-04040: file timezdif.csv in TIMEZDIF_DIR not found

刘泽宇

目标：
After upgrading to 11.2.0.1 (or higher) the alert log shows this error every midnight:

DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file ...trace/XXXX_j004_28916.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file timezdif.csv in TIMEZDIF_DIR not found

Other possible errors seen in the alert.log are:


BMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file ..trace\XXXX_j000_5048.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file D:\oracle\ora102\oracore\zoneinfo\tzuvext000_2664_5048.log

( of course the name on the last line will be different, but will contain  "\oracore\zoneinfo\" )
As an example, in the last case, the trace file contains:

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file ...\oracore\zoneinfo\tzuvext000_2664_5048.log

DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"SYS_TZUV2_AFFECTED_REGIONS"','""', ...)
DBMS_STATS: ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file ...\oracore\zoneinfo\tzuvext000_2664_5048.log



处理方法：

The file timezdif.csv and TIMEZDIF_DIR are used in the sys.sys_tzuv2_affected_regions external table.

This table is a left over from (old) DST upgrade scripts like utltzuv2.sql used for the DSTv4 (USA2007) dst update.

The tables in question are in any case only used/needed during the DST upgrade itself, hence they can be deleted afterwards. The Oracle database does not need these tables to exist to function.

These tables are not used anymore in later DST utltzuvxxx.sql scripts and are totally irrelevant for 11gR2 and higher where a new way of updating DST (DBMS_DST) is introduced.

Seeing that the timezdif.csv file is not there anymore , the external table fails to find timezdif.csv and the GATHER_STATS_JOB cannot gather stats on that table and logs this error.

The solution is to:

- Check which unnecessary objects are in the database

conn / as sysdba
select owner,object_name, object_type from dba_objects where owner='SYS' and upper(object_name) like '%TZUV2%';
This will return some tables (which ones depends on what script had been run in the past) like :

SYS.SYS_TZUV2_AFFECTED_REGIONS
SYS.SYS_TZUV2_TEMPTAB
SYS.SYS_TZUV2_TEMPTAB1
SYS.SYS_TZUV2_VA_TEMPTAB
SYS.SYS_TZUV2_VA_TEMPTAB1

- Simply drop those identified tables:

conn / as sysdba
DROP TABLE SYS.SYS_TZUV2_AFFECTED_REGIONS PURGE;
DROP TABLE SYS.SYS_TZUV2_TEMPTAB PURGE;
DROP TABLE SYS.SYS_TZUV2_TEMPTAB1 PURGE;
DROP TABLE SYS.SYS_TZUV2_VA_TEMPTAB PURGE;
DROP TABLE SYS.SYS_TZUV2_VA_TEMPTAB1 PURGE;
and then drop the directory, if this exists:

conn / as sysdba
DROP DIRECTORY timezdif_dir;