ORA-20011,ORA-29913,KUP-11024

问题现象：
巡检时发现告警日志出现如下错误：

[oracle@cjc-db01 trace]$ pwd
/oracle/db/diag/rdbms/sycjcdb/cjcdb1/trace
[oracle@cjc-db01 trace]$ vim alert_cjcdb1.log 
......
Sat Feb 20 14:04:19 2021
DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.
Errors in file /oracle/db/diag/rdbms/sycjcdb/cjcdb1/trace/cjcdb1_j000_237960.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.
Sat Feb 20 14:04:30 2021

查看对应trace日志
可以看到在收集cjc用户下ET$0BBB00530002外部表统计信息时出现的问题,该外部表和Data Pump job有关。

[oracle@cjc-db01 trace]$ vim /oracle/db/diag/rdbms/sycjcdb/cjcdb1/trace/cjcdb1_j000_237960.trc
Trace file /oracle/db/diag/rdbms/sycjcdb/cjcdb1/trace/cjcdb1_j000_237960.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 = /oracle/db/product/11.2.0
System name:    Linux
Node name:      cjc-db01
Release:        3.10.0-957.el7.x86_64
Version:        #1 SMP Thu Oct 4 20:48:51 UTC 2018
Machine:        x86_64
Instance name: cjcdb1
Redo thread mounted by this instance: 1
Oracle process number: 260
Unix process pid: 237960, image: oracle@cjc-db01 (J000)
*** 2021-02-20 14:04:19.633
*** SESSION ID:(1906.62839) 2021-02-20 14:04:19.633
*** CLIENT ID:() 2021-02-20 14:04:19.633
*** SERVICE NAME:(SYS$USERS) 2021-02-20 14:04:19.633
*** MODULE NAME:(DBMS_SCHEDULER) 2021-02-20 14:04:19.633
*** ACTION NAME:(ORA$AT_OS_OPT_SY_17303) 2021-02-20 14:04:19.633
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.
*** 2021-02-20 14:04:19.633
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"cjc"','"ET$0BBB00530002"','""', ...)
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.

查看对象创建时间等信息

set linesize 200 
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$%';
/
OWNER       OBJECT_NAME      OBJECT_TYPESTATUSCREATED   LAST_DDL_TIME
------------------------------ ------------------------------ ------------------------- ------- ------------------------- -------------------------
cjc       ET$0BBB00530002      TABLEVALID09-jan-2021 00:27:34  09-jan-2021 00:27:34

查看告警日志，显示执行impdp时间和ET$0BBB00530002外部表创建时间吻合。

Sat Jan 09 00:27:32 2021
DM00 started with pid=634, OS id=125529, job cjc.SYS_IMPORT_TABLE_01
Sat Jan 09 00:27:32 2021
DW00 started with pid=638, OS id=125540, wid=1, job cjc.SYS_IMPORT_TABLE_01
Sat Jan 09 00:27:35 2021
Thread 1 advanced to log sequence 266142 (LGWR switch)
  Current log# 6 seq# 266142 mem# 0: +BJ_SY_STMM_DATA/sycjcdb/onlinelog/redo14a
  Current log# 6 seq# 266142 mem# 1: +BJ_SY_STMM_DATA/sycjcdb/onlinelog/redo14b

查看外部表信息

select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
  from dba_external_tables
 order by 1, 2;
OWNER       TABLE_NAME      DEFAULT_DIRECTORY_NAME     ACCESS_
------------------------------ ------------------------------ ------------------------------ -------
cjc       ET$0BBB00530002      BAK_DIR     CLOB

查看路径信息

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;
 TABLE_NAME       OWNER
------------------------------ ------------------------------
path
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ET$0BBB00530002        cjc
/dbbackup/BAK_DIR

查看表结构

SQL> desc cjc.ET$0BBB00530002
 Name   Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 TRANSSN    VARCHAR2(50)
 CREATEDATE    DATE
 BUSSIDATA    CLOB

解决方案：
删除该外部表(删除前确保该外部表不是业务表)

drop table table_name purge;

或锁定该表统计信息

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

参考MOS文档1274653.1

ORA-20011 ORA-29913 and ORA-29400 with Associated KUP-XXXXX Errors from DBMS_STATS.GATHER_STATS_JOB (Doc ID 1274653.1)

作者：chenoracle