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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值