rowid的一點總結！

一直都覺得rowid的設計非常獨到，簡單做了個總結！

未完。。。

[php]
未完。。。。
整理，修正，補充，完善當中。
1、rowid是什麼？

每一個表中都有一列rowid(iot除外)，每一行的rowid代表了該行在資料庫中儲存的實際地址，可見rowid的獨特及重要程度。rowid是偽列（pseudocolumn），偽劣的意思是實際上這一列本身在資料字典中並不存在，在查詢結果輸出時它被構造出來的。在操作上它和普通的列也有一些差別：不能透過desc以及select直接顯示出來，除非顯式說明,rowid不能做表的primarykey ，也不能對rowid執行dml操作。oracle的偽劣還有很多，如：rownum，ora_rowscn,versions_starttime, versions_endtime, versions_xid，versions_operation等，這些列都非常有用，這裡暫時不作介紹。

簡單的測試如下：

--desc無法顯示

SQL> desc t
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

ID

NUMBER(38)

--select 直接不能顯示出來

SQL> select * from t;

ID
----------
1
2

--資料字典中並不存在

SQL> select TABLE_NAME,COLUMN_NAME from dba_tab_columns where table_name='T'
2 AND owner='SYS';

TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
T ID

SQL>

不能和*直接輸出，除非在*前面寫上表名或者是表的別名，這其實和rowid本身關係不大，oracle就不支援直接將*和其他列（當然也包括偽劣）一起輸出，sql server中可以:

SQL> select * , rowid from t;
select * , rowid from t
*
第 1 行出現錯誤:
ORA-00923: 未找到要求的 FROM 關鍵字

SQL> select t.* , rowid from t;

ID NAME ROWID
---------- ------------------ ------------------
1 AAACfGAABAAAHCaAAA
2 AAACfGAABAAAHCaAAB

SQL> select a.* , rowid from t a;

ID NAME ROWID
---------- ------------------ ------------------
1 AAACfGAABAAAHCaAAA
2 AAACfGAABAAAHCaAAB

SQL> select * ,id , rowid from t ;
select * ,id , rowid from t
*
第 1 行出現錯誤:
ORA-00923: 未找到要求的 FROM 關鍵字

SQL> select t.* ,id , rowid from t ;

ID NAME ID ROWID
---------- ------------------ ---------- ------------------
1 1 AAACfGAABAAAHCaAAA
2 2 AAACfGAABAAAHCaAAB

SQL> select a.* ,id , rowid from t a;

ID NAME ID ROWID
---------- ------------------ ---------- ------------------
1 1 AAACfGAABAAAHCaAAA
2 2 AAACfGAABAAAHCaAAB

SQL>

--rowid不能做為表的主鍵

SQL> create table t4(id int);

表已建立。

SQL> alter table t4 add constraint pk_t primary key (rowid);
alter table t4 add constraint pk_t primary key (rowid)
*
第 1 行出現錯誤:
ORA-00904: "ROWID": 識別符號無效

SQL> alter table t4 add constraint pk_t primary key (id);

表已更改。

--也不能對rowid執行dmt操作（Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.）

SQL> select id ,rowid from t4;

ID ROWID
---------- ------------------
1 AAAC89AAEAAAACAAAA
2 AAAC89AAEAAAACAAAB

SQL> update t4 set rowid=chartorowid('AAAC89AAEAAAACAAAC');
update t4 set rowid=chartorowid('AAAC89AAEAAAACAAAC')
*
第 1 行出現錯誤:
ORA-01747: user.table.column, table.column 或列說明無效

2、rowid的分類

1) Physical Rowid

a) extended rowid

b) restricted rowid

2) Logical Rowids

這部分內容doc（The rows of some tables have addresses that are not physical or permanent or were not generated by Oracle Database. For example, the row addresses of index-organized tables are stored in index leaves, which can move. Oracle provides these tables with logical row identifiers, called logical rowids. Rowids of foreign tables, such as DB2 tables accessed through a gateway, are not standard Oracle Database rowids. Oracle provides foreign tables with identifiers called foreign rowids.Oracle Database uses universal rowids (urowids) to store the addresses of index-organized and foreign tables. Both types of urowid are stored in the ROWID pseudocolumn, as are the physical rowids of heap-organized tables.Oracle creates logical rowids based on the primary key of the table. The logical rowids do not change as long as the primary key does not change. The ROWID pseudocolumn of an index-organized table has a datatype of UROWID. You can access this pseudocolumn as you would the ROWID pseudocolumn of a heap-organized table (that is, using a SELECT ... ROWID statement). If you want to store the rowids of an index-organized table, then you can define a column of type UROWID for the table and retrieve the value of the ROWID pseudocolumn into that column.）介紹的更加清楚，Logical Rowids 的資料型別是UROWID（universal rowids ），Logical Rowids 主要是用在iot型別的表上。oracle建立或者生成Logical Rowids 是基於表的primary key，如果primary key不改變則Logical Rowids 就不會發生變化。

