ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

The ORA-20011, can be found in alert.log and this error occurs when DBMS_STATS:GATHER_STATS_JOB gathering stats

ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists

You identify the issue set the following trace events
SQL> alter session set events '10384 trace name context forever , level 16384';

After research found that object truncate and starts are gathering at same time. In case of partition set granularity to ‘PARTITION’

DB level
SQL> exec dbms_stats.set_global_prefs(pname=>'GRANULARITY',pvalue=>'PARTITION')

Table level
SQL> exec dbms_stats.set_table_prefs(ownname=>'Owner_Name',tabname=>'<Table_Name>',pname=>'GRANULARITY',pvalue=>'PARTITION')

Run the gather stats after DDL or DML have been completed.

Also note that “ORA-08103 object no longer exists” error occurs when another user has deleted object since the operation began. In case of incomplete recovery of the object, the work around would be to remove references to the object or delete the object.

When using Global temporary table with ON COMMIT DELETE ROWS options, may encounter ORA-08103: object no longer exists when commit statement that followed right after the delete statement.

In this case recreation of global temporary table with ON COMMIT PRESERVE ROWS clause will helps to safely fetch data

There are several bugs related to ORA-8103 error, check oracle support for more details.

Bug 13618170

Bug 5523799
Bug 5637976

Thanks & Regards,

http://oracleracexpert.com, Oracle ACE