oracle常用包之dbms_rowid oracle rowid含義分析

如果我們查詢一個表的ROWID，就可以獲得object的資訊，檔案資訊，塊資訊與行資訊等等，如根據其中塊的資訊，可以知道該表確切佔用了多少個塊，每行在哪個塊上，哪個資料檔案上。

SQL> select rowid from t;

ROWID
------------------
AAANkiAAGAAAAZfAAA
AAANkiAAGAAAAZfAAB

拿第一個rowid AAANkiAAGAAAAZfAAA分解一下看到如下

Data Object number = AAANki
File               = AAG
Block              = AAAAZf
ROW                = AAA

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

DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------
1631
1631

SQL> select header_file, header_block, blocks from dba_segments where segment_name = 'T';

HEADER_FILE HEADER_BLOCK BLOCKS
----------- ------------ ----------
6 1627 8

另外，我們需要注意的是，ROWID是64進位制的，分佈關係如下

A-Z <==> 0 - 25 (26)
a-z <==> 26 - 51 (26)
0-9 <==> 52 - 61 (10)
+/ <==> 62 - 63 (2)

SQL> --AAANki=55586 AAG=6 AAAAZf=1631 AAA=0

SQL> select 25*64 + 31 from dual;

25*64+31
----------
1631

SQL> select 13*64*64 + 36*64 +34 from dual;

13*64*64+36*64+34
-----------------
55586

SQL> select object_id from user_objects where object_name ='T';

OBJECT_ID
----------
55586

也可以透過dbms_rowid包來查詢

SQL> select dbms_rowid.rowid_object('AAANkiAAGAAAAZfAAA') data_object_id#,
2 dbms_rowid.rowid_relative_fno('AAANkiAAGAAAAZfAAA') rfile#,
3 dbms_rowid.rowid_block_number('AAANkiAAGAAAAZfAAA') block#,
4 dbms_rowid.rowid_row_number('AAANkiAAGAAAAZfAAA') row# from dual;

DATA_OBJECT_ID# RFILE# BLOCK# ROW#
--------------- ---------- ---------- ----------
55586 6 1631 0[@more@]