RE: compile dba_data

From: <shirish_at_microexcel.com>
Date: Thu, 7 Jul 2005 22:55:15 -0700
Message-ID: <20050708055515.14509.qmail@gem-wbe06.prod.mesa1.secureserver.net>

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 ;

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 Received on Fri Jul 08 2005 - 00:58:40 CDT

RE: compile dba_data_files