深入解析：DBA_OBJECTS中的OBJECT_ID与DATA_OBJECT_ID的区别

最新推荐文章于 2021-04-08 02:30:25 发布

数据和云

最新推荐文章于 2021-04-08 02:30:25 发布

阅读量1.1k

点赞数

本文链接：https://blog.csdn.net/enmotech/article/details/105648419

版权

Oracle的DBA_OBJECTS视图中的object_id和data_object_id虽然都代表数据库对象的标识，但object_id是逻辑ID，data_object_id是物理ID。表、索引等有物理存储的对象有data_object_id，而如过程、视图等则没有。对表的TRUNCATE、MOVE或索引的REBUILD操作会影响data_object_id，而object_id保持不变。data_object_id的变化与seg$表中的HWMINCR值关联，反映了物理段的变化。

摘要由CSDN通过智能技术生成

墨墨导读：在Oracle dba_objects视图中存在object_id和data_oubject两个列，已经存在object_id列了为什么还会有一个data_object_id列呢，这两个列有什么区别？通过本文了解一下。

object_id和data_object_id同样是表示数据库对象的一个唯一标志，但是object_id表示的是逻辑id，data_object_id表示的是物理id。只有表,索引,undo这些有实际物理存储位置的对象才有data_object_id, 而一些没有物理属性的object 不存在data_object_id，例如procedure,function,package,data type,db link,mv定义,view定义,临时表,分区表定义等等这些object都是没有对应着某个segment，因此它们的data_object_id都为空。

大多数情况下两者是相等的。但对object 进行truncate,move, rebuild 等操作后，data_object_id就会发生改变，而object_id不会改变。

create table tab1 as select * from emp;
SQL>  create table tab1 as select * from emp;


Table created.


create index ind_tab1_pk on tab1(empno);
SQL> create index ind_tab1_pk on tab1(empno);


Index created.


select object_name,object_type,subobject_name,object_id,data_object_id from dba_objects where object_name in ('TAB1','IND_TAB1_PK');


SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name in ('TAB1','IND_TAB1_PK');


OBJECT_NAME                          object_type        OBJECT_ID DATA_OBJECT_ID
------------------------------ -----------------------  ------------ ---------------
IND_TAB1_PK                             INDEX               74827          74827
TAB1                                    TABLE               74826          74826


SQL> create view v_emp as select * from emp;


View created.


select object_name,object_type,object_id,data_object_id from dba_objects where object_name='V_EMP';
SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name='V_EMP';


OBJECT_NAME                    OBJECT_TYPE          OBJECT_ID DATA_OBJECT_ID
------------------------------ ------------------- ---------- --------------
V_EMP                          VIEW                     74832

通过上面查询上面创建的表、索引以及视图可以看到，表和索引这种存在物理存储的是数据库对象是有data_object_id的，而像视图这样的不存在物理存储的数据库对象是没有data_object_id的。

下面对存在data_object_id的数据库对象进测试查看data_object_id的变化与什么有关。

–删除表中的数据查看object_id和data_object_id的变化

SQL> delete from tab1 where empno=7788;


1 row deleted.


SQL> commit;


Commit complete.


SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name in ('TAB1','IND_TAB1_PK');


OBJECT_NAME                          object_type        OBJECT_ID DATA_OBJECT_ID
------------------------------ -----------------------  ------------ ---------------
IND_TAB1_PK                             INDEX               74827          74827
TAB1                                    TABLE               74826          74826

–对表进行TRUNCATE查看object_id和data_object_id的变化

truncate table tab1;
SQL> truncate table tab1;


Table truncated.


SQL> select object_name,object_type,object_id,data_object_id from dba_objects where object_name in ('TAB1','IND_TAB1_PK');


OBJECT_NAME                    object_type                  OBJECT_ID DATA_OBJECT_ID
--------------------------