RE: what is obj$.type#=10?
- From: "Mercadante, Thomas F" <thomas.mercadante@xxxxxxxxxxxxxxxxx>
- To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Jun 2004 14:46:31 -0400
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@xxxxxxxxx]
Sent: Wednesday, June 23, 2004 2:40 PM
To: oracle-l@xxxxxxxxxxxxx
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@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:
