OBJECT_ID与DATA_OBJECT_ID的不同

在dba_objects中有两个字段object_id与data_object_id，这两个字段有什么区别呢？

object_id是数据库给每个对象分配的id号，无论这个对象是否占用空间（segment）都会有，data_object_id只有占用空间（segment）的对象才会有
object_id与data_object_id一般是相同的，有时也会不同

下面以实验的方式来验证第一点

SQL> create table t( a int,b int);

Table created.

SQL>  create view v_t as select * from t;

View created.

SQL> create table p_t(a int,b int)
  2  partition by range(a)
  3  (
  4  partition pr1 values less than (10),
  5  partition pr2 values less than (20)
  6  );

Table created.

SQL> select object_name,subobject_name,object_id,data_object_id from user_objects 
  2  where object_name in ('T','V_T','P_T');

OBJECT_NAME                              SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID
---------------------------------------- ------------------------------ ---------- --------------
P_T                                      PR1                                 61269          61269
P_T                                      PR2                                 61270          61270
P_T                                                                          61268
T                                                                            61266          61266
V_T                                                                          61267

从上面的结果来看，表T和两个分区PR1、PR2都有data_object_id，说明它们占用空间，而视图V_T和分区表T没有data_object_id，说明它们不占用空间，它们只不过是数据字典中的定义

那什么时候这两个字段不同呢？我知道有一种情况，就是对分区表进行exchange操作时会使它们不同。
下面以实验方式证明

SQL> select object_name,subobject_name,object_id,data_object_id from user_objects 
  2  where object_name in ('P_T','T1');

OBJECT_NAME                              SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID
---------------------------------------- ------------------------------ ---------- --------------
P_T                                      PR1                                 61269          61269
P_T                                      PR2                                 61270          61270
P_T                                                                          61268
T1                                                                           61271          61271

SQL> alter table p_t
  2  exchange partition pr1
  3  with table t1;

Table altered.

SQL> select object_name,subobject_name,object_id,data_object_id from user_objects 
  2  where object_name in ('P_T','T1');

OBJECT_NAME                              SUBOBJECT_NAME                  OBJECT_ID DATA_OBJECT_ID
---------------------------------------- ------------------------------ ---------- --------------
P_T                                      PR1                                 61269          61271
P_T                                      PR2                                 61270          61270
P_T                                                                          61268
T1                                                                           61271          61269

从上面的结果可以看到分区PR1和T1的object_id与data_object_id不一样
分区PR1的object_id与T1的data_object_id一样，T1的object_id与分区PR1的data_object_id一样，他们的物理段（segment）被交换了

alter table ... exchange命令主要用于分区表中的分区与非分区表进行交换，用于分区的老化处理

partition和cluster一般会两个字段不一样

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8745319/viewspace-589038/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8745319/viewspace-589038/