oracle expdp作业外表报错ORA-20011&KUP-11024&ORA-29913实例
2018年1月份第一次巡检中，某地市oracle告警ora-20011&kup-11024&ora-29913，
相关处理如下：
操作版本：rhel 2.6.39-400.17.1.el6uek.x86_64
操作系统内核：rhel 2.6.39-400.17.1.el6uek.x86_64
数据库版本：oracle 11.2.0.4
问题描述：数据库对数据泵作业残余外部表收集统计信息报错：ora-20011&kup-11024&ora-29913
告警日志报错信息如下：
09/01/2018 22:00:10 dbms_stats: gather_stats_job encountered errors. check the trace file.
ora-20011: approximate ndv failed: ora-29913: error in executing odciexttableopen callout
tue jan 09 22:00:10 2018
kup-11024: this external table can only be accessed from within a data pump job.
errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j003_333.trc
查看告警日志提示的trc跟踪文件orcl_j003_333.trc
trace file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j003_333.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/db_1
system name: linux
node name: cis.db
release: 2.6.39-400.17.1.el6uek.x86_64
version: #1 smp fri feb 22 18:16:18 pst 2013
machine: x86_64
instance name: orcl
redo thread mounted by this instance: 1
oracle process number: 57
unix process pid: 333, image: oracle@cis.db (j003)
*** 2018-01-09 22:00:10.612
*** session id:(634.22947) 2018-01-09 22:00:10.612
*** client id:() 2018-01-09 22:00:10.612
*** service name:(sys$users) 2018-01-09 22:00:10.612
*** module name:(dbms_scheduler) 2018-01-09 22:00:10.612
*** action name:(ora$at_os_opt_sy_152) 2018-01-09 22:00:10.612
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.
*** 2018-01-09 22:00:10.612
dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$01b7017f0001"','""', ...)
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.
*** 2018-01-09 22:00:10.656
dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$01c303530001"','""', ...)
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.
*** 2018-01-09 22:00:10.686
dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$01982c090001"','""', ...)
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.
*** 2018-01-09 22:00:10.711
dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$01d400370001"','""', ...)
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.
*** 2018-01-09 22:00:10.735
dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$01d3186f0001"','""', ...)
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.
*** 2018-01-09 22:00:10.759
dbms_stats: gather_stats_job: gather_table_stats('"expdp"','"et$00251aef0001"','""', ...)
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 job 运行时，没有datapump job运行
sql> !ps -ef|grep expdp
oracle 28440 28067 0 09:49 pts/0 00:00:00 /bin/bash -c ps -ef|grep expdp
oracle 28442 28440 0 09:49 pts/0 00:00:00 grep expdp
2、确定外部表属于datapump job
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,
2 to_char(created,'dd-mon-yyyy hh24:mi:ss') created,
3 to_char(last_ddl_time, 'dd-mon-yyyy hh24:mi:ss') last_ddl_time
4 from dba_objects
5 where object_name like 'et$%';
owner object_name object_type status created last_ddl_time
------------------------------ ------------------------------ ------------------------- ------- ------------------------- -------------------------
expdp et$01d400370001 table valid 09-oct-2015 23:11:41 09-oct-2015 23:11:41
expdp et$01d3186f0001 table valid 18-dec-2015 02:20:04 18-dec-2015 02:20:04
expdp et$01c303530001 table valid 15-mar-2015 00:45:46 15-mar-2015 00:45:46
expdp et$01b7017f0001 table valid 15-mar-2015 00:42:03 15-mar-2015 00:42:03
expdp et$01982c090001 table valid 08-oct-2015 23:51:06 08-oct-2015 23:51:06
expdp et$00251aef0001 table valid 29-dec-2015 00:50:20 29-dec-2015 00:50:20
6 rows selected.
sql> select owner, table_name, default_directory_name, access_type
from dba_external_tables
order by 1,2;
owner table_name default_directory_name access_
------------------------------ ------------------------------ ------------------------------ -------
expdp et$00251aef0001 expdata clob
expdp et$01982c090001 expdata clob
expdp et$01b7017f0001 expdata clob
expdp et$01c303530001 expdata clob
expdp et$01d3186f0001 expdata clob
expdp et$01d400370001 expdata clob
sys alert_log_view bdump clob
--查询外部表，报的错误和alert日志一样
sql> select count(*) from expdp.et$00251aef0001;
select count(*) from expdp.et$00251aef0001
*
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.
sql>
--查看外部表定义
sql> desc et$00251aef0001
error:
ora-04043: object et$00251aef0001 does not exist
3、清理datapump jobs。
sql> select 'drop table '||owner||'.'||object_name||' purge ;' from dba_objects where object_name like 'et$%';
'droptable'||owner||'.'||object_name||'purge;'
----------------------------------------------------
drop table expdp.et$01d400370001 purge ;
drop table expdp.et$01d3186f0001 purge ;
drop table expdp.et$01c303530001 purge ;
drop table expdp.et$01b7017f0001 purge ;
drop table expdp.et$01982c090001 purge ;
drop table expdp.et$00251aef0001 purge ;
6 rows selected.
sql> drop table expdp.et$01d400370001 purge ;
drop table expdp.et$01d3186f0001 purge ;
drop table expdp.et$01c303530001 purge ;
drop table expdp.et$01b7017f0001 purge ;
drop table expdp.et$01982c090001 purge ;
drop table expdp.et$00251aef0001 purge ;
table dropped.
sql>
table dropped.
sql>
table dropped.
sql>
table dropped.
sql>
table dropped.
sql>
table dropped.