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

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 23 Jun 2004 15:13:14 -0400

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
-----------------------------------------------------------------

Other related posts: