RE: what is obj$.type#=10?
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 22 Jun 2004 11:47:37 -0700
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@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:
