What is the ROWID
Reference:http://www.orafaq.com/wiki/ROWID
http://www.cnblogs.com/rootq/archive/2008/10/24/1319058.html
http://www.oracleonlinux.cn/2011/11/whats-oracle-rowid/
在Oracle数据库中,每一行记录都会有一个唯一的ROWID指向磁盘中存放此记录的物理地址。一般ROWID一旦分配就不会改变,但当重组表或者使用exp/imp工具导入一个表时候,ROWID将会改变。在分区表中,因为update导致数据从一个分区到另一个分区的row Migration也会改变该记录的ROWID。
从8i开始,ROWID大小固定为10bytes,由data_object_id#+rfile#+block#+row#组成,显示问18位字符串,如下所示:
BBED@linora>select owner,rowid from t where rownum=1;
OWNER ROWID
---------- ------------------
SYSTEM AAAO3qAAJAAAAQNAAA
对以上18位字符解释如下:
The Oracle 8 format is on 10 bytes:
§ bits 1 to 32 (bytes 1 to 4): data object id(0-4294967295)
§ bits 33 to 44 (byte 5 and half byte 6): filenumber inside the tablespace (0-4095)
§ bits 45 to 64 (half byte 6 and bytes 7 and8): block number inside the file (0-1048575)
§ bits 65 to 80 (bytes 9 and 10): row numberinside the block (0-65535)
32bit的objectnumber,每个数据库最多有4G个对象
10bit的file number,每个对象最多有1022个文件(2个文件预留)
22bit的block number,每个文件最多有4M个BLOCK
16bit的row number,每个BLOCK最多有64K个ROWS
ROWID的格式如下:
数据对象编号 文件编号 块编号 行编号
OOOOOO FFF BBBBBB RRR
根据SQL查找出来的ROWID显示是18字符,这些字符是64位编码,10进制跟64进制转换如下:
64进制编码 | A | B | ... | Z | a | b | ... | z | 0 | 1 | ... | 9 | + | / |
10进制值 | 0 | 1 | ... | 25 | 26 | 27 | ... | 51 | 52 | 53 | ... | 61 | 62 | 63 |
64进制-10进制转换表 |
由此可得知:
A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)
因此10进制跟64进制编码转换公式如下:d*(b^p),其中b为基数,这里就是64,p就是从右至左,以0开始的位置数,那么上面例子中AAAO3qAAJAAAAQNAAA ,文件号AAJ具体计算应该为:
9*(64^0)=9
0*(64^1)=0
0*(64^2)=0
因此该文件号为9。块编号为AAAAQN,具体计算为(A值为0,可以不算了):
N=13*(64^0)=13
Q=16*(64^1)=1024
因此,该块编号为1037,行编号为0,可以使用DBMS_ROWID包验证以上计算结果:
select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",
DBMS_ROWID.ROWID_ROW_NUMBER(rowid) "ROW"
from bbed.t
where rownum=1
/
或者:select rowid,
dbms_rowid.rowid_object(rowid) object_id,
dbms_rowid.rowid_relative_fno(rowid) file_id,
dbms_rowid.rowid_block_number(rowid) block_id ,
dbms_rowid.rowid_row_number(rowid) num ,
rowidtochar(rowid) from bbed.t where rownum=1
;
OBJECT FILE BLOCK ROW
---------- ---------- ---------- ----------
60906 9 1037 0
也可以通过以下方式获取相关rowid信息:
create or replace function get_rowid
(l_rowid in varchar2) return varchar2 is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
dbms_rowid.rowid_info(l_rowid,
rowid_type,
object_number,
relative_fno,
block_number,
row_number);
ls_my_rowid := 'Object# is :' || to_char(object_number) || chr(10) ||
'Relative_fno is :' || to_char(relative_fno) || chr(10) ||
'Block number is :' || to_char(block_number) || chr(10) ||
'Row number is :' || to_char(row_number);
return ls_my_rowid;
end;
/
结果如下所示:
BBED@linora>select get_rowid(rowid), owner from t where rownum=1;
GET_ROWID(ROWID) OWNER
---------------------------- -------------------------------
Object# is :60906 SYSTEM
Relative_fno is :9
Block number is :1037
Row number is :0