Stats job fail with errors ORA-20011,ORA-29913, KUP-11024

Error Details from Alert log:

Mon Sep 04 22:00:11 2017

DBMS_STATS: GATHER_STATS_JOB encountered errors.  Check the trace file.

Errors in file /u01/app/oracle/diag/rdbms/hbar/hbar2/trace/hbar2_j002_1614.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.

Mon Sep 04 22:01:20 2017

Cause:

1. Issue with external tables created during data pump job

2. Data pump job might running same time stats are gathering or external tables have not been cleaned up properly. 

Solution:  

1. Check if any data pump jobs are running , external tables will drop once job completes, Skip stats gather jobs during data pump running  

2. List external tables which are not cleaned up after data pump job and purge them

SQL> select OWNER_NAME,JOB_NAME,OPERATION,STATE from dba_datapump_jobs;

OWNER_NAME  JOB_NAME             OPERATION    STATE       

---------- --------------------- ---------- ----------

SYS        SYS_IMPORT_TABLE_01   IMPORT      EXECUTING   -- Datapump job running

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$%';

OWNER              OBJECT_NAME           OBJECT_TYPE          STATUS  CREATED                       LAST_DDL_TIME

-----------   -------------------------- ------------------- ------- ----------------------------- -----------------------------

SYSTEM             ET$13A3DFAD0001         TABLE                VALID   14-mar-2015 15:00:59          14-mar-2015 15:00:59

SYS                ET$00FE052F0002         TABLE                VALID   04-sep-2017 22:35:29          04-sep-2017 22:35:29 --- Table related to running job

3.  Purge external table which was not cleaned up properly

drop table SYSTEM.ET$13A3DFAD0001  purge;