Logical Rowids 依賴primary key，所以建立iot型別的表不指定primary key是不行的：

SQL> create table t_iot(id int) organization index;
create table t_iot(id int) organization index
*
第 1 行出現錯誤:
ORA-25175: 未找到任何 PRIMARY KEY 約束條件
SQL> create table t_iot(id int primary key) organization index;

表已建立。

SQL> insert into t_iot values(1);

已建立 1 行。

SQL> insert into t_iot values(2);

已建立 1 行。

SQL> select id , rowid from t_iot;

ID ROWID
---------- -----------------------------------------
1 *BABAcIoCwQL+
2 *BABAcIoCwQP+

logical rowid使用的資料型別是urowid(universal rowids ),urowid和rowdi一樣本身也是一種資料型別，由於不太常用，可能不被人們說注意：

SQL> edit
已寫入 file afiedt.buf

1 create table t
2 (
3 id number primary key
4 )
5* organization index
SQL> /

表已建立。
SQL> insert into t values(1);

已建立 1 行。

SQL> insert into t values(2);

已建立 1 行。

SQL> commit;

提交完成。
SQL> create table tt(id int , name urowid);

表已建立。

SQL> insert into tt select id , rowid from t;

已建立2行。

SQL> col name format a18
SQL> select * from tt;

ID NAME
---------- ------------------
1 *BAEAAEwCwQL+
2 *BAEAAEwCwQP+

另外需要注意的是不能直接在iot表上建立urowid型別的列：

SQL> create table t1(id int primary key,name urowid) organization index;
create table t1(id int primary key,name urowid) organization index
*
第 1 行出現錯誤:
ORA-01429: 索引表: 沒有儲存溢位行片段的資料段

SQL> create table t1(id int primary key) organization index;

表已建立。

SQL> alter table t1 add name urowid ;
alter table t1 add name urowid
*
第 1 行出現錯誤:
ORA-01429: 索引表: 沒有儲存溢位行片段的資料段

要想在iot上使用urowid型別，需要說明OVERFLOW字句：

SQL> create table t3(id int primary key,name urowid) organization index overflow tablespace users;

表已建立。

--可以建立rowid型別的列
SQL> create table t2(id int primary key,name rowid) organization index;

表已建立。

SQL> select * from tt;

ID NAME
---------- ------------------
1 *BAEAAEwCwQL+
2 *BAEAAEwCwQP+

SQL> insert into t2 select * from tt;
insert into t2 select * from tt
*
第 1 行出現錯誤:
ORA-01410: 無效的 ROWID

需要注意的是urowid型別不會和rowid型別相互轉化。有關logical rowid的格式以及如何透過primary key生成的也是我的疑問。

3、extended rowid的格式

rowid是一個18位的64進位制數，它的資料型別就是rowid型別的。rowid本身就是一種資料型別，由於並不常用，所以通常可能不被大家留意：

SQL> alter table t add name rowid;

表已更改。

SQL> desc t
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

ID NUMBER(38)
NAME ROWID

SQL> select * from t;

ID NAME
---------- ------------------
1
2

SQL> update t set name='test';
update t set name='test'
*
第 1 行出現錯誤:
ORA-01410: 無效的 ROWID

SQL> update t set name='AAACdcAABAAAHCKAAB';

已更新2行。

SQL> update t set name='AAACdcAAB';
update t set name='AAACdcAAB'
*
第 1 行出現錯誤:
ORA-01410: 無效的 ROWID

我們發現只有滿足rowod格式的資料才能儲存到rowid列中。rowid的格式如下：

OOOOOO BBBBBB FFF RRR

Data object number Relative file number Row number

下面把t表的rowid拆分一下看看：

SQL> select id , rowid , substr(rowid , 1 , 6) data_object_id , substr(rowid , 7
, 3) rfile#,substr(rowid,10,6) block_id,substr(rowid , 16,3) from t;

ID ROWID DATA_OBJECT_ RFILE# BLOCK_ID SUBSTR
---------- ------------------ ------------ ------ ------------ ------
1 AAACdcAABAAAHCKAAA AAACdc AAB AAAHCK AAA
2 AAACdcAABAAAHCKAAB AAACdc AAB AAAHCK AAB

SQL>

這裡顯示出來的是64進位制數，oracle在設計rowid時用A~Z表示0-25,用a~z表示26-51,用0~9表示52-61,用+表示62，用/表示63。知道了這個規則之後我們計算一下t表中兩行資料的rowid來驗證一下：

