object_id and data_object_id 区别
==========================
其实object_id和data_object_id同样是表示数据库对象的一个唯一标志,但是object_id表示的是逻辑id,data_object_id表示的是物理id。
如果一些object没有物理属性的话那它就不存在data_object_id,例如procedure,function,package,data type,db link,mv定义,view定义,临时表,分区表定义等等这些object都是没有对应着某个segment,因此它们的data_object_id都为空。
当表刚创建的时候它的object_id和data_object_id都是相等的,但是如果表经过move或truncate后那么data_object_id将会有变化。
一个表的data object number大于object number,说明在表上曾经发生过truncate或者move操作。
对于truncate操作而言,truncate后其data object number就是在原先的data object number上加1,如果表上有索引就是加2。
-------------------------------------------------------------------------------------------------------------------------------------------
OBJECT_ID NUMBER | NOT NULL Dictionary object number of the object
DATA_OBJECT_ID NUMBER Dictionary object number of the segment that contains the object
Note: OBJECT_ID and DATA_OBJECT_ID display data dictionary metadata. Do not confuse these numbers with the unique 16-byte object identifier (object ID) that Oracle Database assigns to row objects in object tables in the system.
--------------------------------------------------------------------------------------------------------------------------------------------
注意dbms_rowid包中的object_id是指segment object id即dba_objects.data_object_id
下面做一个测试:
SQL> create table test1 as select * from emp ;
Table created.
SQL>
SQL> select object_id,data_object_id from dba_objects where object_name='TEST1' ;
OBJECT_ID DATA_OBJECT_ID
---------- --------------
52939 52939
/新建的表object_id和data_object_id是一样的
SQL> truncate table test1 ;
Table truncated.
SQL>
SQL> select object_id,data_object_id from dba_objects where object_name='TEST1'
OBJECT_ID DATA_OBJECT_ID
---------- --------------
52939 52940
/ truncate table 后object_id不变,data_object_id加1
SQL>
SQL> insert into test1 select * from emp ;
14 rows created.
SQL>
SQL> create index ind_test1 on test1(empno) ;
Index created.
SQL>
SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name in ('TEST1','IND_TEST1') ;
OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
-------------------- ------------------- ---------- --------------
IND_TEST1 INDEX 52941 52941
TEST1 TABLE 52939 52940
SQL>
/新建的索引对象object_id和data_object_id是一样的
SQL> alter table test1 move ;
Table altered.
SQL>
SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name in ('TEST1','IND_TEST1') ;
OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
-------------------- ------------------- ---------- --------------
IND_TEST1 INDEX 52941 52941
TEST1 TABLE 52939 52942
///表上有索引再做move table 操作,表的data_object_id加2
SQL>
SQL> truncate table test1 ;
Table truncated.
SQL>
SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name in ('TEST1','IND_TEST1') ;
OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
-------------------- ------------------- ---------- --------------
IND_TEST1 INDEX 52941 52943
TEST1 TABLE 52939 52944
表上有索引后再truncate table ,表和索引的data_object_id加2
SQL>