It goes like this: USER_A creates DATA_TABLE. USER_A creates a public synonym DATA_TABLE that points to = USER_A.DATA_TABLE. USER_B creates a function, SOME_FUNC that references DATA_TABLE, by = virtue of public synonym, it references USER_A.DATA_TABLE. Now, what if USER_B were to create a table called DATA_TABLE? Oracle = needs to invalidate SOME_FUNC, so that it's recompiled on next execution. Oracle uses NON-EXISTANT objects to = do this. When USER_B initially created SOME_FUNC, and did NOT have a table DATA_TABLE in his schmea, Oracle = created a NON-EXISTANT object in his schema. That way, the function SOME_FUNC also maintains a dependency on = the NON-EXISTANT USER_B.DATA_TABLE, and when USER_B creates DATA_TABLE, the SOME_FUNC function is = invalidated, which forces a reconpile on next execution, which finds the new DATA_TABLE in USER_B's schema. Hope that's clear, -Mark PS It's worth noting that when dependencies exist on transient objects = in the library cache, Oracle does the same thing inthe library cache, = though the corresponding permanent objects will not appear in OBJ$. -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Jacques Kilchoer Sent: Wednesday, June 23, 2004 2:56 PM To: oracle-l@xxxxxxxxxxxxx Subject: RE: what is obj$.type#=3D10?=20 OK, but why was the non-existent object created in the first place? Is = it because when I drop a public synonym Oracle thinks that there might = be dependencies on it? -----Original Message----- Bobak, Mark Because unused NON-EXISTANT objects are cleaned up by SMON every 12 = hours. -----Original Message----- Jacques Kilchoer -----Original Message----- Jonathan Lewis -- I agree. Specifically, you get lots of these when -- you create a permanent object that depends on -- public synonym. At some point, you may create -- a local object that "conceals" the public synonym, -- at which point the thing that depends on the synonym -- has to become invalid and depend on the local object. -- To allow this to happen, you NEED a 'non-existent' -- object of the same name to exist, so that the permanent -- object can depend on its non-existence, and notice when -- it ceases to be non-existent. Once again I learn something from the list. (I wrote a sample script = below to show what Mr. Lewis is talking about.) I have a question = though. In an Oracle 9.2 database, I create public synonym X for = some_table. Then I drop public synonym X. No one has ever used public = synonym X, there are no dependencies on it, so why does a row remain in = SYS.OBJ$ for X with type# 10? That row will remain until I restart the = database. -- showing how an procedure depending on a public synonym will cause the -- the creation of a "non-existent" object with the same name as the -- public synonym, in the procedure's schema SQL> select 2 b.name as owner, a.name as object_name, a.type#, 3 decode (a.type#, 2, 'TABLE', 5, 'SYNONYM', 7, 'PROCEDURE') as = object_type 4 from 5 sys.obj$ a, sys.user$ b 6 where 7 a.name in ('COUNTRY', 'COUNTRY_PROC') 8 and a.owner# =3D b.user# ; aucune ligne s=E9lectionn=E9e SQL> create table beta.country 2 (country_code varchar2 (10), country_name varchar2 (40)) ; Table cr=E9=E9e. SQL> create public synonym country for beta.country ; Synonyme cr=E9=E9. SQL> grant select on beta.country to alpha ; Autorisation de privil=E8ges (GRANT) accept=E9e. SQL> create procedure alpha.country_proc 2 as 3 x country.country_code%type ; 4 begin 5 null ; 6 end ; 7 / Proc=E9dure cr=E9=E9e. SQL> select 2 b.name as owner, a.name as object_name, a.type#, 3 decode (a.type#, 2, 'TABLE', 5, 'SYNONYM', 7, 'PROCEDURE') as = object_type 4 from 5 sys.obj$ a, sys.user$ b 6 where 7 a.name in ('COUNTRY', 'COUNTRY_PROC') 8 and a.owner# =3D b.user# ; OWNER OBJECT_NAME TYPE# = OBJECT_TY ------------------------------ ------------------------------ --------- = --------- PUBLIC COUNTRY 5 = SYNONYM ALPHA COUNTRY 10 ALPHA COUNTRY_PROC 7 = PROCEDURE BETA COUNTRY 2 = TABLE SQL> create table alpha.country 2 (country_code varchar2 (15), country_name varchar2 (40)) ; Table cr=E9=E9e. SQL> select 2 b.name as owner, a.name as object_name, a.type#, 3 decode (a.type#, 2, 'TABLE', 5, 'SYNONYM', 7, 'PROCEDURE') as = object_type 4 from 5 sys.obj$ a, sys.user$ b 6 where 7 a.name in ('COUNTRY', 'COUNTRY_PROC') 8 and a.owner# =3D b.user# ; OWNER OBJECT_NAME TYPE# = OBJECT_TY ------------------------------ ------------------------------ --------- = --------- PUBLIC COUNTRY 5 = SYNONYM ALPHA COUNTRY 2 = TABLE ALPHA COUNTRY_PROC 7 = PROCEDURE BETA COUNTRY 2 = TABLE ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------