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

"KUP-04040: file timezdif.csv in TIMEZDIF_DIR not found " logged by GATHER_STATS_JOB in alert.log after upgrade [ID 1290722.1]

References

Applies to:
Oracle Server - Standard Edition - Version: 10.2.0.5 to 11.2.0.2 - Release: 10.2 to 11.2
Information in this document applies to any platform.
This is mainly seen after upgrading to 11gR2 , but while less common this might also be seen after upgrading to other versions like 10.2.0.5 or 11.1.0.7.
The solution stay’s the same.
Goal

After upgrade from 10g to 11.2.0.1 (or higher) the alert log has this error every midnight:
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file

/opt/vendor/oracle/diag/rdbms/n_ckqa/N_CKQA/trace/N_CKQA_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 seen errors in the alert.log are:

BMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file

D:\ORACLE\diag\rdbms\dev06\dev06\trace\dev06_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

( the name in the last line will be different of course but contain “\oracore\zoneinfo” )
In the last case the trace file contains then for example:
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

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 D:\oracle\ora102\oracore\zoneinfo\tzuvext000_2664_5048.log

The file timezdif.csv and TIMEZDIF_DIR is used in the sys.sys_tzuv2_affected_regions external table.
This table is a left over from (old) DST upgrade scripts like the 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 any more in later DST utltzuvxxx.sql scripts and is totally irrelevant for 11gR2 , there a new way of updating DST (DBMS_DST) is introduced.

Seen the timezdif.csv file is not there any more the external table fails to find timezdif.csv and GATHER_STATS_JOB cannot gather stats on that table and logs this error.

The solution is to:

check which unneeded objects are in the database

select owner,object_name, object_type from dba_objects where owner=‘SYS’ and upper(object_name) like ‘%TZUV2%’;

This will give some tables (which ones depends on what script was runned in the past) like :
sys.sys_tzuv2_affected_regions
sys.sys_tzuv2_temptab
sys.sys_tzuv2_temptab1
sys.sys_tzuv2_va_temptab

simply drop those tables (there might be more than the 2 listed below)

DROP TABLE sys.sys_tzuv2_temptab;
DROP TABLE sys.sys_tzuv2_affected_regions;

and then drop the directory:

DROP DIRECTORY timezdif_dir;