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

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 23 Jun 2004 11:56:28 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262109F87921@irvmbxw02>

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# = b.user# ; aucune ligne sélectionnée

SQL> create table beta.country
  2 (country_code varchar2 (10), country_name varchar2 (40)) ; Table créée.

SQL> create public synonym country for beta.country ; Synonyme créé.

SQL> grant select on beta.country to alpha ; Autorisation de privilèges (GRANT) acceptée.

SQL> create procedure alpha.country_proc   2 as
  3 x country.country_code%type ;
  4 begin
  5 null ;
  6 end ;
  7 /
Procédure créée.

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# = 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éée.

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

---

Received on Wed Jun 23 2004 - 13:53:18 CDT