目录

• 一：问题
• 二、查询官方文档
• 三、解决方案
• 1、第一种：临时数据泵外部表
• 2、第二种：其他外部表

一：问题

查看日志发现如下报错

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /xxx/xxx/rac3_j000_9490.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.

• 查询rac3_j000_9490.trc:文件

Trace file /XXX/XXX/RAC3_j000_9490.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/xxx/11.2

Instance name: rac3
Redo thread mounted by this instance: 3
Oracle process number: 0
Unix process pid: 9490, image: oracle@rac003 (J000)


*** 2023-08-10 21:59:50.802
Ioctl ASYNC_CONFIG error, errno = 1
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout

*** 2023-08-10 22:00:12.111
*** SESSION ID:(1896.51673) 2023-08-10 22:00:12.111
*** CLIENT ID:() 2023-08-10 22:00:12.111
*** SERVICE NAME:(SYS$USERS) 2023-08-10 22:00:12.111
*** MODULE NAME:(DBMS_SCHEDULER) 2023-08-10 22:00:12.111
*** ACTION NAME:(ORA$AT_OS_OPT_SY_50991) 2023-08-10 22:00:12.111
 
KUP-11024: This external table can only be accessed from within a Data Pump job.

*** 2023-08-10 22:00:12.131
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYSTEM"','"ET$07EC01BD0003"','""', ...)
DBMS_STATS: 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.

*** 2023-08-10 22:00:12.181
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"ab"','"ET$010A01040001"','""', ...)
DBMS_STATS: 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.

*** 2023-08-10 22:00:12.234
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"abc"','"ET$043478210001"','""', ...)
DBMS_STATS: 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.

*** 2023-08-10 22:00:12.261
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"ET$060414770003"','""', ...)
DBMS_STATS: 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.
rac003:/xxx/trace$

二、查询官方文档

地址
原因：
此问题的主要原因是“外部表”的操作系统文件在某个时间点存在，但现在不存在。然而，数据库仍然相信该表的OS文件存在，因为关于该对象的字典信息没有被修改以反映该变化。当对有问题的表运行DBMS_STATS时，它会调用外部表，但由于对象不在，调用失败。

• 外部表可能不存在的原因有很多，包括：

1. 临时Datapump外部表未正确清理。字典信息应该在DataPump作业完成时删除。
2. 外部表的操作系统文件已被删除，但没有清除相应的数据字典信息。例如：Oracle演示模式表（如外部表“SALES_TRANACTIONS_EXT”）可能已被删除，但字典尚未更新以反映这一点。“SALES_TRANACTIONS_EXT”表是“SH”模式中的一个外部表，该模式是Oracle提供的Demo模式之一。

三、解决方案

从本质上讲，这个问题的解决方案是清理孤立的字典条目。根据主要原因的确切性质，不同的解决方案是合适的，概述如下：

1、第一种：临时数据泵外部表

• 查询

spool obj.out
set linesize 200 trimspool on
set pagesize 2000
col owner form a30
col created form a25
col last_ddl_time form a25
col object_name form a30
col object_type form a25

select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
to_char(CREATED,'dd-mon-yyyy hh24:mi:ss') created
,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
from dba_objects
where object_name like 'ET$%'
/

select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables
order by 1,2
/

spool off

• 删除

drop table system.ET$07EC01BD000 purge;

2、第二种：其他外部表

• 查询目录

undefine owner
undefine table_pattern

select el.table_name, el.owner, dir.directory_path||'/'||dir.directory_name "path"
from dba_external_locations el
, dba_directories dir
where el.table_name like '%&&table_pattern%'
and el.owner like '%&&owner%'
and el.directory_owner = dir.owner
and el.directory_name = dir.directory_name
order by 1, 2;

文件可能仍然存在，但它们刚刚被重命名或重新定位。如果是这种情况，您可以更正位置以避免出现问题。如果文件已被删除，请执行以下任一步骤：

• 使用以下命令锁定这些表上的统计信息

DBMS_STATS.LOCK_TABLE_STATS ('ownname','tabname');

（此步骤可防止DBMS_STATS针对丢失的表进行收集）
或者执行一下命令

• 删除外部表的字典对象。(这个是可选择的，不一定执行，慎重)

DROP TABLE SALES_TRANSACTIONS_EXT;