1)先看data object id是AAACdc，由於兩行資料都屬於t表，因此所有資料的這個值肯定都相同，轉化位10進位制數：

SQL> select 2*power(64,2)+29*64+28 from dual;

2*POWER(64,2)+29*64+28
----------------------
10076

查詢看看和資料字典中記錄的data_object_id是否相同，結果顯示相同。

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10076

2) 同樣的方法計算一下rfile#:

AAB直接可以看出來rfile# =1

SQL> select name , rfile# from v$datafile where rfile#=1;

NAME RFILE#
------------------------------------------------------------ ----------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 1

SQL> show user
USER 為 "SYS"
由於我在建表t時使用了sys使用者，因此t存放在了system 表空間中了，這當然不是個好習慣。大家在操作時最好使用其他使用者而且不要把物件輕易的放在system表空間中，以免給system tbs中造成過多的碎片。

3) 再來看看block_id:AAAHCK

SQL> select 7*power(64,2)+2*64+10 from dual;

7*POWER(64,2)+2*64+10
---------------------
28810

查詢一下segment header block 發現是28809，資料存在28810上應該沒有問題，當然我們也可以dump 28810來驗證資料是否存在。

SQL> SELECT HEADER_BLOCK FROM DBA_SEGMENTS WHERE segment_name='T' AND owner='SYS
';

HEADER_BLOCK
------------
28809

4) 最後來看一下row#:AAA AAB兩行的row#分別是：0 ，1 ；0表示該行資料是其所在block上的第一行，其它的通理。

4、restricted rowid的格式

restricted rowid是在oracle 7中使用的rowid，doc上說它在絕大多數os上佔用6個byte（The restricted internal ROWID is 6 bytes on most platforms），但是win下是10個byte：

SQL> select * from t1;

ID NAME
---------- ---------------------------------------
1 0000708A.0000.0001
2 0000708A.0001.0001

SQL> select dump(name) from t1;

DUMP(NAME)
--------------------------------------------------

Typ=69 Len=10: 0,0,0,0,0,64,112,138,0,0
Typ=69 Len=10: 0,0,0,0,0,64,112,138,0,1

SQL>

而且doc上說它是18位的16進位制數（

The restricted ROWID pseudocolumn is returned to client applications in the form of an 18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the ROWID.

），應該是包含了"."的，其實句點本身沒有什麼意義。正如上面doc所言它由datablock,row,datafile組成。

轉為為10進位制數：

SQL> select to_number(substr(name,1,8),'xxxxxxxx') block_id , to_number(substr(n
ame,10,4)) row# , to_number(substr(name , 15, 4)) file_id from t1;

BLOCK_ID ROW# FILE_ID
---------- ---------- ----------
28810 0 1
28810 1 1

SQL>

t1中的name儲存的是t表的restricted rowid，透過這個restricted rowid拆分出來的BLOCK_ID ROW# FILE_ID和在第三部分中透過extended rowid拆分出來的完全一致。

另外restricted rowid是oracle為了版本向後相容而保留的歷史產物，這裡不做太多介紹，只所以還要介紹restricted rowid是因為在介紹dbms_rowid這個package時會提到。

5、rowid package

上面透過很繁瑣的計算一方面是驗證一下rowid的計算，另一方面也是查詢一下和資料字典中記錄的資訊作個對比。其實oracle還提供了一個package：dbms_rowid,利用dbms_rowid中的幾個函式可以很容易的把rowid拆分成10進位制數：

1）透過函式拆分rowid：

SQL> select id , rowid , dbms_rowid.rowid_relative_fno(rowid) from t;

ID ROWID DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)
---------- ------------------ ------------------------------------
1 AAACdcAABAAAHCKAAA 1
2 AAACdcAABAAAHCKAAB 1

SQL> select id , rowid , dbms_rowid.rowid_block_number(rowid) from t;

ID ROWID DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)
---------- ------------------ ------------------------------------
1 AAACdcAABAAAHCKAAA 28810
2 AAACdcAABAAAHCKAAB 28810

SQL> select id , rowid , dbms_rowid.rowid_row_number(rowid) from t;

ID ROWID DBMS_ROWID.ROWID_ROW_NUMBER(ROWID)
---------- ------------------ ----------------------------------
1 AAACdcAABAAAHCKAAA 0
2 AAACdcAABAAAHCKAAB 1

SQL>

dbms_row中還有幾個非常有用的函式：

SQL> select dbms_rowid.rowid_to_absolute_fno(rowid , 'SYS' , 'T') from t;

DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SYS','T')
-------------------------------------------------
1
1

1）函式rowid_to_absolute_fno可以實現rfile#(相對檔案號) 到file#(絕對檔案號)的轉化。

