Oracle rowid

Oracle 使用 rowid 来定位数据库中的一条记录(行). 

rowid 一共 18 位, 是一个基于 64 进制的物理地址, 每个字符的取值为 A-Z, a-z, 0-9, + 和 /

SQL> select rowid, dept.* from dept where deptno = 10;

ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AAATgAAAFAAAACHAAA         10 ACCOUNTING     NEW YORK

AAATgA ---   data object number对象号 (占 6 个字符) 

AAF ---   relative file number 文件号 (占 3 个字符) 

AAAACH ---   block number 块号 (占 6 个字符)

AAA      ---   row number 行号   (占 3 个字符)

使用 dbms_rowid 包获取 object number, file number, block number, row number

SQL> select dbms_rowid.rowid_object(rowid) object_id,
  2  dbms_rowid.rowid_relative_fno(rowid) file_id,
  3  dbms_rowid.rowid_block_number(rowid) block_id,
  4  dbms_rowid.rowid_row_number(rowid) num
  5  from dept
  6  where deptno = 10;


 OBJECT_ID    FILE_ID   BLOCK_ID        NUM
---------- ---------- ---------- ----------
     79872          5        135          0


SQL> select object_name from dba_objects where object_id = 79872;


OBJECT_NAME
------------------------------
DEPT


SQL> select file_name from dba_data_files where file_id = 5;


FILE_NAME
------------------------------------------------------------------------


G:\APP\DAVIDD\PRODUCT\11.2.0\DBHOME_1\ORADATA\SCOTT_TBS01.DBF

当一条记录被赋予一个 rowid 后, rowid 只有在特殊的情况下才会改动. 例如 flashback table 操作, 表压缩操作, row 移动操作, 以及 row 导入导出操作等等

SQL> select rowid,
  2  deptno,
  3  dbms_rowid.rowid_object(rowid) object_id,
  4  dbms_rowid.rowid_relative_fno(rowid) file_id,
  5  dbms_rowid.rowid_block_number(rowid) block_id,
  6  dbms_rowid.rowid_row_number(rowid) num
  7  from dept;


ROWID                  DEPTNO  OBJECT_ID    FILE_ID   BLOCK_ID        NUM
------------------ ---------- ---------- ---------- ---------- ----------
AAATgAAAFAAAACHAAA         10      79872          5        135          0
AAATgAAAFAAAACHAAB         20      79872          5        135          1
AAATgAAAFAAAACHAAC         30      79872          5        135          2
AAATgAAAFAAAACHAAD         40      79872          5        135          3


SQL> alter table dept move pctfree 15;


Table altered.


SQL> select rowid,
  2  deptno,
  3  dbms_rowid.rowid_object(rowid) object_id,
  4  dbms_rowid.rowid_relative_fno(rowid) file_id,
  5  dbms_rowid.rowid_block_number(rowid) block_id,
  6  dbms_rowid.rowid_row_number(rowid) num
  7  from dept;


ROWID                  DEPTNO  OBJECT_ID    FILE_ID   BLOCK_ID        NUM
------------------ ---------- ---------- ---------- ---------- ----------
AAATmKAAFAAAACbAAA         10      80271          5        155          0
AAATmKAAFAAAACbAAB         20      80271          5        155          1
AAATmKAAFAAAACbAAC         30      80271          5        155          2
AAATmKAAFAAAACbAAD         40      80271          5        155          3

在这里我们可以看出 table move 后, 不仅 rowid 发生了变化, object_id (对象名) 值也发生了变化. 

SQL> col object_name for a30
SQL> select object_name, object_id, data_object_id from dba_objects where object
_id = 79872;


OBJECT_NAME                     OBJECT_ID DATA_OBJECT_ID
------------------------------ ---------- --------------
DEPT                                79872          80271

补充说明: 

dba_objects 中 object_id: Object number of the object
data_object_id: Object number of the segment which contains the object

对于 table 来说, object_id 和 data_object_id 大部分情况下是相等的, 但是如果 table 发生 move, shrink, truncate 情况时, 会重新分配 data segment, 这时 data_object_id 会指向新的 data segment.