问题发现
数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
alert.log
Wed Aug 10 22:00:03 2016
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file /u01/app/diag/rdbms/orcl/orcl/trace/orcl_j003_117689.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.
trace log
Trace file /u01/app/diag/rdbms/orcl/orcl/trace/orcl_j003_117689.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: acarsorcl-primary
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Fri Nov 22 03:15:09 UTC 2013
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 88
Unix process pid: 117689, image: oracle@acarsorcl-primary (J003)
*** 2016-08-10 22:00:03.705
*** SESSION ID:(196.8895) 2016-08-10 22:00:03.705
*** CLIENT ID:() 2016-08-10 22:00:03.705
*** SERVICE NAME:(SYS$USERS) 2016-08-10 22:00:03.705
*** MODULE NAME:(DBMS_SCHEDULER) 2016-08-10 22:00:03.705
*** ACTION NAME:(ORA$AT_OS_OPT_SY_142) 2016-08-10 22:00:03.705
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.
*** 2016-08-10 22:00:03.705
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"EXP"','"ET$00640D550001"','""', ...)
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、DBMS_STATS: GATHER_STATS_JOB包是数据库做统计信息使用的数据库包，时间发生在22:00，数据库每天都在做自动的统计信息收集，时间为平时22:00–2:00四个小时，周末全天。
2、通过trace信息可以看出数据库是在对exp的scheme下的ET$00640D550001表进行收集时报错，exp账号为管理员自己创建的用户，用来做数据库迁移时使用，没有人为创建过任何表，而且通过表名也可以确定出为系统创建的表。
3、查看ET$00640D550001表的定义
create table EXP.ET$00640D550001
(
SPLIT_RESULT_ID NUMBER(10),
MSG_ID VARCHAR2(30),
DATAGRAM_TYPE VARCHAR2(10),
AIRCRAFT_ID VARCHAR2(30),
GATEWAY_TIME DATE,
SPLIT_TEMPLATE_ID NUMBER(10),
SPLIT_RESULT CLOB
)
organization external
(
type ORACLE_DATAPUMP
default directory DIR_EXP
access parameters
(
DEBUG = (0 , 0) DATAPUMP INTERNAL TABLE "SKYXXX"."SPLIT_XXX" JOB ( "EXP","SYS_IMPORT_SCHEMA_01",2) WORKERID 1 PARALLEL 1 VERSION '11.2.0.0.0' ENCRYPTPASSWORDISNULL COMPRESSION DISABLED ENCRYPTION DISABLED
)
location (DIR_EXP:'bogus.dat')
)
reject limit UNLIMITED;
可以看出是在import skylink下的SPLIT_RESULT表时创建的外部表，表的定义与SPLIT_RESULT表一样，数据文件为bogus.dat文件，但是在DIR_EXP目录下并没有此文件。
此时问题已经清楚，在导入表的时候，系统会创建外部表，导入结束后，正常情况下，系统会将数据文件删除后删除表的定义，但是此案例系统只删除了数据文件并没有删除表的定义，或者删除表的定义时出现了问题，导致此错误的发生。
4、验证结论
SQL> select * from exp.ET$00640D550001;
ERROR:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
KUP-11024: This external table can only be accessed from within a Data Pump
job.
no rows selected
问题重现。
问题解决
1、确定job存在数据库中
SQL> SET lines 200
SQL> COL owner_name FORMAT a10
SQL> COL job_name FORMAT a20
SQL> COL state FORMAT a12
SQL> COL operation LIKE state
SQL> COL job_mode LIKE state
SQL> COL owner.object for a50
SQL> SELECT owner_name, job_name, rtrim(operation) "OPERATION",
rtrim(job_mode) "JOB_MODE", state, attached_sessions
FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2; 2 3 4 5
OWNER_NAME JOB_NAME OPERATION JOB_MODE STATE ATTACHED_SESSIONS
---------- -------------------- ------------ ------------ ------------ -----------------
EXP SYS_IMPORT_SCHEMA_01 IMPORT SCHEMA NOT RUNNING 0
2、确定表存在
SQL> SELECT o.status, o.object_id, o.object_type,
o.owner||'.'||object_name "OWNER.OBJECT"
FROM dba_objects o, dba_datapump_jobs j
WHERE o.owner=j.owner_name AND o.object_name=j.job_name
AND j.job_name NOT LIKE 'BIN$%' ORDER BY 4,2; 2 3 4 5
STATUS OBJECT_ID OBJECT_TYPE OWNER.OBJECT
------- ---------- ------------------- --------------------------------------------------
VALID 87606 TABLE EXP.SYS_IMPORT_SCHEMA_01
3、删除表
SQL> drop table EXP.SYS_IMPORT_SCHEMA_01;
Table dropped.
SQL> purge dba_recyclebin;
DBA Recyclebin purged.
问题至此解决，问题处理过程参照MOS Doc ID 336014.1.
