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.
Alert log File information:
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the
trace file.
Errors in file
/u01/app/oracle/admin/testdb/diag/rdbms/testdb/testdb/trace/testdb_j000_22426.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.
Trace File Information:
*** 2015-03-26 08:30:18.342
DBMS_STATS: GATHER_STATS_JOB:
GATHER_TABLE_STATS('"OPS$ORACLE"','"ET$016FA3770001"','""',
...)
DBMS_STATS: 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.
Cause:
The primary cause of this issue is that an external table existed
at some point in time but does not now. However, the database still believes
the table exists since the dictionary information about the object has not been
modified to reflect the change. When DBMS_STATS is run against the table in
question, it makes a call out to the external table which fails because the
object is not there.
There are many reasons that an external table may not exist
including:
1. Temporary Data pump external tables have not been cleaned up
properly. The dictionary information should have been dropped when the DataPump
jobs completed.
2. An External table has been removed without clearing up the
corresponding data dictionary information. For example: Oracle Demo Schema
Tables such as the external table “SALES_TRANSACTIONS_EXT” may have been
removed but the dictionary has not been updated to reflect this. The
"SALES_TRANSACTIONS_EXT" table is an external table in the
"SH" schema which is one of Demo Schema provided by Oracle.
Note:Our
issue is due to point one. External tables are not cleaned properly.
Solutions: To clean up the Orphaned datapump jobs.
Check and cleanup orphaned datapump jobs:
SELECT owner_name, job_name, operation, job_mode,state,
attached_sessions FROM dba_datapump_jobs
WHERE job_name NOT LIKE 'BIN$%'
ORDER BY 1,2;
To identify the external tables.
SQL> conn / as sysdba
Connected.
SQL>
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
------------------------------ ------------------------------
------------------------- ------- -------------------------
-------------------------
OPS$ORACLE
ET$000E4FF90001
TABLE
VALID 16-oct-2012 13:10:15
16-oct-2012 13:10:15
OPS$ORACLE
ET$007360190001
TABLE
VALID 18-sep-2012 23:17:32
18-sep-2012 23:17:32
OPS$ORACLE
ET$00F39F430001
TABLE
VALID 16-oct-2012 13:33:10
16-oct-2012 13:33:10
OPS$ORACLE
ET$016FA3770001
TABLE
VALID 16-oct-2012 13:57:36
16-oct-2012 13:57:36
8 rows selected.
SQL>
select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME, ACCESS_TYPE
from
dba_external_tables order by 1,2
/
OWNER
TABLE_NAME
DEFAULT_DIRECTORY_NAME ACCESS_
------------------------------ ------------------------------
------------------------------ -------
OPS$ORACLE
ET$000E4FF90001
PRODUCT_REFRESH_DIR
CLOB
OPS$ORACLE
ET$007360190001
PRODUCT_REFRESH_DIR
CLOB
OPS$ORACLE
ET$00F39F430001
PRODUCT_REFRESH_DIR
CLOB
OPS$ORACLE
ET$016FA3770001
PRODUCT_REFRESH_DIR
CLOB
To Drop the external temporary datapump tables.
SQL> drop table OPS$ORACLE.ET$000E4FF90001;
SQL> drop table OPS$ORACLE.ET$007360190001;
SQL> drop table OPS$ORACLE.ET$00F39F430001;
SQL> drop table OPS$ORACLE.ET$016FA3770001;
5.To ensure there is no datapump temporary tables.
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$%'
/
no rows selected
SQL> select owner, TABLE_NAME, DEFAULT_DIRECTORY_NAME,
ACCESS_TYPE from dba_external_tables order by 1,2
/
Reference doc 1274653.1
No comments:
Post a Comment