Re: compile dba_data_files

  • From: Jared Still <jkstill@xxxxxxxxx>
  • To: "shirish@xxxxxxxxxxxxxx" <shirish@xxxxxxxxxxxxxx>
  • Date: Fri, 8 Jul 2005 08:02:33 -0700

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@xxxxxxxxxxxxxx <shirish@xxxxxxxxxxxxxx> 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 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

Other related posts: