RE: DBMS_STATS and ORA-01422

From: <>
Date: Thu, 11 Sep 2008 12:15:33 -0400
Message-ID: <>

Thank you so much. That resolved my problem. Some how when I searched metalink I could not find this document. I guess I have to relearn my metalink search practice.

Mayen Shah

"Mercadante, Thomas F (LABOR)" <>
Sent by:
Sep 11 2008 10:56 AM
Please respond to

Mayen Shah/ITS/Lazard_at_Lazard NYC, cc
"oracle-l" <>,
RE: DBMS_STATS and ORA-01422

I found the following note with a workaround to try:  


From: [] On Behalf Of
Sent: Thursday, September 11, 2008 10:54 AM To:
Cc: oracle-l; Subject: Re: DBMS_STATS and ORA-01422  

I did open SR with Oracle support but wanted to get any feed back from more knowledgeable group.

Mayen Shah

"Andrew Kerber" <>
Sent by:
Sep 11 2008 10:49 AM

Please respond to

Mayen Shah/ITS/Lazard_at_Lazard NYC
"oracle-l" <>,
Re: DBMS_STATS and ORA-01422

It definitely looks like a dictionary problem. I suggest you open a service requrest with Oracle.

On Thu, Sep 11, 2008 at 9:39 AM, <> wrote:

Hi Gurus,

Recently I migrated one database from to on Solaris 9 (this is test database, copy of production)

Strangely when I tried to update stats on one of the partitioned tables I receive following error:

SQL> exec DBMS_STATS.GATHER_TABLE_STATS (ownname=>'sysadm',tabname=>'PS_JRNL_LN',estimate_percent=>25,,cascade=>TRUE);

(ownname=>'sysadm',tabname=>'PS_JRNL_LN',estimate_percent=>25,,cascade=>TRUE); END; *
ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows 
ORA-06512: at "SYS.DBMS_STATS", line 13182 
ORA-06512: at "SYS.DBMS_STATS", line 13202 
ORA-06512: at line 1 

I checked dual and there is only one row in dual table.

When I run same gather_stats on exact copy of production database still in it ran without any problem.

Has any one encountered this?

One thing I noticed:
Select count(*) from dba_objects where object_name='PS_JRNL_LN' returns 13 rows
select count(*) from sys.obj$ where name='PS_JRNL_LN' returns 14 rows.

Additional row in obj$ table has type#=10 which is "NON_EXISTENT" according to comment in DBA_OBJECTS view text.

Any help is greatly appreciated.

Thank you
Mayen Shah

Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.' 

Received on Thu Sep 11 2008 - 11:15:33 CDT

Original text of this message