OBJECT_ID和DATA_OBJECT_ID

sunwgneuqsoft發表於2007-12-10

USER_OBJECTS資料字典裡面有兩個欄位:OBJECT_IDDATA_OBJECT_ID

OBJECT_ID大家都比較熟悉,是每個資料物件的唯一標識。

DATA_OBJECT_ID可能就用的少一些了,這個欄位是和SEG$對應的,用來標識該OBJECT的物理儲存的段的位置。

[@more@]

只有表,索引,UNDO這些有實在儲存的物件才會對應一個物理的SEGMENT,欄位DATA_OBJECT_ID才會有數值。象索引,函式等僅僅存在OBJECT_ID,而沒有DATA_OBJECT_ID

SQL> create table test as select * from dual;

Table created.

SQL> select object_id,data_object_id from user_objects where object_name='TEST';

689172 689172

SQL> create view v_test as select * from test;

View created.

SQL> select object_id,data_object_id from user_objects where object_name='V_TEST';

689173

SQL> create table test1 as select * from dual;

Table created.

SQL> select object_id,data_object_id from user_objects where object_name='TEST1';

689174 689174

大家可以看一下USER_OBJECTS資料字典裡面,OBJECT_IDDATA_OBJECT_ID的關係,大部分物件這兩個欄位應該是相等的,而少部分物件的DATA_OBJECT_ID會比OBJECT_ID大。

是這樣的,當我們新建一個物件的時候,OBJECT_IDDATA_OBJECT_ID兩者應該相等,他們的值應該等於MAX(SELECT MAX(OBJECT_ID) FROM DBA_OBJECTS,SELECT MAX(HWMINCR) FROM SEG$) + 1

而當我們對物件進行特定的操作的時候DATA_OBJECT_ID就會變化。比如說TRUNCATEMOVE操作。

SQL> create table test as select rownum rn from user_objects where rownum<2;

Table created.

SQL> select object_id,data_object_id from user_objects where object_name='TEST';

689191 689191

SQL> truncate table test;

Table truncated.

SQL> select object_id,data_object_id from user_objects where object_name='TEST';

689191 689192

SQL> truncate table test;

Table truncated.

SQL> select object_id,data_object_id from user_objects where object_name='TEST';

689191 689192

從上面的例子猜想,TRUNCATE可能是在原來SEGMENT的位置上建立了一個新的SEGMENT,然後分配給原來的物件,這樣就達到了清空了原來的物件中的內容。另一個比較有趣的地方是,當表中有資料和表中沒有資料的兩種情況下,ORACLE的處理方式並不一樣。如果表中是沒有資料的,那data_object_id是不會發生變化的。

前面說到了DATA_OBJECT_ID會比OBJECT_ID大,那麼有沒有DATA_OBJECT_IDOBJECT_ID的情況呢?

我猜想應該是有的,那就是在分割槽表的分割槽交換的操作下。

先寫到這,關於分割槽表中的OBJECT_IDDATA_OBJECT_ID關係,下次接著寫吧。

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/8394333/viewspace-991018/,如需轉載,請註明出處,否則將追究法律責任。

相關文章