问题现象：
巡检时发现告警日志出现如下错误：
[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)
###2020-02-21 12:30 陈举超###