DBMS_STATS: GATHER STATS JOB encountered errors

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.
***Checked for relevance on 05-Nov-2012*** 

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 remains the same.

GOAL

After upgrading from 10g to 11.2.0.1 (or higher) the alert log shows this error every midnight:

Other possible errors seen 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

FIX

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

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

- Simply drop those identified tables (there may 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;

Reference  MOS Doc 1290722.1