Home » SQL & PL/SQL » SQL & PL/SQL » ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists [message #677323] Thu, 12 September 2019 02:56 Go to next message
revathitirun
Messages: 16
Registered: May 2011
Junior Member
Hi All,

When I am trying to execute the STATS GATHER on one particular Table its giving the following error message :


ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332

Code to do stats gather :

DBMS_STATS.GATHER_TABLE_STATS (ownname=>'SCHEMA_NAME', TABNAME=> 'TABLE_NAME' , CASCADE=>TRUE,   degree=>16); 

There is possibility to run this statement two times exactly at the same time.Only few times we are getting this error messages.
Most of the cases its working fine .

Please help me to resolve the issue.What are circumstances which to leads to this error.

Thanks
Revathi.T

Re: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists [message #677333 is a reply to message #677323] Thu, 12 September 2019 07:39 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
revathitirun wrote on Thu, 12 September 2019 02:56
Hi All,

When I am trying to execute the STATS GATHER on one particular Table its giving the following error message :


ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists
ORA-06512: at "SYS.DBMS_STATS", line 24281
ORA-06512: at "SYS.DBMS_STATS", line 24332

Code to do stats gather :

DBMS_STATS.GATHER_TABLE_STATS (ownname=>'SCHEMA_NAME', TABNAME=> 'TABLE_NAME' , CASCADE=>TRUE,   degree=>16); 

There is possibility to run this statement two times exactly at the same time.Only few times we are getting this error messages.
Most of the cases its working fine .

Please help me to resolve the issue.What are circumstances which to leads to this error.

Thanks
Revathi.T

The error means exactly what it says. Apparently, you have some other process that is dropping, and possibly recreating, a table. If it is an external table, I'd suggest you lock stats on it to prevent GATHER_STATS from trying to hit it.
Re: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists [message #677338 is a reply to message #677333] Thu, 12 September 2019 09:32 Go to previous messageGo to next message
revathitirun
Messages: 16
Registered: May 2011
Junior Member
Thanks EdStevens for your interest to resolve the issue.

You are right.There is one more job which was execution at the same time for the same procedure with different parameter.

That table is list partitioned on branch column which contains different location .

Same procedure will be triggered at the same time with the two different branches.

Thanks
Revathi.T




Re: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists [message #677339 is a reply to message #677338] Thu, 12 September 2019 09:39 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
revathitirun wrote on Thu, 12 September 2019 07:32
Thanks EdStevens for your interest to resolve the issue.

You are right.There is one more job which was execution at the same time for the same procedure with different parameter.

That table is list partitioned on branch column which contains different location .

Same procedure will be triggered at the same time with the two different branches.

Thanks
Revathi.T




does table have GLOBAL INDEX?
Re: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists [message #677349 is a reply to message #677339] Fri, 13 September 2019 01:12 Go to previous messageGo to next message
revathitirun
Messages: 16
Registered: May 2011
Junior Member
Hi BlackSwan

Thanks for spending time to analyze the issue.
No We don't any global index on those partitioned table.We have one composite local index on 3 columns.
Partitioned key column also one of the those 3 index.

Thanks
Revathi.T
Re: ORA-20011: Approximate NDV failed: ORA-08103: object no longer exists [message #677437 is a reply to message #677349] Fri, 20 September 2019 06:39 Go to previous message
revathitirun
Messages: 16
Registered: May 2011
Junior Member

Hi All

We found solution to the above problem.As we are going Parallel execution of multiple jobs at the same time with different branches(internally different partitions), instead of the going for full table stats gather every time we are planning to go for Partition gathering.

Even though stats gathering running parallel, each branch can take care of his own partition.

Thanks
Revathi.T
Previous Topic: getting informations about processes
Next Topic: How to call compiled procedure and function in a procedure
Goto Forum:
  


Current Time: Fri Mar 29 04:00:13 CDT 2024