Oracle中object_id和data_object_id的區別

PiscesCanon發表於2017-05-30
實驗出發點:
今天做關於flush buffer cache和x$bh關係的時候,發現的一個問題,詳情可以請看http://blog.itpub.net/30174570/viewspace-2140062/

其他資料:
正如文章題目而言,兩者有什麼區別?

http://blog.itpub.net/12361284/viewspace-170223/可以得到以下資訊:
1.object_id其實是對每個資料庫中資料物件的唯一標識
2.data_object_id用的相比來說會少一些,主要是和seg$對應,用來表示object的物理儲存段的實際位置.
只有表,索引,undo這些有實際物理儲存位置的物件才有data_object_id,而像一些函式,儲存過程,以及view等等是沒有data_object_id的,也就是說僅有object_id

另外,當建立一個新的物件的時候object_id和data_object_id應該分別取
max(select max(object_id) from dba_objects)+1,
max(select max(hwmincr) from seg$)+1

環境說明:
1.作業系統版本
  1. [oracle@oracle ~]$ uname -a
  2. Linux oracle.example.com 2.6.32-431.el6.x86_64 #1 SMP Sun Nov 10 22:19:54 EST 2013 x86_64 x86_64 x86_64 GNU/Linux
  3. [oracle@oracle ~]$ lsb_release -a
  4. LSB Version:    :base-4.0-amd64:base-4.0-noarch:core-4.0-amd64:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-noarch
  5. Distributor ID:    RedHatEnterpriseServer
  6. Description:    Red Hat Enterprise Linux Server release 6.5 (Santiago)
  7. Release:    6.5
  8. Codename:    Santiago
2.資料庫版本
  1. SYS@proc> select * from v$version where rownum=1;

  2. BANNER
  3. --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

實驗過程:
但是自己做實驗還是有些出入的。
1.關於這兩者的取值的實驗如下:
  1. SYS@proc> select a.max_object_id,b.max_hwmincr from (select max(object_id) max_object_id from dba_objects) a,(select max(hwmincr) max_hwmincr from seg$) b;

  2. MAX_OBJECT_ID MAX_HWMINCR
  3. ------------- -----------
  4.         89219       89219

  5. SYS@proc> create table pc(id number);

  6. Table created.

  7. SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='PC';

  8.  OBJECT_ID DATA_OBJECT_ID
  9. ---------- --------------
  10.      89220          89220         --符合前文所說的建立新物件時候的取值規則

  11. SYS@proc>
  12. SYS@proc> drop table pc purge;

  13. Table dropped.

  14. SYS@proc> select a.max_object_id,b.max_hwmincr from (select max(object_id) max_object_id from dba_objects) a,(select max(hwmincr) max_hwmincr from seg$) b;

  15. MAX_OBJECT_ID MAX_HWMINCR
  16. ------------- -----------
  17.         89219       89219

  18. SYS@proc> create table pc(id number);

  19. Table created.

  20. SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='PC';

  21.  OBJECT_ID DATA_OBJECT_ID
  22. ---------- --------------
  23.      89221          89221        --不符合前文所說的建立新物件時候的取值規則
根據大量實驗發現,建立的物件的object_id和data_object_id取值會取MAX_OBJECT_ID和MAX_HWMINCR曾經達到的最大值+1,也就是上述實驗中一開始pc表的object_id,data_object_id均為89220,但是drop表pc之後,89220數值並不能重用,新建立的物件只能取89220+1的數值具體原因還不知道,這是接下去可以探究的問題之一。

從其他實驗結果來看。特別地,當上述實驗中的MAX_OBJECT_ID和MAX_HWMINCR不等的時候,新建立物件的object_id,data_object_id值為MAX_OBJECT_ID和MAX_HWMINCR其中比較大一個的曾經最大值+1。具體原因還不知道,這是接下去可以探究的問題之一。
2.看不明白可以直接看如下實驗:
  1. SYS@proc> create table aa (id number);

  2. Table created.

  3. SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='AA';

  4.  OBJECT_ID DATA_OBJECT_ID
  5. ---------- --------------
  6.      89224          89224

  7. SYS@proc> alter table aa move tablespace test;

  8. Table altered.

  9. SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='AA';

  10.  OBJECT_ID DATA_OBJECT_ID
  11. ---------- --------------
  12.      89224          89225

  13. SYS@proc> select a.max_object_id,b.max_hwmincr from (select max(object_id) max_object_id from dba_objects) a,(select max(hwmincr) max_hwmincr from seg$) b;

  14. MAX_OBJECT_ID MAX_HWMINCR
  15. ------------- -----------
  16.         89224       89225

  17. SYS@proc> create table yy(id number);

  18. Table created.

  19. SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';

  20.  OBJECT_ID DATA_OBJECT_ID
  21. ---------- --------------
  22.      89226          89226
從這裡也可以看出,對錶move操作會增加DATA_OBJECT_ID的值,具體原因還不知道,這是接下去可以探究的問題之一。

3.從網上資料看,truncate操作也有同樣的效果。
不過自己實驗發現,對非空表做截斷才會。具體原因還不知道,這是接下去可以探究的問題之一
  1. SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';

  2.  OBJECT_ID DATA_OBJECT_ID
  3. ---------- --------------
  4.      89226          89226

  5. SYS@proc> select count(*) from yy;

  6.   COUNT(*)
  7. ----------
  8.      0

  9. SYS@proc> truncate table yy;

  10. Table truncated.

  11. SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';

  12.  OBJECT_ID DATA_OBJECT_ID
  13. ---------- --------------
  14.      89226          89226

  15. SYS@proc> insert into yy values(1);

  16. 1 row created.

  17. SYS@proc> commit;

  18. Commit complete.

  19. SYS@proc> truncate table yy;

  20. Table truncated.

  21. SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';

  22.  OBJECT_ID DATA_OBJECT_ID
  23. ---------- --------------
  24.      89226          89227

實驗結論:

具體結論前文可以明顯看出來了,比較懶就不寫了。
關於data_object_id的意義,待文中未解決的問題解決之後可能才會明白。
要是各位網友有知道的,歡迎指教,先謝謝。

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

相關文章