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

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Tue, 22 Jun 2004 11:47:37 -0700
Message-ID: <B5C5F99D765BB744B54FFDF35F60262119FC2B@irvmbxw02>

I learned from this list (probably Jonathan Lewis, apologies if I am = crediting the wrong person) that Oracle tries to "reuse" object ids to = prevent them from increasing too rapidly. For example, many installation scripts say something like this: Drop object ... ;
Create object ... ;

If you ran that script many times then you would use up a new object id = for X every time you ran the script. Instead Oracle keeps the old object = id around in case it can be reused. I know for sure this happens with = dropped synonyms, as the example below shows:

SQL> define objname =3D "XYZZY"
SQL> create synonym &objname for some_table ; Synonyme cr=E9=E9.

SQL> select

  2     a.obj# as object_id,
  3     a.name as object_name,
  4     c.object_type,
  5     a.type#
  6   from
  7     sys.obj$ a, all_users b, user_objects c
  8   where
  9     a.name =3D '&objname'
 10     and a.owner# =3D b.user_id
 11     and b.username =3D user
 12     and a.name =3D c.object_name (+) ;
OBJECT_ID OBJECT_NAME                    OBJECT_TYPE            TYPE#
--------- ------------------------------ ------------------ ---------
    33624 XYZZY                          SYNONYM                    5    =


                                                          =20

SQL> drop synonym &objname ;
Synonyme supprim=E9.

SQL> -- object id still present in obj$
SQL> select

  2     a.obj# as object_id,
  3     a.name as object_name,
  4     c.object_type,
  5     a.type#
  6   from
  7     sys.obj$ a, all_users b, user_objects c
  8   where
  9     a.name =3D '&objname'
 10     and a.owner# =3D b.user_id
 11     and b.username =3D user
 12     and a.name =3D c.object_name (+) ;
OBJECT_ID OBJECT_NAME                    OBJECT_TYPE            TYPE#
--------- ------------------------------ ------------------ ---------
    33624 XYZZY                                                    10    =


                                                          =20

SQL> -- table reuses object id
SQL> create table &objname (n number) ;
Table cr=E9=E9e.

SQL> select

  2     a.obj# as object_id,
  3     a.name as object_name,
  4     c.object_type,
  5     a.type#
  6   from
  7     sys.obj$ a, all_users b, user_objects c
  8   where
  9     a.name =3D '&objname'
 10     and a.owner# =3D b.user_id
 11     and b.username =3D user
 12     and a.name =3D c.object_name (+) ;
OBJECT_ID OBJECT_NAME                    OBJECT_TYPE            TYPE#
--------- ------------------------------ ------------------ ---------
    33624 XYZZY                          TABLE                      2    =


                                                          =20

----------------------------------------------------------------

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 Tue Jun 22 2004 - 13:44:29 CDT