RE: DBMS_STATS and ORA-01422

  • From: "Mercadante, Thomas F (LABOR)" <Thomas.Mercadante@xxxxxxxxxxxxxxxxx>
  • To: <Mayen.Shah@xxxxxxxxxx>, <andrew.kerber@xxxxxxxxx>
  • Date: Thu, 11 Sep 2008 10:56:43 -0400

I found the following note with a workaround to try:

 

Note:338845.1

 

 

 

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of
Mayen.Shah@xxxxxxxxxx
Sent: Thursday, September 11, 2008 10:54 AM
To: andrew.kerber@xxxxxxxxx
Cc: oracle-l; oracle-l-bounce@xxxxxxxxxxxxx
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@xxxxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx 

Sep 11 2008 10:49 AM 

Please respond to
andrew.kerber@xxxxxxxxx

To

Mayen Shah/ITS/Lazard@Lazard NYC 

cc

"oracle-l" <oracle-l@xxxxxxxxxxxxx>, oracle-l-bounce@xxxxxxxxxxxxx 

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@xxxxxxxxxx> 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.' 

Other related posts: