From jkstill@cybcon.com Tue, 24 Jun 2003 08:01:46 -0700 From: Jared Still Date: Tue, 24 Jun 2003 08:01:46 -0700 Subject: Re: Thanks!! What are the type#'s in obj$ ? Message-ID: MIME-Version: 1.0 Content-Type: text/plain 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).