墨墨导读：在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
------------------------------ ---------------------------- ---------- --------------
IND_TAB1_PK INDEX 74827 74828
TAB1 TABLE 74826 74829
–对表进行MOVE查看object_id和data_object_id的变化
alter table tab1 move;
SQL> alter table tab1 move;
Table altered.
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 74828
TAB1 TABLE 74826 74830
–对索引进行REBUILD查看object_id和data_object_id的变化
alter index ind_tab1_pk rebuild;
SQL> alter index ind_tab1_pk rebuild;
Index altered.
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 74831
TAB1 TABLE 74826 74830
从上面的测试来看当在对表做了TRUNCATE时，表TAB1和表上的索引的data_object_id都发生了变化，在对表做move的时候只有表的data_object_id发生了变化，在对索引重建的时候只有索引的data_object_id发生了变化。在删除表中的数据的时候data_object_id却没有变化。原因是什么呢？
–查看视图DBA_OBJECTS的定义
SQL> select text from dba_views where view_name='DBA_OBJECTS';
TEXT
--------------------------------------------------------------------------------
select u.name, o.name, o.subname, o.obj#, o.dataobj#,
decode(o.type#, 0, 'NEXT OBJECT', 1, 'INDEX', 2, 'TABLE', 3, 'CLUSTER',
4, 'VIEW', 5, 'SYNONYM', 6, 'SEQUENCE',
7, 'PROCEDURE', 8, 'FUNCTION', 9, 'PACKAGE',
11, 'PACKAGE BODY', 12, 'TRIGGER',
13, 'TYPE', 14, 'TYPE BODY',
19, 'TABLE PARTITION', 20, 'INDEX PARTITION', 21, 'LOB',
22, 'LIBRARY', 23, 'DIRECTORY', 24, 'QUEUE',
28, 'JAVA SOURCE', 29, 'JAVA CLASS', 30, 'JAVA RESOURCE',
32, 'INDEXTYPE', 33, 'OPERATOR',
34, 'TABLE SUBPARTITION', 35, 'INDEX SUBPARTITION',
40, 'LOB PARTITION', 41, 'LOB SUBPARTITION',
42, NVL((SELECT 'REWRITE EQUIVALENCE'
FROM sum$ s
WHERE s.obj#=o.obj#
and bitand(s.xpflags, 8388608) = 8388608),
'MATERIALIZED VIEW'),
43, 'DIMENSION',
44, 'CONTEXT', 46, 'RULE SET', 47, 'RESOURCE PLAN',
48, 'CONSUMER GROUP',
51, 'SUBSCRIPTION', 52, 'LOCATION',
55, 'XML SCHEMA', 56, 'JAVA DATA',
57, 'EDITION', 59, 'RULE',
60, 'CAPTURE', 61, 'APPLY',
62, 'EVALUATION CONTEXT',
66, 'JOB', 67, 'PROGRAM', 68, 'JOB CLASS', 69, 'WINDOW',
72, 'SCHEDULER GROUP', 74, 'SCHEDULE', 79, 'CHAIN',
81, 'FILE GROUP', 82, 'MINING MODEL', 87, 'ASSEMBLY',
90, 'CREDENTIAL', 92, 'CUBE DIMENSION', 93, 'CUBE',
94, 'MEASURE FOLDER', 95, 'CUBE BUILD PROCESS',
100, 'FILE WATCHER', 101, 'DESTINATION',
'UNDEFINED'),
o.ctime, o.mtime,
to_char(o.stime, 'YYYY-MM-DD:HH24:MI:SS'),
decode(o.status, 0, 'N/A', 1, 'VALID', 'INVALID'),
decode(bitand(o.flags, 2), 0, 'N', 2, 'Y', 'N'),
decode(bitand(o.flags, 4), 0, 'N', 4, 'Y', 'N'),
decode(bitand(o.flags, 16), 0, 'N', 16, 'Y', 'N'),
o.namespace,
o.defining_edition
from sys."_CURRENT_EDITION_OBJ" o, sys.user$ u
where o.owner# = u.user#
and o.linkname is null
and (o.type# not in (1 /* INDEX - handled below */,
10 /* NON-EXISTENT */)
or
(o.type# = 1 and 1 = (select 1
from sys.ind$ i
where i.obj# = o.obj#
and i.type# in (1, 2, 3, 4, 6, 7, 9))))
and o.name != '_NEXT_OBJECT'
and o.name != '_default_auditing_options_'
and bitand(o.flags, 128) = 0
union all
select u.name, l.name, NULL, to_number(null), to_number(null),
'DATABASE LINK',
l.ctime, to_date(null), NULL, 'VALID','N','N', 'N', NULL, NULL
from sys.link$ l, sys.user$ u
where l.owner# = u.user#
SQL> desc dba_objects
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(30)
data_object_id在dba_objects视图中的顺序可以对应到视图定义中的o.dataobj#字段，该字段来自于视图_CURRENT_EDITION_OBJ
接着查看该视图的定义
SQL> select text from dba_views where view_name='_CURRENT_EDITION_OBJ';
TEXT--------------------------------------------------------------------------------select o."OBJ#",o."DATAOBJ#",o."OWNER#",o."NAME",o."NAMESPACE",o."SUBNAME",o."TYPE#",o."CTIME",o."MTIME",o."STIME",o."STATUS",o."REMOTEOWNER",o."LINKNAME",o."FLAGS",o."OID$",o."SPARE1",o."SPARE2",o."SPARE3",o."SPARE4",o."SPARE5",o."SPARE6",
o.spare3, case when (o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87) or bitand(u.spare1, 16) = 0) then null when (u.type# = 2) then (select eo.name from obj$ eo where eo.obj# = u.spare2) else 'ORA$BASE' endfrom obj$ o, user$ uwhere o.owner# = u.user# and ( /* non-versionable object */ ( o.type# not in (4,5,7,8,9,10,11,12,13,14,22,87,88) or bitand(u.spare1, 16) = 0) /* versionable object visible in current edition */ or ( o.type# in (4,5,7,8,9,10,11,12,13,14,22,87) and ( (u.type# <> 2 and sys_context('userenv', 'current_edition_name') = 'ORA$BASE')
or (u.type# = 2 and u.spare2 = sys_context('userenv', 'current_edition_id')) or exists (select 1 from obj$ o2, user$ u2 where o2.type# = 88 and o2.dataobj# = o.obj# and o2.owner# = u2.user# and u2.type# = 2 and u2.spare2 = sys_context('userenv', 'current_edition_id')) ) ) )
从该视图的定义中可以看到o.DATAOBJ#字段来自于内部表obj，而obj.dataobj#又对应着seg$.HWMINCR
select max(HWMINCR) from sys.seg$;SQL> select max(HWMINCR) from sys.seg$;
MAX(HWMINCR)------------ 74831
这里的HWMINCR就是data_object_id的来源，每次seg里生成新的一条记录都会增加HWMINCR这个值，同时obj.dataobj#也会跟着变化。
从前面可以看到74831是tab1表上的索引ind_tab1_pk的data_object_id，那么现在对该索引进行rebuild再查看seg$.HWMINCR最大值的变化
alter index ind_tab1_pk rebuild;
SQL> alter index ind_tab1_pk rebuild;
Index altered.
SQL> select max(HWMINCR) from sys.seg$;
MAX(HWMINCR)------------ 74833
这时候发现max(HWMINCR)变为了74833，这里的HWMINCR就是data_object_id的来源，每当数据库上存在物理段的对象段发生变化的时候都会在seg里生成新的一条记录增加HWMINCR这个值，同时obj.dataobj#也会跟着变化。最后在视图dba_objects中对应对象的data_object_id值也会发生变化。
墨天轮原文链接：https://www.modb.pro/db/24365（复制到浏览器中打开或者点击“阅读原文”）
推荐阅读：144页！分享珍藏已久的数据库技术年刊
数据和云
ID：OraNews
如有收获，请划至底部，点击“在看”，谢谢！
点击下图查看更多 ↓
云和恩墨大讲堂 | 一个分享交流的地方
长按，识别二维码，加入万人交流社群
请备注：云和恩墨大讲堂
点个“在看”
你的喜欢会被看到❤