Analysis of ORA-20011 KUP-11024 errors in Oracle

The database alert log appears ORA-20011 KUP-11024 errors
Thu Sep 22 18:00:31 2016
Dbms_stats:gather_stats_job encountered errors. Check the trace file.
Errors in FILE/U1/ORACLE/DIAG/RDBMS/XIFENFEI/XIFENFEI/TRACE/XIFENFEI_J002_2686.TRC:
Ora-20011:approximate NDV failed:ora-29913:error in executing odciexttableopen callout
Kup-11024:this External table can only is accessed from within a Data Pump job.
From the information to see the error should be the database collection statistics error (gather_stats_job), but the error is due to access to the external table, and the external table is likely to be related to data pump.

View trace log
[Oracle@xifenfei]$ MORE/U1/ORACLE/DIAG/RDBMS/XIFENFEI/XIFENFEI/TRACE/XIFENFEI_J002_2686.TRC
Trace FILE/U1/ORACLE/DIAG/RDBMS/XIFENFEI/XIFENFEI/TRACE/XIFENFEI_J002_2686.TRC
Oracle Database 11g Enterprise Edition release 11.2.0.3.0-64bit Production
With the partitioning, OLAP, Data Mining and real application testing options
Oracle_home =/u1/oracle/pruduct/11.2.0.3
System Name:linux
Node Name:xifenfei
Release:2.6.32-220.el6.x86_64
Version: #1 SMP Wed Nov 9 08:03:13 EST 2011
Machine:x86_64
Instance Name:xifenfei
Redo thread mounted by this instance:1
Oracle Process number:356
Unix process pid:2686, Image:oracle@xifenfei (J002)


2016-09-22 18:00:31.939
Session ID: (835.16363) 2016-09-22 18:00:31.939
CLIENT ID: () 2016-09-22 18:00:31.939
SERVICE NAME: (sys$users) 2016-09-22 18:00:31.939
MODULE NAME: (dbms_scheduler) 2016-09-22 18:00:31.939
ACTION NAME: (ora$at_os_opt_sy_10669) 2016-09-22 18:00:31.939

Ora-20011:approximate NDV failed:ora-29913:error in executing odciexttableopen callout
Kup-11024:this External table can only is accessed from within a Data Pump job.

2016-09-22 18:00:31.939
DBMS_STATS:GATHER_STATS_JOB:GATHER_TABLE_STATS (' Dwdba "', '" et$012d00070001 ', ' "" ", ...)
Dbms_stats:ora-20011:approximate NDV failed:ora-29913:error in executing odciexttableopen callout
Kup-11024:this External table can only is accessed from within a Data Pump job.

2016-09-22 18:00:31.960
DBMS_STATS:GATHER_STATS_JOB:GATHER_TABLE_STATS (' Dwdba "', '" et$01d10d4f0001 ', ' "" ", ...)
Dbms_stats:ora-20011:approximate NDV failed:ora-29913:error in executing odciexttableopen callout
Kup-11024:this External table can only is accessed from within a Data Pump job.
With the trace file, we have been able to make it clear that the errors seen in the above alert log were reported by the database when collecting statistics on the dwdba.et$012d00070001 and dwdba.et$01d10d4f0001 tables.

Querying database records
Sys@xifenfei>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$% '
6/

OWNER object_name object_type STATUS CREATED last_ddl_time
--------- ---------------- ------------ ------- ------------------------- ----------------
DWDBA et$012d00070001 TABLE VALID 10-mar-2016 16:32:25 10-mar-2016 16:32:25
DWDBA et$01d10d4f0001 TABLE VALID 10-mar-2016 17:29:29 10-mar-2016 17:29:29

Sys@xifenfei> Select owner, TABLE_NAME, Default_directory_name, Access_type
2 from Dba_external_tables
3 ORDER BY 1,2
4/

OWNER table_name Default_directory_name Access_
----------- ------------------------------ ------------------------------ -------
DWDBA et$012d00070001 Exp_file_dir CLOB
DWDBA et$01d10d4f0001 Exp_file_dir CLOB
By this step, we are fully aware that et$012d00070001 and et$01d10d4f0001 are two external tables, because their presence makes the collection of statistical information unusual.

Analysis of et$012d00070001 tables
Sys@xifenfei>desc dwdba.et$012d00070001
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
Store_no Number (3)
Item_no Number (6)
Work_date DATE
Division_no Number (2)
Section_no Number (3)
Sup_no Number (6)
Grp_no Number (3)
Subgrp_no Number (3)
USR VARCHAR2 (30)
TYPE Number (1)
Active_sell_price Number (8,2)
Sell_price Number (8,2)
Sell_vat Number (1)
Buy_price Number (10,4)
Buy_vat Number (1)
Promotion_no Number (10)
Prom_class VARCHAR2 (1)
Prom_level Number (1)
Stock Number (10,3)
Stock_adj Number (10,3)
RECPT Number (10,3)
SALES Number (10,3)
Stock_adj_amnt Number (10,2)
Recpt_amnt Number (10,2)
Sales_amnt Number (10,2)
Prof_amnt Number (10,2)
Cost_change Number (10,2)
DISC Number (10,3)
Rtn_qty Number (9,3)
Disc_amnt Number (10,2)
Rtn_amnt Number (10,2)
Loss_amnt Number (10,2)
Created_date DATE
Cost Number (10,4)
NBR_PK Number (5)
Nbr_visit Number (5)
Nbr_pk_line Number (5)
N_n_prof_amnt Number (9,2)
Con_fore Number (10,2)
Con_fore_oth Number (10,2)
Sales_b Number (10,3)
Sales_amnt_b Number (10,2)

Sys@xifenfei>select Count (*) from dwdba.et$012d00070001;
Select COUNT (*) from dwdba.et$012d00070001
*
ERROR at line 1:
Ora-29913:error in executing odciexttableopen callout
Kup-11024:this External table can only is accessed from within a Data Pump job.
by et$012d00070001 the same error as the alert log on the table query, it is clear that the problem is due to the external table exception. By querying MoS, determine the bug 10327346 Dbms_workload_capture does not Drop external tables (causing ORA-20011 from dbms_stats) may cause dbms_workload_capture to not properly clean up data The problem occurs when an pump external table causes an orphaned external Table object to appear datapump. The solution is to drop the related external table directly. That's right here (et$012d00070001 and et$01d10d4f0001)

Original from http://www.xifenfei.com/2016/10/ora-20011-kup-11024.html