在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/