溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務(wù)條款》
您好，登錄后才能下訂單哦！
本篇文章為大家展示了如何解決ORA-20011 KUP-11024 外部表引起的錯誤，內(nèi)容簡明扼要并且容易理解，絕對能使你眼前一亮，通過這篇文章的詳細介紹希望你能有所收獲。
查看alert日志，看到了沒有見過的錯誤。
字面大致意思是：GATHER_STATS_JOB收集統(tǒng)計信息時發(fā)生錯誤。
錯誤發(fā)生在執(zhí)行調(diào)出ODCIEXTTABLEOPEN時；外部表只能通過數(shù)據(jù)泵調(diào)用。
Sat Jun 13 14:07:30 2015
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file /opt/oracle/diag/rdbms/TDB/TDB/trace/TDB_j000_12961.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 Jun 13 14:08:06 2015
Thread 1 cannot allocate new log, sequence 11157
錯誤原因：
外部表對應的操作系統(tǒng)上的外部表文件不存在了。但是，數(shù)據(jù)依然相信操作系統(tǒng)上的外部表文件還存在。
當DBMS_STATS運行，收集這個外部表的統(tǒng)計信息，由于外部表的文件不存在了，所以出現(xiàn)了上述錯誤。
外部表不存在的情況如下：
1、沒有正確的清理datapump的臨時外部表（使用datapump將查詢結(jié)果填充到外部表的情況，http://czmmiao.iteye.com/blog/1268712）。
datapump job結(jié)束后，臨時外部表應該會被刪除掉。
2、外部表文件已經(jīng)被刪除，但是沒有清理外部表的定義。
解決辦法：
1、DBMS_STATS job 運行時，沒有DataPump job運行
2、檢查并清理孤兒DataPump jobs。
3、確定外部表屬于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
select OWNER,OBJECT_NAME,OBJECT_TYPE, status,
to_char(CREATED,'dd-mon-yyyy hh34:mi:ss') created
,to_char(LAST_DDL_TIME , 'dd-mon-yyyy hh34:mi:ss') last_ddl_time
from dba_objects
where object_name like 'ET$%'
/
OWNER OBJECT_NAME OBJECT_TYPE STATUS CREATED LAST_DDL_TIME
------------------------------ ------------------------------ ------------------------- -------------- ------------------------- -------------------------
SCOTT ET$0071FF750001 TABLE VALID 28-feb-2015 16:12:19 04-may-2015 14:43:41
SCOTT ET$007CB0E70001 TABLE VALID 28-feb-2015 16:12:20 04-may-2015 14:43:41
SCOTT ET$008701B10001 TABLE VALID 28-feb-2015 16:12:20 04-may-2015 14:43:41
SCOTT ET$00922EAD0001 TABLE VALID 28-feb-2015 16:35:23 04-may-2015 14:43:41
SCOTT ET$009DD93B0001 TABLE VALID 28-feb-2015 16:40:11 04-may-2015 14:43:41
SCOTT ET$00A83F510001 TABLE VALID 28-feb-2015 16:50:55 04-may-2015 14:43:41
6 rows selected.
select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables
order by 1,2
/
OWNER TABLE_NAME DEFAULT_DI ACCESS_TYPE
---------- -------------------- ---------- --------------
SCOTT ET$0071FF750001 EXPDP CLOB
SCOTT ET$007CB0E70001 EXPDP CLOB
SCOTT ET$008701B10001 EXPDP CLOB
SCOTT ET$00922EAD0001 EXPDP CLOB
SCOTT ET$009DD93B0001 EXPDP CLOB
SCOTT ET$00A83F510001 EXPDP CLOB
查詢外部表，報的錯誤和alert日志一樣
SQL> select count(*) from SCOTT.ET$009DD93B0001;
select count(*) from SCOTT.ET$009DD93B0001
*
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.
外部表ET$0071FF750001的定義,看著像是datapump創(chuàng)建外部表。datapump導出的dmp文件作為外部表的數(shù)據(jù)文件。
CREATE TABLE "SCOTT"."ET$0071FF750001"
( "ORD_ID" NUMBER(15,0),
"ORD_NO" VARCHAR2(30),
"ORD_SEQ" NUMBER(5,0),
"ORD_CD" VARCHAR2(3),
"UNIT_SEQ" NUMBER(15,0),
"PROD_ID" NUMBER(6,0),
"UNIT_ID" NUMBER(3,0),
"WH_CD" VARCHAR2(3),
"RCPT_NO" NUMBER(15,0),
"INVC_ID" VARCHAR2(30),
"VEN_ID" NUMBER(6,0),
"ORD_QTY" NUMBER(10,0),
"CUST_ID" NUMBER(15,0),
"ORD_DATE" DATE,
"ORD_STAT_CD" VARCHAR2(3),
"COST" NUMBER(15,2),
"PRC" NUMBER(15,2),
"SPEC_PRC" NUMBER(15,2),
"TRAN_PRC" NUMBER(15,2),
"ORD_AMT" NUMBER(15,2),
"ORD_SUM_AMT" NUMBER(15,2),
"ADV_ID" NUMBER(10,0),
"MEDIA_ID" NUMBER(10,0),
"EXP_CD" VARCHAR2(20),
"SID" NUMBER(15,0),
"PRVNC_ID" VARCHAR2(10),
"CITY_ID" VARCHAR2(10),
"CNTY_ID" VARCHAR2(10),
"CUST_AGE" VARCHAR2(30),
"CUST_SEX" VARCHAR2(30),
"CUST_NM" VARCHAR2(60),
"BC_ORD_STAT_CD" VARCHAR2(30),
"OUT_CNFM_DATE" DATE,
"OVER_CNFM_DATE" DATE,
"CASH_RECEIVE" NUMBER(15,2),
"CASH_NEED" NUMBER(15,2),
"CUSER_ID" VARCHAR2(60),
"CTIME" DATE,
"UUSER_ID" VARCHAR2(60),
"UTIME" DATE,
"CENTER_CD" VARCHAR2(60),
"OLD_ORD_ID" NUMBER(15,0),
"PAY_ID" NUMBER(15,0),
"BANK_ID" NUMBER(15,0),
"ID" NUMBER(15,0),
"TRADE" NUMBER(15,4),
"UTRADE" NUMBER(15,4),
"TICKET" NUMBER(15,4),
"INTEGRAL" NUMBER(15,4),
"UINTEGRAL" NUMBER(15,4),
"ORDER_FLAG" VARCHAR2(2),
"OUT_BOUND_DATE" DATE,
"RTN_EXN_RSN_CD" VARCHAR2(7)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_DATAPUMP
DEFAULT DIRECTORY "EXPDP"
ACCESS PARAMETERS
( DEBUG = (0 , 0) DATAPUMP INTERNAL TABLE "SCOTT"."TC_BB_ORD" JO
B ( "SCOTT","IVL_CHG_98TAB_20150208",1) WORKERID 1 PARALLEL 1 VERSION '11.2.0.0
.0' ENCRYPTPASSWORDISNULL COMPRESSION DISABLED ENCRYPTION DISABLED TABLEEXISTS
)
LOCATION
( 'bogus.dat'
)
)
REJECT LIMIT UNLIMITED
4、刪除屬于datapump的外部表
drop table SCOTT.ET$0071FF750001 purge;
drop table SCOTT.ET$007CB0E70001 purge;
drop table SCOTT.ET$008701B10001 purge;
drop table SCOTT.ET$00922EAD0001 purge;
drop table SCOTT.ET$009DD93B0001 purge;
drop table SCOTT.ET$00A83F510001 purge;
參考：
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)
上述內(nèi)容就是如何解決ORA-20011 KUP-11024 外部表引起的錯誤，你們學到知識或技能了嗎？如果還想學到更多技能或者豐富自己的知識儲備，歡迎關(guān)注億速云行業(yè)資訊頻道。
免責聲明：本站發(fā)布的內(nèi)容（圖片、視頻和文字）以原創(chuàng)、轉(zhuǎn)載和分享為主，文章觀點不代表本網(wǎng)站立場，如果涉及侵權(quán)請聯(lián)系站長郵箱：is@yisu.com進行舉報，并提供相關(guān)證據(jù)，一經(jīng)查實，將立刻刪除涉嫌侵權(quán)內(nèi)容。
億速云公眾號
手機網(wǎng)站二維碼
