ORA-20011, KUP-11024 外部表引发报错

”从容冷静，从表面着手，深入探究“

一、2020年度第三次次巡检中，某企业Oracle数据库告警

Mon Oct 12 22:00:16 2020
DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /u01/app/oracle/diag/rdbms/odsdb/odsdb2/trace/odsdb2_j001_20570.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.

二、版本信息如下：

操作系统内核：CentOS release 6.5 (Final) / 2.6.32-431.el6.x86_64
数据库版本：oracle 11.2.0.4

三、问题描述：

查看alert日志，发现以上告警。

字面大致意思是：GATHER_STATS_JOB收集统计信息时发生错误。
错误发生在执行调出ODCIEXTTABLEOPEN时，外部表只能通过数据泵调用

四、再次查看告警日志提示的trc跟踪文件odsdb2_j001_20570.trc

Trace file /u01/app/oracle/diag/rdbms/odsdb/odsdb2/trace/odsdb2_j001_20570.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.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/11204
System name:    Linux
Node name:      pro-bi-2
Release:        2.6.32-431.el6.x86_64
Version:        #1 SMP Fri Nov 22 03:15:09 UTC 2013
Machine:        x86_64
Instance name: odsdb2
Redo thread mounted by this instance: 2
Oracle process number: 181
Unix process pid: 20570, image: oracle@pro-bi-2 (J001)


*** 2020-10-12 22:00:16.709
*** SESSION ID:(3989.32255) 2020-10-12 22:00:16.709
*** CLIENT ID:() 2020-10-12 22:00:16.709
*** SERVICE NAME:(SYS$USERS) 2020-10-12 22:00:16.709
*** MODULE NAME:(DBMS_SCHEDULER) 2020-10-12 22:00:16.709
*** ACTION NAME:(ORA$AT_OS_OPT_SY_12446) 2020-10-12 22:00:16.709

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.

*** 2020-10-12 22:00:16.709
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"ET$0B7721930001"','""', ...)
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.

*** 2020-10-12 22:00:16.725
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"ET$0C2E359B0001"','""', ...)
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.

五、处理方法：

1、查询两个对象相关信息

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  
SQL> 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$%'; 
 
OWNER
------------------------------------------------------------
OBJECT_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_TYPE                            STATUS         CREATED                                LAST_DDL_TIME
-------------------------------------- -------------- -------------------------------------- --------------------------------------
SYS
ET$0C2E359B0001
TABLE                                  VALID          2019-01-16 22:00:49                    2019-01-16 22:00:49

SYS
ET$0B7721930001
TABLE                                  VALID          2019-01-16 19:19:33                    2019-01-16 19:19:33

能看到2个 SYS用户下状态未VALID状态的表，于TRC文件中得对象名称相同。

2、确认属于dump生成的外部表

select owner,table_name,default_directory_name,access_type from dba_external_tables order by 1,2;


SQL> /

OWNER                          TABLE_NAME                     DEFAULT_DIRECTORY_NAME                                       ACCESS_TYPE

------------------------------ ------------------------------ ------------------------------------------------------------ --------------

SYS                            ET$0B7721930001                DATA_PUMP2                                                   CLOB
SYS                            ET$0C2E359B0001                DATA_PUMP2                                                   CLOB

select * from SYS.ET$0B7721930001;  
select * from SYS.ET$0B7721930001
                  *
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump job.

3、清理DataPump jobs。

drop table SYS.ET$0B7721930001;

Table dropped.

drop table SYS.ET$0C2E359B0001;

Table dropped.

六、错误原因：
外部表对应的操作系统上的外部表文件不存在了。但是，数据依然相信操作系统上的外部表文件还存在。
当DBMS_STATS运行，收集这个外部表的统计信息，由于外部表的文件不存在了，所以出现了上述错误。

外部表不存在的情况如下：
1、没有正确的清理datapump的临时外部表，datapump job结束后，临时外部表应该会被删除掉。
2、外部表文件已经被删除，但是没有清理外部表的定义。

                          部分截取自互联网，如有侵权请与我联系。
                          wechat: 704012932
                          email:  pkweibu@163.com
                          CSDN:   https://blog.csdn.net/weixin_37423880