北极星Polestar Peter 修身、齐家、治国、平天下

racle常用包之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