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

Interesting. I didn't realize that oracle would keep dropped objects as an unused type rather than delete them.

Thanks,

Jared

On Tuesday 24 June 2003 05:33, Tanel Poder wrote:
> 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: Jared Still
  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).