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

Hi,

When I run the DBMS_STATS as parallel , I got ” ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists ” error.

ORA-20011: Approximate NDV failed

Details of error are as follows.

begin
dbms_stats.gather_table_stats(
ownname => 'MSDBA',
tabname => 'TEST_PARTITONED_TABLE',
estimate_percent => dbms_stats.auto_sample_size,
degree => 96,
granularity =>'ALL'
);
end;
Error at line 1
ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_STATS", line 40751
ORA-06512: at "SYS.DBMS_STATS", line 40035

ORA-08103: object no longer exists

This error is related with Partitioned table and Parallel execution on Cluster Nodes.

ORA-06512: at “SYS.DBMS_STATS”

To solve this problem, You can run the same SQL as follows.

set the following trace events, then run the same SQL as follows.

alter session set events '10384 trace name context forever , level 16384';

SQL> alter session set events '10384 trace name context forever , level 16384';

Session altered.

SQL> ALTER SESSION SET PARALLEL_FORCE_LOCAL=TRUE;

Session altered.

SQL> begin
dbms_stats.gather_table_stats(
ownname => 'MSDBA',
tabname => 'TEST_PARTITONED_TABLE',
estimate_percent => dbms_stats.auto_sample_size,
degree => 96,
granularity =>'ALL'
);
end;
/

PL/SQL procedure successfully completed.

Or the second Case’s solution is as follows.

1. Set granularity to ‘PARTITION’:

— Globally :

or

— Just for the table:

Or

2. Gather statistics after DDL and DML’s have been completed.

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )