可恶的ORA-200011;KUP-11024
博客专区 > Vimeo 的博客 > 博客详情
可恶的ORA-200011;KUP-11024
Vimeo 发表于1年前
可恶的ORA-200011;KUP-11024
  • 发表于 1年前
  • 阅读 48
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 学生专属云服务套餐 10元起购>>>   

 

 

Errors in file /EAS/app/oracle/diag/rdbms/hseas/hseas/trace/hseas_j000_16527.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.

Errors in file /EAS/app/oracle/diag/rdbms/hseas/hseas/trace/hseas_j000_16527.trc

内容如下:

*** 2016-07-09 10:09:54.738
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"ET$018300070001"','""', ...)
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.

*** 2016-07-09 10:09:54.780
DBMS_STATS: GATHER_STATS_JOB: GATHER_TABLE_STATS('"SYS"','"ET$030100010001"','""', ...)
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.

说明:外部表只能通过datapumpjob访问。

OK,那我们先看看外部表的情况

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

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$030100010001              TABLE            VALID    08-jul-2016 23:20:06      08-jul-2016 23:20:06
SYS                   ET$018300070001              TABLE            VALID    08-jul-2016 19:40:03      08-jul-2016 19:40:03

 

OK,确实存在两条外部表记录,在查看下这个外部表类型:

select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables
  3  order by 1,2
  4  ;

OWNER                   TABLE_NAME              DEFAULT_DIRECTORY_NAME         ACCESS_
------------------------------ ------------------------------ ------------------------------ -------
SYS                   ET$018300070001              DUMP                 CLOB
SYS                   ET$030100010001              DUMP                 CLOB

SQL> select count(*) from ET$018300070001;
select count(*) from ET$018300070001
*
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.

查询外表表时,报错显示和alert一致。

 

解决方法有两种

第一种:直接干掉这些外部表。

SQL> drop table  ET$018300070001 purge;

Table dropped.

SQL> drop table  ET$030100010001 purge;

Table dropped.

 

 

第二种:锁定这些外部表的统计信息:

dbms_stats.lock_table_stats(‘SYS'.'tabname');

 

然后重新跑impdp。

 

参考:
ORA-20011 ORA-29913 and ORA-29400 with Associated KUP-XXXXX Errors from DBMS_STATS.GATHER_STATS_JOB (文档 ID 1274653.1)
How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ? (文档 ID 336014.1)

 

 

 

 

 

共有 人打赏支持
粉丝 6
博文 106
码字总数 35893
×
Vimeo
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: