当表刚创建的时候它的object_id和data_object_id都是相等的，但是如果表经过move或truncate后那么data_object_id将会有变化。
SQL 10G>select object_id,data_object_id from user_objects where object_name=’T';
OBJECT_ID DATA_OBJECT_ID
———- ————–
63053 63053
刚开始创建表时object_id=data_object_id
SQL 10G>SELECT HEADER_FILE,HEADER_BLOCK,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’T’ AND OWNER=’TEST’;
HEADER_FILE HEADER_BLOCK BLOCKS
———– ———— ———-
4 2019 8
SQL 10G>alter table t move;
Table altered.
SQL 10G>select object_id,data_object_id from user_objects where object_name=’T';
OBJECT_ID DATA_OBJECT_ID
———- ————–
63053 63463
在move以后可以看到data_object_id发生变化了
SQL 10G>SELECT HEADER_FILE,HEADER_BLOCK,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’T’ AND OWNER=’TEST’;
HEADER_FILE HEADER_BLOCK BLOCKS
———– ———— ———-
4 467 8
SQL 10G>truncate table t;
Table truncated.
SQL 10G>select object_id,data_object_id from user_objects where object_name=’T';
OBJECT_ID DATA_OBJECT_ID
———- ————–
63053 63464
SQL 10G>SELECT HEADER_FILE,HEADER_BLOCK,BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME=’T’ AND OWNER=’TEST’;
HEADER_FILE HEADER_BLOCK BLOCKS
———– ———— ———-
4 467 8
truncate之后虽然segment的位置没有移动，但是data_object_id还是发生变化了。
SQL 10G>alter table t add(b number);
Table altered.
SQL 10G>select object_id,data_object_id from user_objects where object_name=’T';
OBJECT_ID DATA_OBJECT_ID
———- ————–
63053 63464
我们对t表加一个字段，发现data_object_id没有发生变化，我们可以认为只有当segment发生变化时data_object_id才会随之变化。我们来看看data_object_id从哪里来的
SQL 10G> select text from dba_views where view_name=’DBA_OBJECTS’；
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 distinct ‘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, ‘SECURITY PROFILE’, 59, ‘RULE’,
60, ‘CAPTURE’, 61, ‘APPLY’,
62, ‘EVALUATION CONTEXT’,
66, ‘JOB’, 67, ‘PROGRAM’, 68, ‘JOB CLASS’, 69, ‘WINDOW’,
72, ‘WINDOW GROUP’, 74, ‘SCHEDULE’, 79, ‘CHAIN’,
81, ‘FILE GROUP’,
‘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
SQL 10G>select max(HWMINCR) from sys.seg$;
MAX(HWMINCR)
————
63464
这里的HWMINCR就是data_object_id的来源，每次seg$里生成新的一条记录都会增加HWMINCR这个值，同时obj$.dataobj#也会跟着变化。
data_object_id的最主要的用户就是方便物理对象的管理啊….
比如我们做alter table exchange partition的时候, 就可以直接修改object_id与data_object_id的对应关系, 就可以实现这个操作.
具体可以自己操作一下..
select object_id,data_object_id from obj where object_name in ('’,'’);
alter session set events ‘10046 trace name context ,level 8′;
alter table tab_name exchange partition with table tab_name;
alter session set events ‘10046 trace name context off’;
select object_id,data_object_id from obj where object_name in ('’,'’);