In EBS 12.1.3, Gather Schema Stats fails with Cause: FDPSTP failed due to ORA-29913: error in executing ODCIEXTTABLEOPEN callout

Recently in our environment, Gather Schema Statistics program were failing for custom schema with errors

Cause: FDPSTP failed due to ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file ***.csv in Custom Directory not found
ORA-06512: at "APPS.FND_STATS",

From the error message, we can easily conclude that the program was trying to find the file in the custom directory. The program failed because the program couldn't find the file in the respective directory

What could be the reason.


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. The error can also occur during data pump operations.



Solution:

By using the below query, identify the list of external tables in the custom schema

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;

This will list down the table name,owner name and path. If the path is incorrect, modify it first.

Now there are two options.

1.Lock the stats collection for the respective external tables under custom schema.

exec DBMS_STATS.LOCK_TABLE_STATS('owner','external table name');

Then run the gather schema statistics.The program will complete nowmal now and the log message will show that the stats have not been gathered for the locked tables.

Once the program completes, it is feasible to unlock the stats gathering

exec DBMS_STATS.UNLOCK_TABLE_STATS('owner','external table name');


2. This is the best option. Run gather table statisctics for the respective tables one by one. For any external tables, if the program throws an error “file not found”, then create a 0 byte file manually and then run it. It should complete normal now
Once gather table stats completes for all the external tables, run gather schema statistics for custom schema

reference:-ORA-20011 ORA-29913 and ORA-29400 with Associated KUP-XXXXX Errors from DBMS_STATS.GATHER_STATS_JOB (Doc ID 1274653.1)