某客户某套生产系统发现如下报错：
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file /u01/app/oracle/diag/rdbms/dsjhkdb/dsjhkdb2/trace/dsjhkdb2_j001_32783.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.
匹配MOS：ORA-20011 ORA-29913 and ORA-29400 with Associated KUP-XXXXX Errors from DBMS_STATS.GATHER_STATS_JOB (Doc ID 1274653.1)
根据MOS给出的解决办法：
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$000A343F0001 TABLE VALID 10-jul-2020 20:27:00 10-jul-2020 20:27:00
SYS ET$000B00AB0001 TABLE VALID 20-jun-2020 21:59:27 20-jun-2020 21:59:27
SYS ET$000C000D0001 TABLE VALID 21-jun-2020 08:27:26 21-jun-2020 08:27:26
SYS ET$000D00090001 TABLE VALID 20-jun-2020 18:23:04 20-jun-2020 18:23:04
SYS ET$000F00150001 TABLE VALID 21-jun-2020 12:05:37 21-jun-2020 12:05:37
SYS ET$001300010001 TABLE VALID 21-jun-2020 18:08:52 21-jun-2020 18:08:52
SYS ET$001311470001 TABLE VALID 11-jul-2020 06:36:46 11-jul-2020 06:36:46
SYS ET$001701FB0001 TABLE VALID 11-jul-2020 06:36:46 11-jul-2020 06:36:46
SYS ET$001800010001 TABLE VALID 18-jun-2020 20:29:59 18-jun-2020 20:29:59
SYS ET$001800090001 TABLE VALID 21-jun-2020 12:05:36 21-jun-2020 12:05:36
SYS ET$0018000B0001 TABLE VALID 21-jun-2020 13:38:02 21-jun-2020 13:38:02
SYS ET$002000970001 TABLE VALID 21-jun-2020 13:38:00 21-jun-2020 13:38:00
SYS ET$02FE01250001 TABLE VALID 21-jun-2020 08:27:23 21-jun-2020 08:27:23
SYS ET$0302021B0001 TABLE VALID 20-jun-2020 18:23:03 20-jun-2020 18:23:03
SYS ET$030300010001 TABLE VALID 21-jun-2020 08:27:26 21-jun-2020 08:27:26
SYS ET$03030C810001 TABLE VALID 20-jun-2020 18:23:04 20-jun-2020 18:23:04
SYS ET$030500050001 TABLE VALID 21-jun-2020 12:05:38 21-jun-2020 12:05:38
SYS ET$030600EF0001 TABLE VALID 20-jun-2020 18:23:05 20-jun-2020 18:23:05
SYS ET$030A00010001 TABLE VALID 21-jun-2020 21:40:19 21-jun-2020 21:40:19
SYS ET$030C03450001 TABLE VALID 10-jul-2020 20:27:00 10-jul-2020 20:27:00
SYS ET$030F00170001 TABLE VALID 20-jun-2020 21:59:28 20-jun-2020 21:59:28
SYS ET$0311002B0001 TABLE VALID 21-jun-2020 13:38:02 21-jun-2020 13:38:02
SYS ET$031200170001 TABLE VALID 21-jun-2020 12:05:37 21-jun-2020 12:05:37
SYS ET$031300250001 TABLE VALID 11-jul-2020 06:36:46 11-jul-2020 06:36:46
SYS ET$05F100210001 TABLE VALID 20-jun-2020 18:23:02 20-jun-2020 18:23:02
SYS ET$05F604710001 TABLE VALID 21-jun-2020 18:08:55 21-jun-2020 18:08:55
SYS ET$05F713990001 TABLE VALID 21-jun-2020 13:38:02 21-jun-2020 13:38:02
SYS ET$05F800070001 TABLE VALID 20-jun-2020 21:59:28 20-jun-2020 21:59:28
SYS ET$05F803110001 TABLE VALID 18-jun-2020 20:29:59 18-jun-2020 20:29:59
SYS ET$05FA01730001 TABLE VALID 20-jun-2020 18:23:03 20-jun-2020 18:23:03
SYS ET$05FC00010001 TABLE VALID 21-jun-2020 08:27:26 21-jun-2020 08:27:26
SYS ET$05FD36DF0001 TABLE VALID 11-jul-2020 06:36:46 11-jul-2020 06:36:46
SYS ET$060132C90001 TABLE VALID 10-jul-2020 20:27:00 10-jul-2020 20:27:00
SYS ET$060600010001 TABLE VALID 18-jun-2020 20:30:03 18-jun-2020 20:30:03
SYS ET$08E9022B0001 TABLE VALID 20-jun-2020 18:23:03 20-jun-2020 18:23:03
SYS ET$08EA00830001 TABLE VALID 21-jun-2020 08:27:26 21-jun-2020 08:27:26
SYS ET$08EB00210001 TABLE VALID 21-jun-2020 12:05:36 21-jun-2020 12:05:36
SYS ET$08EB00BB0001 TABLE VALID 20-jun-2020 18:23:01 20-jun-2020 18:23:01
SYS ET$08ED00870001 TABLE VALID 20-jun-2020 18:23:03 20-jun-2020 18:23:03
SYS ET$08EF00030001 TABLE VALID 20-jun-2020 18:23:03 20-jun-2020 18:23:03
SYS ET$08EF00990001 TABLE VALID 21-jun-2020 20:39:15 21-jun-2020 20:39:15
SYS ET$08F073BF0001 TABLE VALID 10-jul-2020 20:27:00 10-jul-2020 20:27:00
SYS ET$08F502FD0001 TABLE VALID 20-jun-2020 21:59:26 20-jun-2020 21:59:26
SYS ET$08FC01330001 TABLE VALID 21-jun-2020 13:38:02 21-jun-2020 13:38:02
44 rows selected.
select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables
order by 1,2;
OWNER TABLE_NAME DEFAULT_DIRECTORY_NAME ACCESS_
------------------------------ ------------------------------ ------------------------------ -------
SYS ET$000A343F0001 IMPDP1 CLOB
SYS ET$000B00AB0001 IMPDP1 CLOB
SYS ET$000C000D0001 IMPDP1 CLOB
SYS ET$000D00090001 IMPDP1 CLOB
SYS ET$000F00150001 IMPDP1 CLOB
SYS ET$001300010001 IMPDP1 CLOB
SYS ET$001311470001 IMPDP1 CLOB
SYS ET$001701FB0001 IMPDP1 CLOB
SYS ET$001800010001 IMPDP1 CLOB
SYS ET$001800090001 IMPDP1 CLOB
SYS ET$0018000B0001 IMPDP1 CLOB
SYS ET$002000970001 IMPDP1 CLOB
SYS ET$02FE01250001 IMPDP1 CLOB
SYS ET$0302021B0001 IMPDP1 CLOB
SYS ET$030300010001 IMPDP1 CLOB
SYS ET$03030C810001 IMPDP1 CLOB
SYS ET$030500050001 IMPDP1 CLOB
SYS ET$030600EF0001 IMPDP1 CLOB
SYS ET$030A00010001 IMPDP1 CLOB
SYS ET$030C03450001 IMPDP1 CLOB
SYS ET$030F00170001 IMPDP1 CLOB
SYS ET$0311002B0001 IMPDP1 CLOB
SYS ET$031200170001 IMPDP1 CLOB
SYS ET$031300250001 IMPDP1 CLOB
SYS ET$05F100210001 IMPDP1 CLOB
SYS ET$05F604710001 IMPDP1 CLOB
SYS ET$05F713990001 IMPDP1 CLOB
SYS ET$05F800070001 IMPDP1 CLOB
SYS ET$05F803110001 IMPDP1 CLOB
SYS ET$05FA01730001 IMPDP1 CLOB
SYS ET$05FC00010001 IMPDP1 CLOB
SYS ET$05FD36DF0001 IMPDP1 CLOB
SYS ET$060132C90001 IMPDP1 CLOB
SYS ET$060600010001 IMPDP1 CLOB
SYS ET$08E9022B0001 IMPDP1 CLOB
SYS ET$08EA00830001 IMPDP1 CLOB
SYS ET$08EB00210001 IMPDP1 CLOB
SYS ET$08EB00BB0001 IMPDP1 CLOB
SYS ET$08ED00870001 IMPDP1 CLOB
SYS ET$08EF00030001 IMPDP1 CLOB
SYS ET$08EF00990001 IMPDP1 CLOB
SYS ET$08F073BF0001 IMPDP1 CLOB
SYS ET$08F502FD0001 IMPDP1 CLOB
SYS ET$08FC01330001 IMPDP1 CLOB
44 rows selected.
undefine owner
undefine table_pattern
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$000A343F0001 SYS /picctmp/impdp1/IMPDP1
处理如下：
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$000A343F0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$000B00AB0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$000C000D0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$000D00090001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$000F00150001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$001300010001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$001311470001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$001701FB0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$001800010001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$001800090001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$0018000B0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$002000970001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$02FE01250001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$0302021B0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$030300010001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$03030C810001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$030500050001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$030600EF0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$030A00010001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$030C03450001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$030F00170001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$0311002B0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$031200170001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$031300250001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$05F100210001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$05F604710001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$05F713990001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$05F800070001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$05F803110001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$05FA01730001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$05FC00010001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$05FD36DF0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$060132C90001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$060600010001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$08E9022B0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$08EA00830001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$08EB00210001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$08EB00BB0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$08ED00870001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$08EF00030001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$08EF00990001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$08F073BF0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$08F502FD0001');
EXEC DBMS_STATS.LOCK_TABLE_STATS ('SYS','ET$08FC01330001');
drop table ET$000A343F0001;
drop table ET$000B00AB0001;
drop table ET$000C000D0001;
drop table ET$000D00090001;
drop table ET$000F00150001;
drop table ET$001300010001;
drop table ET$001311470001;
drop table ET$001701FB0001;
drop table ET$001800010001;
drop table ET$001800090001;
drop table ET$0018000B0001;
drop table ET$002000970001;
drop table ET$02FE01250001;
drop table ET$0302021B0001;
drop table ET$030300010001;
drop table ET$03030C810001;
drop table ET$030500050001;
drop table ET$030600EF0001;
drop table ET$030A00010001;
drop table ET$030C03450001;
drop table ET$030F00170001;
drop table ET$0311002B0001;
drop table ET$031200170001;
drop table ET$031300250001;
drop table ET$05F100210001;
drop table ET$05F604710001;
drop table ET$05F713990001;
drop table ET$05F800070001;
drop table ET$05F803110001;
drop table ET$05FA01730001;
drop table ET$05FC00010001;
drop table ET$05FD36DF0001;
drop table ET$060132C90001;
drop table ET$060600010001;
drop table ET$08E9022B0001;
drop table ET$08EA00830001;
drop table ET$08EB00210001;
drop table ET$08EB00BB0001;
drop table ET$08ED00870001;
drop table ET$08EF00030001;
drop table ET$08EF00990001;
drop table ET$08F073BF0001;
drop table ET$08F502FD0001;
drop table ET$08FC01330001;
最后修改时间：2021-10-08 15:19:13
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
