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

object_id and data_object_id 區別_20091213

相關文章