object_id and data_object_id 区别_20091213
object_id and data_object_id 区别
参考文档
http://hi.baidu.com/edeed/blog/item/69e60cf422567bec7709d727.html
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不会改变。
SQL> create table t2(id number, name varchar2(20));
Table created
SQL> select object_id, data_object_id from user_obJects where object_name='T2';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
29076 29076
对象建立时两者是一致的。
SQL> select object_id, data_object_id from user_obJects where object_name='IND_T2';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
29078 29078
表和索引都存在DATA_OBJECT_ID
SQL> create view v_t2 as select * from t2;
View created
SQL> select object_id, data_object_id from user_obJects where object_name='V_T2';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
29077
视图不存在Data_object_id。因为没有真正的物理存储空间
SQL> truncate table t2;
Table truncated
SQL> select object_id, data_object_id from user_obJects where object_name='IND_T2';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
29078 29079
SQL> select object_id, data_object_id from user_obJects where object_name='T2';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
29076 29080
Truncate table 后，Data Segment Object和Index Segment Object 的DATA_OBJECT_ID
都发生改变，而OBJECT_ID不会改变。
同时发现当创建一个新的对象的时候object_id和data_object_id应该分别取
object_id ＝ max(select max(object_id) from dba_objects)+1,
data_object_id = max(select max(hwmincr) from seg$)+1
Data_object_id的改变也说明truncate,move, rebuild动作会重新生成一个物理的Segment
现在看看data_object_id从哪里来的：
idle> 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, '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, 'SECURITY PROFILE', 59, 'RULE',
62, 'EVALUATION CONTEXT',
'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')
from sys.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_'
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'
from sys.link$ l, sys.user$ u
where l.owner# = u.user#
可以看到data_object_id是从obj$.dataobj#来的，而obj$.dataobj#又对应着seg$.HWMINCR(这如何看出来的？)。
idle> select max(HWMINCR) from sys.seg$;
MAX(HWMINCR)
------------
29080
这里的HWMINCR就是data_object_id的来源，每次seg$里生成新的一条记录都会增加HWMINCR这个值(段的高水位值？)，同时obj$.dataobj#也会跟着变化。
查找物理空间被更改过的对象
SQL> select object_name, object_type, object_id, data_object_id from user_objects
2 where object_id <> data_object_id;
OBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
-------------------------------------------------------------------------------- --------------
IND_T2 INDEX 29078 29079
T2 TABLE 29076 29080
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/10248702/viewspace-622310/ ，如需转载，请注明出处，否则将追究法律责任。