SQL> select dbms_rowid.rowid_to_absolute_fno(rowid , 'SYS' , 'T') from t;

DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO(ROWID,'SYS','T')
-------------------------------------------------
1
1

由於都是small file type的tbs以及資料檔案數<1024因此file#和rfile#都是相同的。

2）rowid_to_restricted函式可以實現從extentded rowd到restricted rowid的轉化：

使用這個函式時要注意一個引數（conversion_type in int），這個引數只能是0和1

其中0 表示rowid是restricted型別的，1表示是extended型別的

SQL> select dbms_rowid.rowid_to_restricted(rowid,0) from t;

DBMS_ROWID.ROWID_T
------------------
0000708A.0000.0001
0000708A.0001.0001

不過這裡我發現輸入1時也可以轉化過來，有一點疑問。

3）rowid_to_extended可以實現從restricted rowid到extended rowid的轉化。

SQL> create table t1(id int , name rowid);

表已建立。

SQL> insert into t1 select id , dbms_rowid.rowid_to_restricted(rowid , 0) from t
;

已建立2行。

SQL> select * from t1;

ID NAME
---------- ------------------------------------------------------------
1 0000708A.0000.0001
2 0000708A.0001.0001

SQL> desc t
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

ID NUMBER(38)
NAME ROWID

SQL> col name format a20
SQL> select id , dbms_rowid.rowid_to_extended(name,'SYS','T',1) from t1;

ID DBMS_ROWID.ROWID_T
---------- ------------------
1 AAACdcAABAAAHCKAAA
2 AAACdcAABAAAHCKAAB

SQL> select id , rowid from t;

ID ROWID
---------- ------------------
1 AAACdcAABAAAHCKAAA
2 AAACdcAABAAAHCKAAB

SQL>

這裡有點繞其實就是把t中的rowid先轉為restricted rowid臨時存放在t1表中的name欄位中，之後在轉為extended rowid和t中顯示的rowid比較一下看看這兩個函式是否好用！結果當然是一樣的，說明沒有問題。

4）rowid_create可以根據輸入的data_object_id,rfile#,blocked_id , row#生成rowid：

SQL> select id , rowid , dbms_rowid.rowid_create(1,10076
2 ,1,28810,decode(id,1,0,2,1)) from t;

ID ROWID DBMS_ROWID.ROWID_C
---------- ------------------ ------------------
1 AAACdcAABAAAHCKAAA AAACdcAABAAAHCKAAA
2 AAACdcAABAAAHCKAAB AAACdcAABAAAHCKAAB

SQL>

透過把上面拆分rowid生成的資訊再作為引數透過rowid_create重新生成rowid和顯示出來的rowid完全一樣，說明沒有問題。

5）函式rowid_type是用來判斷rowid是restricted orextended的：

SQL> select id , dbms_rowid.rowid_type(rowid) from t;

ID DBMS_ROWID.ROWID_TYPE(ROWID)
---------- ----------------------------
1 1
2 1

SQL> select id , dbms_rowid.rowid_type(name) from t1;

ID DBMS_ROWID.ROWID_TYPE(NAME)
---------- ---------------------------
1 0
2 0

SQL> select * from t1;

ID NAME
---------- --------------------
1 0000708A.0000.0001
2 0000708A.0001.0001

SQL>

返回1表示rowid是extended，0表示rowid是restricted。

6）其實rowid提供的一個procedure可以直接把rowid拆分，當然一次只能拆分一個rowid，不過我們可以自己改造一下完全可以使其實現拆分表中各行的rowid。

SQL> declare
2 v_rowid_type int;
3 v_object_number number;
4 v_relative_fno int;
5 v_block_number number;
6 v_row_number int;
7 begin
8 dbms_rowid.rowid_info('AAACdcAABAAAHCKAAA',v_rowid_type,
9 v_object_number,v_relative_fno,v_block_number,v_row_number);
10 dbms_output.put_line(v_rowid_type||' '||v_object_number||' '||v_relative_fn
o||' '||v_block_number||' '||v_row_number);
11 end;
12 /
1 10076 1 28810 0

PL/SQL 過程已成功完成。

SQL>

6、rfile# 和 file#的區別

rowid涉及到了rfile#,這裡簡要介紹一下rfile# 和 file#的區別，因為經常看到puber門發出這樣的疑問：

file#是資料檔案的絕對檔案號，是資料檔案在資料庫中的唯一標示；對應著dba_data_files.file_id以及v$datafile.file#,資料庫中每增加一個datafile，系統都會為其分配一個file_id,系統優先使用當前最大值以下的空閒值。

SQL> select file_name , file_id from dba_data_files;

