<<Back to DB Administration Main Page
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.Errors in file /u01/diag/rdbms/orcl1d/ORCL1D/trace/ORCL1D_j000_24858.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file PSG_XVD.csv in DUMP_DATA not found
Tue Sep 10 22:05:06 2019
Cause
The primary cause of this issue is that an OS file for an "external table" existed at some point in time but does not now. However, the database still believes the OS file for 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.
Solution
Clean up the orphaned dictionary entries.
For Temporary Datapump External Table
check out the following document from oracle support and follow the steps accordingly.
Document 10327346.8 Bug 10327346 - DBMS_WORKLOAD_CAPTURE does not drop external tables (causing ORA-20011 from DBMS_STATS)
Document 336014.1 How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?
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:
Locate the files for these tables in their directory.
undefine owner
undefine table_pattern
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;
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.- It may be that the directory path itself does not exists on the server.
If file has been renamed or re-located you can restore back the file to avoid the problem.
If the file has been removed or the directory itself has been deleted then follow either the following steps:
Lock the statistics on these tables by using the following command:
DBMS_STATS.LOCK_TABLE_STATS ('HR','EMP');
OR
Remove the dictionary object for the external table. DROP TABLE HR.EMP;
Remove the dictionary object for the external table. DROP TABLE HR.EMP;
More on locking unlocking Stats read here
Comments
Post a Comment