RE: DBMS_STATS and ORA-01422

I found the following note with a workaround to try:  

Note:338845.1      

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mayen.Shah_at_lazard.com
Sent: Thursday, September 11, 2008 10:54 AM To: andrew.kerber_at_gmail.com
Cc: oracle-l; oracle-l-bounce_at_freelists.org 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.

Thanks
Mayen Shah

"Andrew Kerber" <andrew.kerber_at_gmail.com>
Sent by: oracle-l-bounce_at_freelists.org

Sep 11 2008 10:49 AM

Please respond to
andrew.kerber_at_gmail.com

To

Mayen Shah/ITS/Lazard_at_Lazard NYC

cc

"oracle-l" <oracle-l_at_freelists.org>, oracle-l-bounce_at_freelists.org

Subject

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, <Mayen.Shah_at_lazard.com> wrote:

Hi Gurus,

Recently I migrated one database from 9.2.0.8 <http://9.2.0.8/> to 10.2.0.3 <http://10.2.0.3/> 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);
BEGIN DBMS_STATS.GATHER_TABLE_STATS
(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 9.2.0.8 <http://9.2.0.8/> 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
while
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.' 


--
http://www.freelists.org/webpage/oracle-l