SOLUTION: Temporary Datapump External Table

Essentially the solution to this issue is to clean up the orphaned dictionary entries. Different solutions are appropriate dependent on the exact nature of the primary cause and these are outlined below:

Temporary Datapump External Table

1. Ensure that there are no DataPump jobs running at the same time as the DBMS_STATS job (this is to avoid any potential complications associated with cleaning up at the same time as someting else is running).
2. Check and clean up orphaned DataPump jobs. The following article addresses a case where DBMS_WORKLOAD_CAPTURE does not drop external tables (causing ORA-20011 from DBMS_STATS) 

   Document 10327346.8 Bug 10327346 - DBMS_WORKLOAD_CAPTURE does not drop external tables (causing ORA-20011 from DBMS_STATS)

   
   Both above steps can be done by following Document:

   Document 336014.1 How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?

3. Identify external tables. To do this, Run the following as SYSDBA in SQL*Plus

spool obj.out
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$%'
/

select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from dba_external_tables
order by 1,2
/

spool off

Correlate the information from DBA_OBJECTS and DBA_EXTERNAL TABLES above to identify the temporary external tables that belong to the DataPump

      4. Drop the temporary external tables that belong to the DataPump. eg:

SQL> drop table system.&1 purge;
Enter value for 1: ET$00654E1E0001
old 1: drop table system.&1 purge
new 1: drop table system.ET$00654E1E0001 purge

Other External Table

With cases where specific External tables (be they Demo Schema tables or other tables) are missing, the process for handling them is much the same and can be resolved by following the procedures below. For example, if the additional error is 'error opening file ../demo/schema/log/ext_1v3.log', then this indicates that there is a problem opening or locating the log file from the demo schema directory. The implication is that the demo tables have not been cleared up correctly:

     1.  Try to locate the files for these tables in their directory. 

          To confirm if a file is present at the expected location for the external file, the following query might be helpful. 

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;

          It may be that the files still exist but they have just been renamed or re-located. If that is the case you can correct the location to avoid the problem. If the file has been removed then follow either the following steps:

     2.  Lock the statistics on these tables by using the following command:

DBMS_STATS.LOCK_TABLE_STATS ('ownname','tabname');

     (This step prevents DBMS_STATS from gathering against the missing table)

     AND/OR

     Remove the dictionary object for the external table.  For example if the os file for the "SALES_TRANSACTIONS_EXT" Demo table is missing you would use :

DROP TABLE SALES_TRANSACTIONS_EXT;