Oracle中object_id和data_object_id的區別
實驗出發點:
今天做關於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.作業系統版本
2.資料庫版本
實驗過程:
但是自己做實驗還是有些出入的。
1.關於這兩者的取值的實驗如下:
根據大量實驗發現,建立的物件的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.看不明白可以直接看如下實驗:
從這裡也可以看出,對錶move操作會增加DATA_OBJECT_ID的值,具體原因還不知道,這是接下去可以探究的問題之一。
3.從網上資料看,truncate操作也有同樣的效果。
不過自己實驗發現,對非空表做截斷才會。具體原因還不知道,這是接下去可以探究的問題之一。
實驗結論:
具體結論前文可以明顯看出來了,比較懶就不寫了。
關於data_object_id的意義,待文中未解決的問題解決之後可能才會明白。
要是各位網友有知道的,歡迎指教,先謝謝。
今天做關於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.作業系統版本
-
[oracle@oracle ~]$ uname -a
-
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
-
[oracle@oracle ~]$ lsb_release -a
-
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
-
Distributor ID: RedHatEnterpriseServer
-
Description: Red Hat Enterprise Linux Server release 6.5 (Santiago)
-
Release: 6.5
- Codename: Santiago
-
SYS@proc> select * from v$version where rownum=1;
-
-
BANNER
-
--------------------------------------------------------------------------------
- Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
實驗過程:
但是自己做實驗還是有些出入的。
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;
-
-
MAX_OBJECT_ID MAX_HWMINCR
-
------------- -----------
-
89219 89219
-
-
SYS@proc> create table pc(id number);
-
-
Table created.
-
-
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='PC';
-
-
OBJECT_ID DATA_OBJECT_ID
-
---------- --------------
-
89220 89220 --符合前文所說的建立新物件時候的取值規則
-
-
SYS@proc>
-
SYS@proc> drop table pc purge;
-
-
Table dropped.
-
-
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;
-
-
MAX_OBJECT_ID MAX_HWMINCR
-
------------- -----------
-
89219 89219
-
-
SYS@proc> create table pc(id number);
-
-
Table created.
-
-
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='PC';
-
-
OBJECT_ID DATA_OBJECT_ID
-
---------- --------------
- 89221 89221 --不符合前文所說的建立新物件時候的取值規則
從其他實驗結果來看。特別地,當上述實驗中的MAX_OBJECT_ID和MAX_HWMINCR不等的時候,新建立物件的object_id,data_object_id值為MAX_OBJECT_ID和MAX_HWMINCR其中比較大一個的曾經最大值+1。具體原因還不知道,這是接下去可以探究的問題之一。
2.看不明白可以直接看如下實驗:
-
SYS@proc> create table aa (id number);
-
-
Table created.
-
-
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='AA';
-
-
OBJECT_ID DATA_OBJECT_ID
-
---------- --------------
-
89224 89224
-
-
SYS@proc> alter table aa move tablespace test;
-
-
Table altered.
-
-
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='AA';
-
-
OBJECT_ID DATA_OBJECT_ID
-
---------- --------------
-
89224 89225
-
-
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;
-
-
MAX_OBJECT_ID MAX_HWMINCR
-
------------- -----------
- 89224 89225
-
-
SYS@proc> create table yy(id number);
-
-
Table created.
-
-
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';
-
-
OBJECT_ID DATA_OBJECT_ID
-
---------- --------------
- 89226 89226
3.從網上資料看,truncate操作也有同樣的效果。
不過自己實驗發現,對非空表做截斷才會。具體原因還不知道,這是接下去可以探究的問題之一。
-
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';
-
-
OBJECT_ID DATA_OBJECT_ID
-
---------- --------------
-
89226 89226
-
-
SYS@proc> select count(*) from yy;
-
-
COUNT(*)
-
----------
-
0
-
-
SYS@proc> truncate table yy;
-
-
Table truncated.
-
-
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';
-
-
OBJECT_ID DATA_OBJECT_ID
-
---------- --------------
-
89226 89226
-
-
SYS@proc> insert into yy values(1);
-
-
1 row created.
-
-
SYS@proc> commit;
-
-
Commit complete.
-
-
SYS@proc> truncate table yy;
-
-
Table truncated.
-
-
SYS@proc> select object_id,data_object_id from dba_objects where owner='SYS' and object_name='YY';
-
-
OBJECT_ID DATA_OBJECT_ID
-
---------- --------------
- 89226 89227
實驗結論:
具體結論前文可以明顯看出來了,比較懶就不寫了。
關於data_object_id的意義,待文中未解決的問題解決之後可能才會明白。
要是各位網友有知道的,歡迎指教,先謝謝。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/30174570/viewspace-2140058/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- object_id 與data_object_id 的區別Object
- data_object_id 及 object_id 的聯絡與區別Object
- OBJECT_ID和DATA_OBJECT_IDObject
- object_id 及 data_object_id (oracle)ObjectOracle
- object_id and data_object_id 區別_20091213Object
- Differences between DATA_OBJECT_ID and OBJECT_IDObject
- object_id與data_object_id的關係Object
- object_id and data_object_id in dba_objectsObject
- OBJECT_ID、DATA_OBJECT_ID與truncate的本質Object
- object_id與data_object_id淺析(一)Object
- object_id與data_object_id淺析(二)Object
- oracle中in和exists的區別Oracle
- oracle中distinct和group by的區別Oracle
- oracle中 DG和GG的區別Oracle
- Oracle中truncate和delete的區別Oracledelete
- (轉)ORACLE 中IN和EXISTS的區別Oracle
- Oracle中latch和lock的區別Oracle
- Oracle中Date和Timestamp的區別Oracle
- oracle中rownum和rowid的區別Oracle
- Oracle rman中recover和restore的區別:OracleREST
- Oracle中User和Schema的區別和聯絡Oracle
- Oracle中sys和system的區別小結Oracle
- 在Oracle中session和process的區別(轉)OracleSession
- Oracle中truncate和delete的區別(例項)Oracledelete
- Oracle - @和@@、&與&& 的區別Oracle
- MySQL和Oracle的區別MySqlOracle
- Oracle和MySQL的區別OracleMySql
- mysql中!=和is not的區別MySql
- JavaScript中for in 和for of的區別JavaScript
- mysql中“ ‘ “和 “ ` “的區別MySql
- Js中for in 和for of的區別JS
- JavaScript中==和===的區別JavaScript
- Oracle中單引號和雙引號的區別Oracle
- Oracle中drop user和drop user cascade的區別Oracle
- Oracle 執行計劃中access 和 filter的區別OracleFilter
- oracle中sysdate和current_date兩者的區別Oracle
- 插入單引號在oracle和informix中的區別OracleORM
- Oracle User 和 Schema 的區別Oracle