RE: what is obj$.type#=10?

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Wed, 23 Jun 2004 15:13:14 -0400
Message-ID: <4C9B6FDA0B06FE4DAF5918BBF0AD82CFECFD1F@bosmail00.bos.il.pqe>

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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Jacques Kilchoer Sent: Wednesday, June 23, 2004 2:56 PM
To: oracle-l_at_freelists.org
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_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/

FAQ is at http://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_at_freelists.org

put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Received on Wed Jun 23 2004 - 14:10:08 CDT