rowid详细介绍

rowid是一个伪列，rowid代表着数据行的地址。oracle的rowid值包含了定位一个行的必要信息，包括：
1.数据库对象号(1-6)
2.行所在数据文件号（7-9）
3.行所在数据文件中的块号（10-15）
4.行所在数据块中的位置（16-18）

XXXXXX	XXX	XXXXXX	XXX
Data Object ID	Relative File No	Block Number	Row Number(Slot)

SQL> select rowid from rowid_example;

ROWID
------------------
AAANdqAABAAAPFyAAA

AAANdq	AAB	AAAPFy	AAA
Data Object ID	Relative File No	Block Number	Row Number(Slot)

rowdi是64位进制编码格式，对应关系如下：

Decimal Sequence	Base-64 Representation
0 – 25	‘A’ – ‘ Z’ (A through Z)
26 – 51	‘a’ – ‘z’ (a through z)
52 – 61	‘0’ – ‘9’ (0 through 9)
62	‘+’
63	‘/’

(AAANdq) -> (000000 000000 000000 001101 011101 101010)  => (Base-64) -> (Binary)

2. Convert the binary representation to decimal value to

(000000000000000000001101011101101010) -> (55146) => (Binary) -> (Decimal)

SQL>select data_object_id from dba_objects where object_name='ROWID_EXAMPLE';

DATA_OBJECT_ID
--------------
55146

3. Similarly we can convert the Relative File Number, Block Number and the Row Slot (Number).

Read the conversion below as => (Base-64) -> (Binary) -> (Decimal)

(AAB) -> (000000 000000 000001) -> (1) 
(AAAPFy) -> (000000 000000 000000 001111 000101 110010) -> (61810)
(AAA) -> (000000 000000 000000) -> (0)

Using the Relative File Number and the Block Number in the following query we can get the Object Name.

SQL> select segment_name, segment_type, owner from dba_extents where file_id=1 and 61810 between block_id and block_id + blocks - 1;

SEGMENT_NAME        SEGMENT_TYPE   OWNER
-------------------------- ------------------     --------------
ROWID_EXAMPLE       TABLE                    SYS

在oracle中可以通过dbms_rowid包把rowid转换成易懂的内容
1.查看行的文件号
select ROWID,DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) file_id from emp; 
ROWID                             FILE_ID 
------------------                     ----------  
AAAR3sAAEAAAACXAAA          4 
AAAR3sAAEAAAACXAAB          4 
2.查看行的数据块号
select ROWID,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)block_id from emp; 
ROWID                            BLOCK_ID  
------------------                     ---------- 
AAAR3sAAEAAAACXAAA        151  
AAAR3sAAEAAAACXAAB        151 
3.查看行号
select ROWID,DBMS_ROWID.ROWID_ROW_NUMBER(rowid)row_number from emp;
ROWID                            ROW_NUMBER 
------------------                     ----------  
AAAR3sAAEAAAACXAAA          0 
AAAR3sAAEAAAACXAAB          1
4.看对象号
select ROWID,DBMS_ROWID.ROWID_OBJECT(rowid)object_id from emp; 
ROWID                           OBJECT_ID  
------------------                     ----------  
AAAR3sAAEAAAACXAAA      73196  
AAAR3sAAEAAAACXAAB      73196
select object_id,object_name from user_objects where object_name='EMP'
 OBJECT_ID     OBJECT_NAME  
----------         ------------------------------  
73196             EMP 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30291162/viewspace-1705706/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30291162/viewspace-1705706/