FILE_NAME FILE_ID
------------------------------------------------------------ ----------
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 4
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 3
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF 2
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 1
E:ORACLEPRODUCT10.2.0ORADATATESTMSSM.DBF 6
E:ORACLEPRODUCT10.2.0ORADATATESTASSM.DBF 7
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS02.DBF 12

已選擇7行。

SQL> alter tablespace assm add datafile 'E:ORACLEPRODUCT10.2.0ORADATATESTA
SSM01.DBF' size 3m;

表空間已更改。

SQL> select file_name , file_id from dba_data_files;

FILE_NAME FILE_ID
------------------------------------------------------------ ----------
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 4
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 3
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF 2
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 1
E:ORACLEPRODUCT10.2.0ORADATATESTASSM01.DBF 5
E:ORACLEPRODUCT10.2.0ORADATATESTMSSM.DBF 6
E:ORACLEPRODUCT10.2.0ORADATATESTASSM.DBF 7
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS02.DBF 12

已選擇8行。

SQL>

這裡發現新增加的file，系統為它分配的file_id=5而不是13

資料庫同時能開啟的最大資料檔案數是受到引數db_files的限制，default值是200，超過default值以後再增加資料檔案會得到錯誤：

declarev_string varchar2(200) ;beginfor i in 1..1024 loop
v_string:='create tablespace'||' df'||i||' datafile '||'''E:ORACLEPRODUCT
10.2.0ORADATAORCLdf'||i||'.DBF'''||' size 3m';execute immediate v_string;dbms_output.put_line(v_string) ;end loop;end;

錯誤如下：

............................
create tablespace df192 datafile'E:ORACLEPRODUCT10.2.0ORADATAORCLdf192.DBF' size 3mdeclare
*第 1 行出現錯誤:ORA-00059: 超出 DB_FILES 的最大值
ORA-06512: 在 line 6
SQL> show parameter db_files
NAME TYPE VALUE------------------------------------ ----------- ------------------------------db_files integer 1250

SQL> select count(*) from dba_data_files;COUNT(*)
----------1209

修改之後，繼續建立，目前庫中資料檔案達到了1209個。到底最多能有多少個資料檔案？doc上說是65533，doc如下：

Database files
Maximum per tablespace
Operating system dependent; usually 1022
Maximum per database
65533

資料檔案太多，不容驗證。驗證一下每個表空間最多能有多少個datafile：

declare 2 v_string varchar2(200) ;3 begin
4 for i in 2..1024 loop
5 v_string:='alter tablespace df1 add datafile '||'''E:ORACLEPRODUCT10.2
.0ORADATAORCLdf'||i||'.DBF'''||' size 1m';6 execute immediate v_string;7 end loop; 8 end;9 /
declare
*第 1 行出現錯誤:ORA-01686: 最大檔案數 (1023) 對於表空間 DF1 已達到
ORA-06512: 在 line 6SQL> connect /as sysdba
已連線。
SQL> select count(*) from dba_data_files where tablespace_name='DF1';

COUNT(*)
----------
1023

COUNT(*)
----------1023doc上說每個tbs能有的資料檔案依賴於os，通常是1022個，在win下看來是1023個。

另外值得一提的是db_files,oracle不建議把db_files設的太大，因為db_files越大消耗sga中的mem就越多（When starting an Oracle Database instance, the DB_FILES initialization parameter indicates the amount of SGA space to reserve for datafile information and thus, the maximum number of datafiles that can be created for the instance.
If the value of DB_FILES is too high, memory is unnecessarily consumed.）。

提到db_files，不得不說一說controlfile中的MAXDATAFILES，控制檔案中的datafiles 僅僅是影響控制檔案中datafile section的初始化大小！即使沒有初始化，在10g也沒有什麼影響，為了能滿足開啟更多的資料檔案，oracle會自動擴充套件controlfile中datafile section的，在10g之前，如果datafiles達到了controlfile中的MAXDATAFILES需要重新建立控制檔案。否則系統也會報錯：（以下錯誤來自於網上，機器沒有低版本的oracle，沒有親自測試）

ORA-01165: MAXDATAFILES may not exceed string
Cause: MAXDATAFILES specified on the command line too large.
Action: Resubmit the command with a smaller MAXDATAFILES

不過MAXDATAFILES的值越大，控制檔案也越大。MAXDATAFILES的最大值是65534

SQL> CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 100000
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG' SIZE 10M,9 GROUP 2 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG' SIZE 10M,10 GROUP 3 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'E:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF',14 'E:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF', 15 'E:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF',16 'E:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF',17 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST.DBF', 18 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST1.DBF', 19 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST2.DBF', 20 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST3.DBF',21 'E:ORACLEPRODUCT10.2.0ORADATAORCLDF1.DBF'22 CHARACTER SET ZHS16GBK
23 ;

CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG*第 1 行出現錯誤:ORA-01165: MAXDATAFILES ???? 65534
SQL> edit

已寫入 file afiedt.buf

1 CREATE CONTROLFILE REUSE DATABASE "ORCL" NORESETLOGS NOARCHIVELOG
2 MAXLOGFILES 16
3 MAXLOGMEMBERS 3
4 MAXDATAFILES 65534
5 MAXINSTANCES 8
6 MAXLOGHISTORY 292
7 LOGFILE
8 GROUP 1 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO01.LOG' SIZE 10M, 9 GROUP 2 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO02.LOG' SIZE 10M,10 GROUP 3 'E:ORACLEPRODUCT10.2.0ORADATAORCLREDO03.LOG' SIZE 10M
11 -- STANDBY LOGFILE
12 DATAFILE
13 'E:ORACLEPRODUCT10.2.0ORADATAORCLSYSTEM01.DBF',14 'E:ORACLEPRODUCT10.2.0ORADATAORCLUNDOTBS01.DBF',15 'E:ORACLEPRODUCT10.2.0ORADATAORCLSYSAUX01.DBF',16 'E:ORACLEPRODUCT10.2.0ORADATAORCLUSERS01.DBF',17 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST.DBF',18 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST1.DBF',19 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST2.DBF',20 'E:ORACLEPRODUCT10.2.0ORADATAORCLTEST3.DBF',

21 'E:ORACLEPRODUCT10.2.0ORADATAORCLDF1.DBF'22* CHARACTER SET ZHS16GBK
23 /控制檔案已建立。
SQL>

上面一下引出了很多問題，有些冗長，接著看file# 和rfile#的區別，看看doc的介紹：

Absolute Uniquely identifies a datafile in the database. This file number can be used in many SQL statements that reference datafiles in place of using the file name. The absolute file number can be found in the FILE# column of the V$DATAFILE or V$TEMPFILE view, or in the FILE_ID column of the DBA_DATA_FILES or DBA_TEMP_FILES view.
Relative Uniquely identifies a datafile within a tablespace. For small and medium size databases, relative file numbers usually have the same value as the absolute file number. However, when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number. In a bigfile tablespace, the relative file number is always 1024 (4096 on OS/390 platform).

when the number of datafiles in a database exceeds a threshold (typically 1023), the relative file number differs from the absolute file number已經很好理解了。下面創一個bigfile 型別的tbs看看：

SQL> create bigfile tablespace bigfile datafile 'E:ORACLEPRODUCT10.2.0ORADAT
ATESTbigfile.dbf' size 3m;

表空間已建立。

SQL> col name format a56
SQL> select name , file#, rfile# from v$datafile;

NAME FILE# RFILE#
-------------------------------------------------------- ---------- ----------
E:ORACLEPRODUCT10.2.0ORADATATESTSYSTEM01.DBF 1 1
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS01.DBF 2 2
E:ORACLEPRODUCT10.2.0ORADATATESTSYSAUX01.DBF 3 3
E:ORACLEPRODUCT10.2.0ORADATATESTUSERS01.DBF 4 4
E:ORACLEPRODUCT10.2.0ORADATATESTASSM01.DBF 5 5
E:ORACLEPRODUCT10.2.0ORADATATESTMSSM.DBF 6 6
E:ORACLEPRODUCT10.2.0ORADATATESTASSM.DBF 7 7
E:ORACLEPRODUCT10.2.0ORADATATESTBIGFILE.DBF 8 1024
E:ORACLEPRODUCT10.2.0ORADATATESTUNDOTBS02.DBF 12 12

已選擇9行。

SQL>

我們發現新建立的tbs bigfile其file#=8 rfile=1024和doc說的一致。

關於doc的這段說法（ The absolute file number can be found in the FILE# column of the V$DATAFILE or V$TEMPFILE view, or in the FILE_ID column of the DBA_DATA_FILES or DBA_TEMP_FILES view.
）這裡需要說明一下，資料檔案沒有問題，他說DBA_TEMP_FILES 以及V$TEMPFILE 中的file_id以及file#是絕對檔案號是有前提條件的，只能說他們是tempfile的絕對檔案號，在整個資料庫內部臨時檔案也是有絕對號的，不過在dba_temp_files和v$tempfile中都沒有顯示出來，如果透過一個大的排序使其使用tempseg，然後觀察v$tempseg_usage 或者$sort_usage:

SQL> select tablespace,segtype ,segfile# from v$sort_usage;

TABLESPACE SEGTYPE SEGFILE#
------------------------------- --------- ----------
TEMP SORT 202

SQL>

