oracle object_id和data_object_id的区别

Oracle的数据字典表dba_objects包含了两个字段，object_id, data_object_id，官方文档上的解释是：

object_id: Dictionary object number of the object.
Data_object_id: Dictionary object number of the segment that contains the object.

直译一下是：

object_id：对象的数据字典标示。
Data_object_id：包含对象的段的数据字典标示。

直译下来还是很难理解，不过下面就详细讲解他们的区别:

　　首先，区别一下段（segment）和数据字典对象（dictionary object）的概念，段（segment）是指实实在在的分配了一个或者多个区（extents）来存储数据。而数据字典对象（dictionary object）有可能有存储区域，也有可能没有。比如sequence，package，type这些对象并没有存储空间，所以并不存在段与之相关联。所以这些对象的data_object_id都是空值。

　　

XPCHILD/XPCHILD@ORCL>select object_id, data_object_id from dba_objects where object_name='SEQ_TEST'; 
OBJECT_ID     DATA_OBJECT_ID
---------- --------------
36385

下面看一个实际的例子，就能够很好的理解这两个概念了:

1.　　move 操作：

XPCHILD/XPCHILD@ORCL>select object_id, data_object_id from dba_objects where object_name='TEST1';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     36386          36386

1 row selected.
XPCHILD/XPCHILD@ORCL>alter table test1 move;

Table altered.
XPCHILD/XPCHILD@ORCL>select object_id, data_object_id from dba_objects where object_name='TEST1';

 OBJECT_ID DATA_OBJECT_ID
---------- --------------
     36386          36387

可以看到，test1在创建的时候，object_id, data_object_id都是36386.这是因为在创建的时候数据字典分配的机制是相同的。使用move操作，而本身move操作只是重新分配了空间来重组原来的数据，所以对象本身没有发生变化，而是重新分配了段来存储数据。

2.　　partition分区表

　　

XPCHILD/XPCHILD@ORCL>CREATE TABLE test2
  2  (id number,  status char(1)) 
PARTITION BY list(status)  (PARTITION p_t VALUES ('t'), PARTITION p_f VALUES ('f'));

Table created.

XPCHILD/XPCHILD@ORCL>select object_name, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID from dba_objects where object_name='TEST2';

OBJECT_NAM SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID
---------- ------------------------------ ---------- --------------
TEST2      P_F                                 36390          36390
TEST2      P_T                                 36389          36389
TEST2                                          36388

3 rows selected

这个地方可以看出，test2对象只有object_id，而真正只有分区才会有data_object_id, 因为每一个分区分配了一个段。

接着再创建一个普通表：

　　

XPCHILD/XPCHILD@ORCL>CREATE TABLE test3
   ( id number,
    status char(1)
   );

XPCHILD/XPCHILD@ORCL>select object_name, object_id, data_object_id from dba_objects where object_name='TEST3';

OBJECT_NAM  OBJECT_ID DATA_OBJECT_ID
---------- ---------- --------------
TEST3           36391          36391

然后进行分区交换：

XPCHILD/XPCHILD@ORCL>alter table test2 exchange partition p_t with table test3 including indexes;

Table altered.

最后再看一下相关的object_id和data_object_id：

XPCHILD/XPCHILD@ORCL>select object_name, SUBOBJECT_NAME, object_id, data_object_id from dba_objects where object_name in ('TEST2','TEST3');

OBJECT_NAM SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID
---------- ------------------------------ ---------- --------------
TEST2      P_F                                 36390          36390
TEST2      P_T                                 36389          36391
TEST2                                          36388
TEST3                                          36391          36389

所以这个地方显而易见，在交换过后，对象的object_id都不会发生变变化，因为exchange操作并不会迁移数据，而仅仅是更改了对象的数据存储即段得指向，也就是更改了对象的指针，这个指针指向的是数据存储区域，即所谓的段。

3.   最后再看一下rowid

　　

rowid是指数据块中行的物理地址。看一下rowid的组成：
XPCHILD/XPCHILD@ORCL>select rowid ,
  2  substr(rowid,1,6) "OBJECT",
  3  substr(rowid,7,3) "FILE",
  4  substr(rowid,10,6) "BLOCK",
  5  substr(rowid,16,3) "ROW",
  6  dbms_rowid.rowid_object(rowid) object_id,
  7  id
  8  from test3 t;

ROWID              OBJECT       FILE   BLOCK        ROW     OBJECT_ID         ID
------------------ ------------ ------ ------------ ------ ---------- ----------
AAAI4lAAMAAAhssAAA AAAI4l       AAM    AAAhss       AAA         36389          1

1 row selected.

XPCHILD/XPCHILD@ORCL>alter table test3 move;

Table altered.


XPCHILD/XPCHILD@ORCL>select rowid ,
  2  substr(rowid,1,6) "OBJECT",
  3  substr(rowid,7,3) "FILE",
  4  substr(rowid,10,6) "BLOCK",
  5  substr(rowid,16,3) "ROW",
  6  dbms_rowid.rowid_object(rowid) object_id,
  7  id
  8  from test3 t;

ROWID              OBJECT       FILE   BLOCK        ROW     OBJECT_ID         ID
------------------ ------------ ------ ------------ ------ ---------- ----------
AAAI4oAAMAAAhtMAAA AAAI4o       AAM    AAAhtM       AAA         36392          1

1 row selected.

　　可以看到，rowid表示的是数据的在块中的地址，所以在rowid的组成中，object_id是所谓的段的数据字典id，即data_object_id，如上所示，对表进行move操作，object_id发生了变化，即验证了这个猜想。

转载于:https://www.cnblogs.com/xpchild/p/3694957.html