某客户某套生产系统发现如下报错：
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;
