RE: DBMS_STATS and ORA-01422
Date: Thu, 11 Sep 2008 12:15:33 -0400
Message-ID: <OF541B1AB5.8EDBCCE4-ON852574C1.00592476-852574C1.00595047@lazard.com>
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.
Thanks
Mayen Shah
"Mercadante, Thomas F (LABOR)" <Thomas.Mercadante_at_labor.state.ny.us>
Sent by: oracle-l-bounce_at_freelists.org
Sep 11 2008 10:56 AM
Please respond to
Thomas.Mercadante_at_labor.state.ny.us
To
Mayen Shah/ITS/Lazard_at_Lazard NYC, andrew.kerber_at_gmail.com
cc
"oracle-l" <oracle-l_at_freelists.org>, oracle-l-bounce_at_freelists.org
Subject
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 to 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 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-lReceived on Thu Sep 11 2008 - 11:15:33 CDT