這裡看到的SEGFILE#=202才是臨時檔案真真在資料庫中的絕對檔案號，為什麼是202？是因為庫中還存在一個臨時檔案：

SQL> select file_name , tablespace_name from dba_temp_files;

FILE_NAME TABLESPACE
-------------------------------------------------- ----------
E:ORACLEPRODUCT10.2.0ORADATATESTTMP.DBF TMP
E:ORACLEPRODUCT10.2.0ORADATATESTTEMP02.DBF TEMP

SQL>

而臨時檔案TMP.DBF 的絕對檔案號是201。oracle是如何絕對臨時檔案在庫中的編號的，其實是受到引數db_files的限制：

SQL> show parameter db_files

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_files integer 200
SQL>

從這個引數指定的為資料庫預留的資料檔案編號開始為臨時檔案指定臨時檔案在庫中的真真絕對號。

7、object_id 和data_object_id的區別

經常看到pub上有人發出這樣的疑問：兩者的區別，其實容易理解：

object_id 是物件的物理編號，只要這個物件建立系統就為它分配了一個物件編號，而且只要物件在資料庫中存在，這個編號就不會變化，是物件在資料庫中的唯一標示。

data_object_id是物件的邏輯編號，準確的說是segment的編號，由於segment存在表空間中，因此也可以說data_object_id是在表空間裡的物件編號，或者說是segment的編號，如果segment發生變化，data_object_id是會改變的，諸如我們熟悉的move , truncate操作都會是data_object_id發生變化。簡單的測試一下：

--資料庫中確實存在data_object_id相同的很多物件，因為data_object_id是tbs內部的標示，因此在庫中存在相同的不足為奇

SQL> select data_object_id , count(*) from dba_objects
2 group by data_object_id
3 having count(*)>1;

DATA_OBJECT_ID COUNT(*)
-------------- ----------
7612
29 3
6 3
573 2
2 17
181 7
251 2
163 3
8 3
241 3
10 3

已選擇11行。

--再來看看tbs中是否存在相同的data_object_id ：

--再來看看庫中是否存在相同的object_id:

SQL> select object_id , count(*) from dba_objects
2 where object_id is not null
3 group by object_id
4 having count(*)>1;

未選定行

SQL>

驗證一下data_object_id的變化：

SQL> select id , rowid from t;

ID ROWID
---------- ------------------
1 AAACdcAABAAAHCKAAA
2 AAACdcAABAAAHCKAAB

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10076

SQL> ALTER table t move;

表已更改。

--這裡需要注意，move後面不寫tablespace以及tbs名字似乎不能完全保證segment發生移動

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10176

SQL> ALTER table t move tablespace users;

表已更改。

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10177

SQL> ALTER table t move tablespace system;

表已更改。

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10178

SQL> ALTER table t move tablespace system;

表已更改。

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10179

SQL> truncate table t;

表被截斷。

SQL> select object_id , data_object_id from dba_objects where object_name='T' an
d owner='SYS';

OBJECT_ID DATA_OBJECT_ID
---------- --------------
10076 10182

SQL>

直觀感覺DATA_OBJECT_ID應該是個序列。

8、rowid的應用

1）刪除重複資料

表中出現重複資料要想刪除不是一件容易的事情，通常人們能想到的做法是給這個表加上一列，然後把流水號更新上來，我之前在sybase，sql server中就經常這樣幹，表很大的話有時候操作起來風險很大，很不方面，但是在oracle資料中透過rowid很方便的可以實現重複資料的刪除：

SQL> select t.* , rowid from t;

ID NAME ROWID
---------- ------------------ ------------------
1 AAACfGAABAAAHCaAAA
2 AAACfGAABAAAHCaAAB
1 AAACfGAABAAAHCaAAC
2 AAACfGAABAAAHCaAAD
SQL> delete from t where rowid in (select max(rowid) from t group by id);

已刪除2行。

SQL> select * from t;

ID NAME
---------- ------------------
1
2

2）rowid在index中的儲存

由於透過rowid可以準確定位每一行在datafile中的確切位置，因此透過rowid查詢資料是最有效、最直接的方式。oracle內部把它和index key一起儲存在index 的leaf node上：

SQL> create table t4(id int);

表已建立。

SQL> insert into t4 values(1);

已建立 1 行。

SQL> insert into t4 values(2);

已建立 1 行。

SQL> commit;

提交完成。

SQL> create index idx_t4 on t4(id);

索引已建立。
SQL> analyze table t4 compute statistics for all indexes;

表已分析。

SQL> select blevel , leaf_blocks from dba_indexes where index_name='IDX_T4';

BLEVEL LEAF_BLOCKS
---------- -----------
0 1

SQL> select HEADER_FILE,HEADER_BLOCK,BLOCKS from dba_segments where segment_name
='IDX_T4';

HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
4 11283 8

SQL> select file_id , block_id from dba_extents where segment_name='IDX_T4';

FILE_ID BLOCK_ID
---------- ----------
4 11281

根據對oracle邏輯結構的認識透過上面查詢得知 11283 是segment header block， 11282是二級bitmap block ， 11281是一級bitmap block（因為使用的是assm），所以斷定11284 是leaf block，dump一下看看：

SQL> alter system dump datafile 4 block 11284;

系統已更改。

主要dump內容如下：

--================================================

Start dump data blocks tsn: 4 file#: 4 minblk 11284 maxblk 11284
buffer tsn: 4 rdba: 0x01002c14 (4/11284)
scn: 0x0000.001010ca seq: 0x01 flg: 0x04 tail: 0x10ca0601
frmt: 0x02 chkval: 0xec2d type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x086A2200 to 0x086A4200
........................
Block header dump: 0x01002c14
Object id on Block? Y
seg/obj: 0x2f40 csc: 0x00.1010c9 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1002c11 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.001010c9

Leaf block dump
===============
header address 141173348=0x86a2264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 2
kdxcofbo 40=0x28
kdxcofeo 8012=0x1f4c
kdxcoavs 7972
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 00 2c 10 00 00
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 2c 10 00 01
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 11284 maxblk 11284

--================================================

透過這一段：

col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 00 2c 10 00 00
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 2c 10 00 01

清楚的知道leaf node不僅儲存了index key值，而且儲存了index key所在的行的rowid部分資訊，儲存在index leaf node上的rowid佔用了6byte，把16進位制數轉化為二進位制數和rowid對比一下看看：

16進位制：01 00 2c 10 00 00

2進位制：0000 0001 00 00 0000 0010 1100 0001 0000 0000 0000 0000 0000

前10位表示rfile＃：0000 0001 00 轉為10進位制是4

接下來的22位表示block_number:00 0000 0010 1100 0001 0000

SQL> select 8*1024+2*1024+1024+16 from dual;

8*1024+2*1024+1024+16
---------------------
11280

最後16位表示row_number：0000 0000 0000 0000 顯然是10進位制的0

再把rowid拆分一下做對比：

SQL> select dbms_rowid.rowid_relative_fno(rowid) rfile#, dbms_rowid.rowid_block_
number(rowid) block_number ,dbms_rowid.rowid_row_number(rowid) row_number from t
4;

RFILE# BLOCK_NUMBER ROW_NUMBER
---------- ------------ ----------
4 11280 0
4 11280 1

SQL>

我們此時清楚的知道在index leaf node上儲存的rowid沒有data_object_id，事實上也確實不需要儲存data_object_id，因為一個index是屬於那個表，在資料字典中是有記錄的，就像書的頁碼上不需要指定書的名字是一回事。有關index和rowid就介紹這麼多，關於分割槽index和rowid的關係大家也可以利用同樣的辦法分析。
9、rowid的儲存以及對oracle的限制

上面提到rowid採用的是64位編碼，但是內部事實上是需要10個位元組即80位來儲存rowid的，其中data_object_id佔用32 位，rfile#佔用10 位，block_number佔用22，row#佔用16 位。由此，我們可以得出：

32bit的object number，每個資料庫最多有4G個物件

10bit的rfile#，由於rfile#佔用了10位，因此每個表空間最多有1022個檔案（2個檔案預留）

22bit的block number，每個檔案最多有4M個BLOCK

16bit的row number，每個BLOCK最多有64K個ROWS

簡單的測試如下：

SQL> select id , rowid from t;

ID ROWID
---------- ------------------
1 AAACdcAABAAAHCKAAA
2 AAACdcAABAAAHCKAAB

SQL> select vsize(rowid) from t;

VSIZE(ROWID)
------------
10
10

可見實際儲存rowid卻是使用了10byte

10、rowid和字元型別相互轉化

oracle提供了2個函式可以實現rowid和字元型別相互轉化：

SQL> select id, rowid , rowidtochar(rowid) , chartorowid(rowidtochar(rowid)) fro
m t;

ID ROWID ROWIDTOCHAR(ROWID) CHARTOROWID(ROWIDT
---------- ------------------ ------------------ ------------------
1 AAACfGAABAAAHCaAAA AAACfGAABAAAHCaAAA AAACfGAABAAAHCaAAA
2 AAACfGAABAAAHCaAAB AAACfGAABAAAHCaAAB AAACfGAABAAAHCaAAB

SQL>

在最開始我的測試例子中其中有一個是把t表的rowid轉換位restricted rowid後更新到了t1表的name欄位裡了其實是oracle內部發生了隱式轉換。
.

[/php]