From oracle-l-bounce@freelists.org Fri Jul 8 00:58:40 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j685wel7001996 for ; Fri, 8 Jul 2005 00:58:40 -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 j685v9IP001630 for ; Fri, 8 Jul 2005 00:57:09 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A17B31CAD39; Fri, 8 Jul 2005 00:57:02 -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 15612-04; Fri, 8 Jul 2005 00:57:02 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1DA5A1CACFA; Fri, 8 Jul 2005 00:57:02 -0500 (EST) Message-ID: <20050708055515.14509.qmail@gem-wbe06.prod.mesa1.secureserver.net> Date: Thu, 7 Jul 2005 22:55:15 -0700 From: shirish@microexcel.com Subject: RE: compile dba_data_files To: jkstill@gmail.com Cc: premjhere@gmail.com, oracle-l MIME-Version: 1.0 Content-Type: TEXT/html; CHARSET=US-ASCII X-archive-position: 22225 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_50_60, HTML_MESSAGE,HTML_MIME_NO_HTML_TAG,MIME_HTML_ONLY,NO_REAL_NAME autolearn=no version=2.63
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
 
 


 

-------- Original Message --------
Subject: Re: compile dba_data_files
From: Jared Still <jkstill@gmail.com>
Date: Thu, July 07, 2005 7:44 pm
To: "shirish@microexcel.com" <shirish@microexcel.com>
Cc: premjhere@gmail.com, oracle-l <oracle-l@freelists.org>

On 7/7/05, shirish@microexcel.com <shirish@microexcel.com> wrote:
I just took a 10046 for
 
alter view dba_data_files compile
 The sql executed in this case are
 ...
so it does reference obj$ while doing a recompile

Nice work, I was obviously too lazy to trace it. :)

In Prem's case obj$ has a issue so he should FIRST CHECK dba_objects

What is it you think will be found in DBA_OBJECTS?

It appears that the update of obj$ is causing the error with one of the following unique indexes

 
TABLE                INDEXNAME                      UNIQUENES COLUMNNAME
-------------------- ------------------------------ --------- -----------------------------------
OBJ$                 I_OBJ1                         UNIQUE    OBJ#
 
                     I_OBJ2                         UNIQUE    OWNER#
                                                    UNIQUE    NAME
                                                    UNIQUE    NAMESPACE
                                                    UNIQUE    REMOTEOWNER
                                                    UNIQUE    LINKNAME
                                                    UNIQUE    SUBNAME
   
 
7 rows selected.

A level 4 trace on the 'alter view dba_data_files compile' operation should reveal the offending values.

I would not know how to fix it however.
In any case, this appears to require opening a TAR.

Inbetween , in my view, only analyze of data dict is supported from 9i onwards but further i have seen no documents which say OR does not say a way in which dict views can be recompiled.


Can you explain what you think dictionary statistics have to do with this?

Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

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