ITPub博客

首页 > Linux操作系统 > Linux操作系统 > OBJECT_ID与DATA_OBJECT_ID的不同

OBJECT_ID与DATA_OBJECT_ID的不同

原创 Linux操作系统 作者:jason_wang2002 时间:2009-04-14 19:27:39 0 删除 编辑

在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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2009-03-05

  • 博文量
    35
  • 访问量
    40750