Re: Thanks!! What are the type#'s in obj$ ?

Hi!

> The example given below is tootaly wrong in this concern.
> Type 10 is used for nagetivec dependancy tracking of objects.
> This is use in reference of public on object of one schema and used by
other
> schema and oracle internally assume that second schema have the referenced
> object and have type#10 ( in reality it is not exist).

Not correct. Negative dep. tracking is only one of the cases why we see type 10 objects.

And if you check my example more closely, I didn't use public synonyms. Not-existing (type 10) objects are needed only in case of public synonyms, to be sure that dependent objects are invalidated if an object with same name is created in current schema.

But the second case is just for keeping obj$ table and indexes sane in environments where objects are very frequently dropped and recreated. When dropping sequences, synonyms, procedures-functions, etc.. you actually see that the objects remain in obj$ table with type# 10. No public synonyms or dependencies involved at all! When you create a new same type object with different name, a new entry is added to obj$, but when you recreate the same type object with old, original name, the type 10 entry in obj$ is actually reused (updated back to correct type). There is no index on type# column, thus it's cheaper just to update the type# column instead of delete from table and maintain corresponding index entries (possibly following an insert shortly). The type 10 entries (actually only those which don't have anyone depending on them) are deleted by SMON during next startup (maybe after some time interval too, haven't checked that far).

So, there are more cases when type 10 entries might exist in obj$ and my example is correct.

Btw, you should proofread your posts, it isn't easy to read what words like 'nagetivec' might mean ;)

Tanel.

>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]>
> Sent: Tuesday, June 24, 2003 9:09 AM
>
>
> > Wow. Terrific information.
> > Thanks Tanel, Jacques, Richard, and Jared!!!
> >
> > The database and I are having a bit of a disagreement
> > about whether this object really exists.
> >
> > I guess the database wins.
> >
> > Thanks again.
> >
> > Barb
> >
> >
> > --- Tanel Poder <[EMAIL PROTECTED]> wrote:
> > > Hi!
> > >
> > > Check
> > > http://www.jlcomp.demon.co.uk/faq/non_exist.html for
> > > explanation.
> > >
> > > Also, I'll include a little sample, how they might
> > > occur.
> > >
> > >
> > > Cheers,
> > > Tanel.
> > >
> > > SQL> create table a (b number);
> > >
> > > Table created.
> > >
> > > SQL> create synonym s for a;
> > >
> > > Synonym created.
> > >
> > > SQL> select name, type# from obj$ where name = 'A';
> > >
> > > NAME TYPE#
> > > ------------------------------ ----------
> > > A 2
> > > A 2
> > >
> > > SQL> select name, type# from obj$ where name = 'S';
> > >
> > > NAME TYPE#
> > > ------------------------------ ----------
> > > S 5
> > >
> > > SQL> drop synonym s;
> > >
> > > Synonym dropped.
> > >
> > > SQL> select name, type# from obj$ where name = 'S';
> > >
> > > NAME TYPE#
> > > ------------------------------ ----------
> > > S 10
> > >
> > > SQL> startup force
> > > ORACLE instance started.
> > >
> > > Total System Global Area 135338868 bytes
> > > Fixed Size 453492 bytes
> > > Variable Size 109051904 bytes
> > > Database Buffers 25165824 bytes
> > > Redo Buffers 667648 bytes
> > > Database mounted.
> > > Database opened.
> > > SQL> select name, type# from obj$ where name = 'S';
> > >
> > > no rows selected
> > >
> > > SQL>
> > >
> > >

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tanel Poder
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).