Oracle ROWID

Oracle ROWID

ROWID为Oracle表的一个伪列，并不存储在数据库中，只是在执行查询的时候才计算它的值。

注：除了在同一聚簇中可能不唯一外，每条记录的rowid是唯一的

示例：查看rowid列

SQL> select rowid from emp;ROWID------------------AAAR3sAAEAAAACXAAAAAAR3sAAEAAAACXAABAAAR3sAAEAAAACXAACAAAR3sAAEAAAACXAADAAAR3sAAEAAAACXAAEAAAR3sAAEAAAACXAAFAAAR3sAAEAAAACXAAGAAAR3sAAEAAAACXAAHAAAR3sAAEAAAACXAAIAAAR3sAAEAAAACXAAJAAAR3sAAEAAAACXAAKAAAR3sAAEAAAACXAALAAAR3sAAEAAAACXAAMAAAR3sAAEAAAACXAANAAAR3sAAEAAAACXAAO已选择15行。

rowid包含如下内容：

①：对象所在的数据文件号

②：对象所在的块号

③：对象所在行在块内的位置

④：对象号

Oracle正是根据这些内容找到相应的数据的。

可以通过dbms_rowid包把rowid转换成易懂的内容。

示例1：看文件号

SQL> select ROWID,DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)file_id from emp;ROWID                 FILE_ID------------------ ----------AAAR3sAAEAAAACXAAA          4AAAR3sAAEAAAACXAAB          4AAAR3sAAEAAAACXAAC          4AAAR3sAAEAAAACXAAD          4AAAR3sAAEAAAACXAAE          4AAAR3sAAEAAAACXAAF          4AAAR3sAAEAAAACXAAG          4AAAR3sAAEAAAACXAAH          4AAAR3sAAEAAAACXAAI          4AAAR3sAAEAAAACXAAJ          4AAAR3sAAEAAAACXAAK          4AAAR3sAAEAAAACXAAL          4AAAR3sAAEAAAACXAAM          4AAAR3sAAEAAAACXAAN          4AAAR3sAAEAAAACXAAO          4已选择15行。

示例2：看数据块号

SQL> select ROWID,DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid)block_id from emp;ROWID                BLOCK_ID------------------ ----------AAAR3sAAEAAAACXAAA        151AAAR3sAAEAAAACXAAB        151AAAR3sAAEAAAACXAAC        151AAAR3sAAEAAAACXAAD        151AAAR3sAAEAAAACXAAE        151AAAR3sAAEAAAACXAAF        151AAAR3sAAEAAAACXAAG        151AAAR3sAAEAAAACXAAH        151AAAR3sAAEAAAACXAAI        151AAAR3sAAEAAAACXAAJ        151AAAR3sAAEAAAACXAAK        151AAAR3sAAEAAAACXAAL        151AAAR3sAAEAAAACXAAM        151AAAR3sAAEAAAACXAAN        151AAAR3sAAEAAAACXAAO        151已选择15行。

示例3：看行号

SQL> select ROWID,DBMS_ROWID.ROWID_ROW_NUMBER(rowid)row_number from emp;ROWID                ROW_NUMBER------------------ ----------AAAR3sAAEAAAACXAAA          0AAAR3sAAEAAAACXAAB          1AAAR3sAAEAAAACXAAC          2AAAR3sAAEAAAACXAAD          3AAAR3sAAEAAAACXAAE          4AAAR3sAAEAAAACXAAF          5AAAR3sAAEAAAACXAAG          6AAAR3sAAEAAAACXAAH          7AAAR3sAAEAAAACXAAI          8AAAR3sAAEAAAACXAAJ          9AAAR3sAAEAAAACXAAK         10AAAR3sAAEAAAACXAAL         11AAAR3sAAEAAAACXAAM         12AAAR3sAAEAAAACXAAN         13AAAR3sAAEAAAACXAAO         14已选择15行。

示例4:看对象号

SQL> select ROWID,DBMS_ROWID.ROWID_OBJECT(rowid)object_id from emp;ROWID               OBJECT_ID------------------ ----------AAAR3sAAEAAAACXAAA      73196AAAR3sAAEAAAACXAAB      73196AAAR3sAAEAAAACXAAC      73196AAAR3sAAEAAAACXAAD      73196AAAR3sAAEAAAACXAAE      73196AAAR3sAAEAAAACXAAF      73196AAAR3sAAEAAAACXAAG      73196AAAR3sAAEAAAACXAAH      73196AAAR3sAAEAAAACXAAI      73196AAAR3sAAEAAAACXAAJ      73196AAAR3sAAEAAAACXAAK      73196AAAR3sAAEAAAACXAAL      73196AAAR3sAAEAAAACXAAM      73196AAAR3sAAEAAAACXAAN      73196AAAR3sAAEAAAACXAAO      73196已选择15行。----验证一下，正确SQL> col object_name for a30SQL> select object_id,object_name from user_objects where object_name='EMP'; OBJECT_ID OBJECT_NAME---------- ------------------------------     73196 EMP

或用这个更方便

SQL> create or replace function get_rowid  2  (l_rowid in varchar2)  3   return varchar2  4   is  5   ls_my_rowid     varchar2(200);  6  rowid_type     number;  7  object_number     number;  8  relative_fno     number;  9  block_number     number; 10  row_number     number; 11 12  begin 13    dbms_rowid.rowid_info(l_rowid,rowid_type,object_number,relative_fno, block_number, row_number); 14   ls_my_rowid := 'Object# is      :'||to_char(object_number)||chr(10)|| 15           'Relative_fno is :'||to_char(relative_fno)||chr(10)|| 16           'Block number is :'||to_char(block_number)||chr(10)|| 17           'Row number is   :'||to_char(row_number); 18    return ls_my_rowid ; 19   end; 20 21  /函数已创建。

试用一下，不错。

SQL> col content for a30SQL> select empno,get_rowid(rowid) as content  from emp where empno=7788;     EMPNO CONTENT---------- ------------------------------      7788 Object# is      :73196           Relative_fno is :4           Block number is :151           Row number is   :7

更多参考：

SubprogramDescription

ROWID_BLOCK_NUMBER Function

Returns the block number of a ROWID

ROWID_CREATE Function

Creates a ROWID, for testing only

ROWID_INFO Procedure

Returns the type and components of a ROWID

ROWID_OBJECT Function

Returns the object number of the extended ROWID

ROWID_RELATIVE_FNO Function

Returns the file number of a ROWID

ROWID_ROW_NUMBER Function

Returns the row number

ROWID_TO_ABSOLUTE_FNO Function

Returns the absolute file number associated with the ROWID for a row in a specific table

ROWID_TO_EXTENDED Function

Converts a ROWID from restricted format to extended

ROWID_TO_RESTRICTED Function

Converts an extended ROWID to restricted format

ROWID_TYPE Function

Returns the ROWID type: 0 is restricted, 1 is extended

ROWID_VERIFY Function

Checks if a ROWID can be correctly extended by the ROWID_TO_EXTENDED function