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

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Wed, 23 Jun 2004 14:46:31 -0400
Message-ID: <DE8A21F8F1D0254EA4A9214D52AB2FEDAD5C6E@exchsen0a1ma>

You Said:
"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."

I can guess. Oracle probably figured it wasn't worth cleaning it up - kind of a wasted effort. A new object would reuse the value. A restart of the database would clear the object. Why waste the time and effort to clean it up?

Tom Mercadante
Oracle Certified Professional

-----Original Message-----
From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_quest.com] Sent: Wednesday, June 23, 2004 2:40 PM
To: oracle-l_at_freelists.org
Subject: RE: what is obj$.type#=10?

-----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 - 13:44:07 CDT