ORA-20011: Approximate NDV failed

DataGuard环境下11.2.0.2升级至11.2.0.4警告日志中出现如下:

Setting Resource Manager plan SCHEDULER[0x310D]:DEFAULT_MAINTENANCE_PLAN via scheduler window

Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter

Sun Feb 15 06:00:00 2015

Starting background process VKRM

Sun Feb 15 06:00:00 2015

VKRM started with pid=21, OS id=17692

Sun Feb 15 06:00:03 2015

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.

Errors in file /u01/oracle/diag/rdbms/xitest01/xitest0/trace/xitest0_j002_17712.trc:

ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump job.

TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY: ADDED INTERVAL PARTITION SYS_P65 (3) VALUES LESS THAN (TO_DATE(' 2015-02-15 02:07:51', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

TABLE SYS.WRI$_OPTSTAT_HISTGRM_HISTORY: ADDED INTERVAL PARTITION SYS_P68 (3) VALUES LESS THAN (TO_DATE(' 2015-02-15 02:07:59', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))

Sun Feb 15 06:00:04 2015

Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"

Sun Feb 15 06:00:31 2015

End automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"

 

 

select owner,table_name,type_name,default_directory_name,access_parameters from dba_external_tables;

exec DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','ET$01F000030001')

select DIRECTORY_NAME, DIRECTORY_PATH from dba_directories;

因为我将/u03下面 bogus.dat文件删除了系统不能正常开启ET$01F00030001外部表，现在将IMP_PATH目录删除并重新建立

SQL> drop directory IMP_PATH

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','ET$01F000030001')

BEGIN DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','ET$01F000030001'); END;

*

ERROR at line 1:

ORA-20011: Approximate NDV failed: ORA-06564: object IMP_PATH does not exist

ORA-06512: at "SYS.DBMS_STATS", line 24281

ORA-06512: at "SYS.DBMS_STATS", line 24332

ORA-06512: at line 1

 

SQL> create directory imp_path as '/u03/imp_path';

 

Directory created.

同样报错

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','ET$01F000030001')

BEGIN DBMS_STATS.GATHER_TABLE_STATS('SYSTEM','ET$01F000030001'); END;

 

*

ERROR at line 1:

ORA-20011: Approximate NDV failed: ORA-29913: error in executing

ODCIEXTTABLEOPEN callout

KUP-11024: This external table can only be accessed from within a Data Pump

job.

ORA-06512: at "SYS.DBMS_STATS", line 24281

ORA-06512: at "SYS.DBMS_STATS", line 24332

ORA-06512: at line 1

 

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SYS','WR$_REPLAY_UC_GRAPH_EXT')

BEGIN DBMS_STATS.GATHER_TABLE_STATS('SYS','WR$_REPLAY_UC_GRAPH_EXT'); END;

 

*

ERROR at line 1:

ORA-20000: Unable to analyze TABLE "SYS"."WR$_REPLAY_UC_GRAPH_EXT",

insufficient privileges or does not exist

ORA-06512: at "SYS.DBMS_STATS", line 24270

ORA-06512: at "SYS.DBMS_STATS", line 24332

ORA-06512: at line 1

SQL> GRANT ANALYZE ANY  TO SYS;

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SYS','WRR$_REPLAY_UC_GRAPH_EXT')

BEGIN DBMS_STATS.GATHER_TABLE_STATS('SYS','WRR$_REPLAY_UC_GRAPH_EXT'); END;

 

*

ERROR at line 1:

ORA-20011: Approximate NDV failed: ORA-06564: object WRR_TMP1371999955 does not

exist

ORA-06512: at "SYS.DBMS_STATS", line 24281

ORA-06512: at "SYS.DBMS_STATS", line 24332

ORA-06512: at line 1

 

测试分析ALERT表

SQL> select count(*) from ALERT;

 

  COUNT(*)

----------

   1735763

 

SQL> exec DBMS_STATS.GATHER_TABLE_STATS('SYS','ALERT');

 

PL/SQL procedure successfully completed.

 

现在将其他外部表删除

SQL> drop TABLE SYSTEM.ET$01F000030001;

 

Table dropped.

SQL> drop table SYS.WRR$_REPLAY_UC_GRAPH_EXT;

 

Table dropped.

 

 
Oracle升级一定要注意数据库是否有外部表不然升级后有可能导致数据库无法登录(ORA-20011: Approximate NDV failed)

  select owner,table_name,type_name,default_directory_name,access_parameters from dba_external_tables;

其他参考

http://blog.csdn.net/leshami/article/details/6078481