From oracle-l-bounce@freelists.org Fri Jul 8 12:16:10 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j68HGAKU015399 for ; Fri, 8 Jul 2005 12:16:10 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air891.startdedicated.com (8.12.10/8.12.10) with ESMTP id j68HG8IP015389 for ; Fri, 8 Jul 2005 12:16:08 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 539011C98C7; Fri, 8 Jul 2005 12:16:00 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 24984-02; Fri, 8 Jul 2005 12:16:00 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CCCD91C926D; Fri, 8 Jul 2005 12:15:59 -0500 (EST) Message-ID: <20050708171412.28630.qmail@gem-wbe03.mesa1.secureserver.net> Date: Fri, 8 Jul 2005 10:14:12 -0700 From: shirish@microexcel.com Subject: RE: compile dba_data_files To: Jared Still Cc: premjhere@gmail.com, oracle-l MIME-Version: 1.0 Content-Type: TEXT/html; CHARSET=US-ASCII X-archive-position: 22249 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: shirish@microexcel.com Precedence: normal Reply-To: shirish@microexcel.com X-list: oracle-l X-Virus-Scanned: by amavisd-new-20030616-p9 (Debian) at avenirtech.net X-mailscan-MailScanner-Information: Please contact the ISP for more information X-mailscan-MailScanner: Found to be clean X-MailScanner-From: oracle-l-bounce@freelists.org X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.63 (2004-01-11) on air891.startdedicated.com X-Spam-Status: No, hits=-3.1 required=5.0 tests=AWL,BAYES_00,HTML_MESSAGE, HTML_MIME_NO_HTML_TAG,MIME_HTML_ONLY,NO_REAL_NAME autolearn=no version=2.63
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