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
Received on Thu Jul 07 2005 - 14:54:09 CDT
