ORA-20011 ORA-29913 and ORA-29400 with associated kup-xxxxx Errors from Dbms_stats. Gather_stats_job (Doc ID 1274653.1)

First, the following bug is seen in alert log:

Dbms_stats:gather_stats_job encountered errors. Check the trace file. Errors in FILE/ORACLE/DIAG/RDBMS/PHALR/PHALR/TRACE/PHALR_J001_5306.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.

View TRC files

[email protected]:/oracle/diag/rdbms/phalr/phalr/trace> more/oracle/diag/rdbms/phalr/phalr/trace/ PHALR_J001_5306.TRC Trace file/oracle/diag/rdbms/phalr/phalr/trace/phalr_j001_5306.trc Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production with the partitioning, OLAP, Data Mining and Real application Testing options Oracle_home =/oracle/product/11.2.0 System name:    Linux Node name: & nbsp;  phalrdb1 release:    2.6.32-431.el6.x86_64 version:    #1 SMP Sun Nov 22:19:54 EST machine:    x86_64 Instance name:phalr Redo thread mounted by the Stance:1 Oracle Process number:46 Unix process pid:5306, Image: [email protected] (J001) 2016-11-19 06:00:13.568 SESSION ID: (2209.51473) 2016-11-19 06:00:13.568 CLIENT ID: () 2016-11-19 06:00:13.568 SERVICE NAME: (sys$users) 2016-11-19 06:00:13.568 MODULE NAME: (dbms_scheduler) 2016-11-19 06:00:13.568 ACTION NAME: (ora$at_os_opt_sy_2339) 2016-11-19 06:00:13.568 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-11-19 06:00:13.568 DBMS_STATS:GATHER_STATS_JOB:GATHER_TABLE_STATS (' "PHALWDA1" ', ' "et$081400230001" ', ' "" ', ...) 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.

Not very clear, go to MOS search an article

ORA-20011 ORA-29913 and ORA-29400 with associated kup-xxxxx Errors from Dbms_stats. Gather_stats_job (Doc ID 1274653.1)

This article describes in detail the resolution of this exception, we import and export in the Datadump, will produce a similar sys_export_table_02 table, if it is normal to finish the Oracle will automatically clear the table, if the import and export when interrupted, Then this table will not be cleared, and the cause of this Ora error is related to this.

Temporary datapump external tables has not been cleaned up properly. The dictionary information should has been dropped when the datapump jobs completed.

The solution is to clear the temporary external table that datapump left behind, and here's the detailed procedure

[BEGIN] 2016/11/25 05:19:51 SET Lines 200 COL owner_name FORMAT A10; COL job_name FORMAT A20 COL State FORMAT A12 COL operation like state COL Job_mode like state COL Owner.object for A50 Sql> --Locate Data Pump jobs: Sql> Select Owner_name, Job_name, RTrim (operation) "Operation", RTrim (Job_mode) "Job_mode", state, Attached_sessions From Dba_datapump_jobs WHERE job_name not like ' bin$% ' 5 ORDER by 1, 2; Owner_name job_name Operation Job_mode State attached_sessions ---------- -------------------- ------------ ------------ ------------ ----------------- PHALWDA1 sys_export_table_01 EXPORT TABLE not RUNNING 0 PHALWDA1 sys_export_table_02 EXPORT TABLE not RUNNING 0 PHALWDA1 sys_import_full_01 IMPORT Full not RUNNING 0 SELECT O.status, o.object_id, O.object_type, o.owner| | '. ' | | object_name "OWNER. OBJECT " From Dba_objects O, Dba_datapump_jobs J WHERE O.owner=j.owner_name and O.object_name=j.job_name 5 and J.job_name not as ' bin$% ' ORDER by 4, 2; STATUS object_id object_type OWNER. OBJECT --------------------- ---------- --------------------------------------------------------- ----------------------- --------------------------- VALID 76494 TABLE PHALWDA1. Sys_export_table_01 VALID 76497 TABLE PHALWDA1. Sys_export_table_02 VALID 81868 TABLE PHALWDA1. Sys_import_full_01 sql> drop table PHALWDA1. sys_export_table_01; Table dropped. sql> drop table PHALWDA1. sys_export_table_02; Table dropped. sql> drop table PHALWDA1. sys_import_full_01; Table dropped. Sql> purge Dba_recyclebin; DBA RecycleBin purged. SET Lines 200 COL owner_name FORMAT A10; COL job_name FORMAT A20 COL State FORMAT A12 COL operation like state COL Job_mode like state COL Owner.object for A50 Sql> --Locate Data Pump jobs: Sql> Select Owner_name, Job_name, RTrim (operation) "Operation", RTrim (Job_mode) "Job_mode", state, Attached_sessions From Dba_datapump_jobs WHERE job_name not like ' bin$% ' 5 ORDER by 1, 2; No rows selected Spool Obj.out Set Linesize 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 Sql> 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$% ' / Select owner, TABLE_NAME, Default_directory_name, Access_type OWNER object_name object_type STATUS CREATED last_ddl_time ------------------------------ ------------------------------ ------------------------- --------------------- ----- -------------------- ------------------------- PHALWDA1 et$081400230001 TABLE VALID 01-oct-2016 08:35:49 01-oct-201 6 08:35:49 From Dba_external_tables ORDER BY / OWNER table_name ------------------------------ --------------------------------------------------------------------------------- --------- Default_directory_name Access_type ------------------------------------------------------------------------------------------ --------------------- PHALWDA1 et$081400230001 BACKUP CLOB sql> sql> spool off Sql> quit disconnected from Oracle Database 11g Enterprise Edition Release 11 .2.0.4.0-64bit Production With the partitioning, OLAP, Data Mining and Real Application testing Options [EMAIL&N Bsp;protected]:/home/oracle> ls cc  chk_cmd  chk_lst  dba_cmd  obj.out [email  Protected]:/home/oracle> more obj.out sql> set linesize trimspool on sql> set pagesize + sql& Gt Col owner Form A30 sql> col created form A25 sql> col last_ddl_time form A25 sql> Col object_name for M A30 sql> col object_type form A25 sql> sql> 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&N Bsp / OWNER object_name object_type STATUS CREATED Last_ddl_time ------------------------------ ------------------------------ ------------------------- --------------------- ----- -------------------- -------------------- ----- PHALWDA1 et$081400230001 TABLE VALID 01-oct-2016 08:35:49 01-oct-2016 08:35:49 Sql> Sql> Select owner, TABLE_NAME, Default_directory_name, Access_type 2 from Dba_external_tables 3 ORDER BY 4/ OWNER table_name ------------------------------ --------------------------------------------------------------------------------- --------- Default_directory_name Access_type ------------------------------------------------------------------------------------------ --------------------- PHALWDA1 et$081400230001 BACKUP CLOB Sql> Sql> Spool Off [Email protected]:/home/oracle> sqlplus/as SYSDBA Sql*plus:release 11.2.0.4.0 Production on Fri Nov 25 17:33:55 2016 Copyright (c) 1982, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit Production With the partitioning, OLAP, Data Mining and Real application testing options sql> drop table phalwda1.et$081400230001 purge; Table dropped. [END] 2016/11/25 05:35:40

Reference:

ORA-20011 ORA-29913 and ORA-29400 with associated kup-xxxxx Errors from Dbms_stats. Gather_stats_job (Doc ID 1274653.1)

How to Cleanup orphaned datapump Jobs in Dba_datapump_jobs? (Doc ID 336014.1)

ORA-20011 ORA-29913 and ORA-29400 with associated kup-xxxxx Errors from Dbms_stats. Gather_stats_job (Doc ID 1274653.1)