From oracle-l-bounce@freelists.org Thu Jul 7 14:54:09 2005 Return-Path: Received: from air891.startdedicated.com (root@localhost) by orafaq.com (8.12.10/8.12.10) with ESMTP id j67Js91B015445 for ; Thu, 7 Jul 2005 14:54:09 -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 j67JrpIP015397 for ; Thu, 7 Jul 2005 14:53:55 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4592D1CA630; Thu, 7 Jul 2005 14:53:42 -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 19095-03; Thu, 7 Jul 2005 14:53:42 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A76201CAC81; Thu, 7 Jul 2005 14:53:41 -0500 (EST) Message-ID: <20050707195146.32299.qmail@webmail09.mesa1.secureserver.net> Date: Thu, 7 Jul 2005 12:51:46 -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: 22210 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,NORMAL_HTTP_TO_IP,NO_REAL_NAME autolearn=no version=2.63
I just took a 10046 for
 
alter view dba_data_files compile
 
The sql executed in this case are
 
select obj# from obj$ o,user$ u   where o.name=:1 and o.owner#=u.user# and u.name=:2 and o.type#=4/*VIEW*/   and LINKNAME is NULL
select property,v.textlength,   decode(bitand(property,1),0,0,t.oidtextlength),   decode(bitand(property,1),0,0,t.typetextlength),   decode(bitand(property,134217728),0,0,t.undertextlength)   from view$ v, typed_view$ t where v.obj#=:1 and v.obj#=t.obj#(+)
select lengthb(name) from col$ where obj#=:1
select name,lengthb(name) from col$ where obj#=:1 order by intcol#
select text from view$ where obj#=:1
 
 
so it does reference obj$ while doing a recompile
 
In Prem's case obj$ has a issue so he should FIRST CHECK dba_objects
 
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.
 
obviously, running catalog/catproc or utlrp ( if it shows as invalid) is the more obvious way.
 
Coming to Prem's case
 
he should check the dba_objects to start with the analysis
as in case his obj$ has some issues , he should definitely get alerted
--
Shirish
 
 
 
 
 


 

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

Perhaps I am missing something obvious here, but what do stats
on dictionary objects have to do with recompiling DBA_DATA_FILES?

Also, the error that Prem is seeing is rather strange, as the view DBA_DATA_FILES
does not include any reference to obj$, or any other views that do.

FROM clauses for dba_data_files:

8i:
      from sys.file$ f, sys.ts$ ts, sys.v$dbfile v
      from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts

10gR1:
      from sys.file$ f, sys.ts$ ts, sys.v$dbfile v
      from sys.v$dbfile v, sys.file$ f, sys.x$ktfbhc hc, sys.ts$ ts


Jared

On 7/7/05, shirish@microexcel.com <shirish@microexcel.com> wrote:
Oracle does supports collecting stats on dict objects
 
but in your case it looks like that you have some issues with obj$
 
does you dba_objects works fine ?
 
better is to do
 
1)analyze the obj$
2) if it fails then take a 10046 or
3)set a errorstack for 4043
 
in case obj$ is corrupted then it you need to patch the dict or restore recover from a good known backup .....
 
Regards
Shirish


 

-------- Original Message --------
Subject: compile dba_data_files
From: Prem <premjhere@gmail.com>
Date: Thu, July 07, 2005 7:38 am
To: oracle-l <oracle-l@freelists.org>

Hi all ,

it's on 9.2.0.6.

SQL > desc dba_data_files
ERROR:
ORA-04043: object dba_data_files does not exist

so one of my friend did a

SQL> alter view dba_data_files compile ;

though it erred out saying "unique key violation in OBJ$" , the next time

SQL > desc dba_data_files

worked .

my question is , can we compile data dictionary views like this ?
OR it is to be avoided strictly ? does oracle support this ?

Please advice.

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



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

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