RE: compile dba_data

From: <shirish_at_microexcel.com>
Date: Fri, 8 Jul 2005 10:14:12 -0700
Message-ID: <20050708171412.28630.qmail@gem-wbe03.mesa1.secureserver.net>

Hmmmm seems valuable

Then

The question is why is violation occuring if all the integrity constarints are in place

In all the cases of dict corruption one or other constraint do not work and that leads to issues

but then we do need 10046

Prem can you upload 10046 of the compile statement

take 10046 as

alter session set events '10046 trace name context forever, level 12';

this will generate a trace file in udump

upload the file

Shirish

-------- Original Message --------
Subject: Re: compile dba_data_files
From: Jared Still <jkstill@gmail.com>
Date: Fri, July 08, 2005 11:02 am
To: "shirish@microexcel.com" <shirish@microexcel.com>
Cc: premjhere@gmail.com, oracle-l <oracle-l@freelists.org>

Ah, thanks, I see where you were going with it.

I don't however think there is a duplicate row in obj$.

There are 2 unique indexes on obj$ in 9i, so there
can't be duplicate rows.

The compile of the view is attempting to violate one
of the unique indexes by creating a row that would
be a duplicate.

That is why I suggested the level 4 trace to show the
bind variables when the update of obj$ is done.

Use those bind variable values to query sys.obj$ to
find the already existing row.

--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

On 7/7/05, shirish@microexcel.com <shirish@microexcel.com> wrote:

the best guess is that the obj$ has duplicate row for this view

dba_data_files

stats has practically nothing to do with this case .....it was just to say that oracle even supports analyze .....on sys objects (for stats)

thus he should do

sql>analyze table obj$ validate structure cascade

and

run this to find the duplicate row

select count(*) ,obj# from obj$ group by obj# having count(*) > 1 ;

or

select count(*) ,owner#, name, namespace,
remoteowner, linkname, subname from obj$ group by owner#, name, namespace,remoteowner, linkname, subname having count(*) > 1 ;

if he opens tar, this will be a perfect case as support guy will do an OWC and can just run a update on obj$ and do shutdown abort BUT this is something which oracle will never support if ct does on its own.......I agree it does require tar :-)

--Shirish

-- http://www.freelists.org/webpage/oracle-l Received on Fri Jul 08 2005 - 12:16:10 CDT

RE: compile dba_data_files