1、rowid是一个伪列，是用来确保表中行的唯一性，它并不能指示出行的物理位置，但可以用来定位行。 2、rowid是存储在索引中的一组既定的值（当行确定后）。我们可以像表中普通的列一样将它选出来。
3、利用rowid是访问表中一行的最快方式。
4、rowid需要10个字节来存储，显示为18位的字符串。
rowid的组成结构为：
data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串)，如：AAAADeAABAAAAZSAAA
5、我们可以借助oracle提供的包dbms_rowid，来对rowid进行解析从而获取关于行的相关信息：
bossdb-SQL>select
2 rowid,
3 dbms_rowid.rowid_object(rowid) obj_id,
4 dbms_rowid.rowid_relative_fno(rowid) df#,
5 dbms_rowid.rowid_block_number(rowid) blknum,
6 dbms_rowid.rowid_row_number(rowid) rowno
7 from p_test where rownum<5;
ROWID OBJ_ID DF# BLKNUM ROWNO
------------------ ---------- ---------- ---------- ----------
AAAQ+tAANAAAC6SAAA 69549 13 11922 0 AAAQ+tAANAAAC6SAAB 69549 13 11922 1 AAAQ+tAANAAAC6SAAC 69549 13 11922 2 AAAQ+tAANAAAC6SAAD 69549 13 11922 3
我们可以看到，通过rowid_row_number得到的行号是从0开始的，这是和rownum伪列的一个不同之处。我猜测rowid_row_number在求行号的时候是计算首行的偏移量。
一般来说，当表中的行确定后，rowid就不会发生变化。
但当如下情况发生时，rowid将发生改变：
1、对一个表做表空间的移动后
2、对一个表进行了EXP/IMP后
ROWID
For each row in the database, the ROWID pseudocolumn returns a row's
address. ROWID values contain information necessary to locate a
row:
* which data block in the data file
* which row in the data block (first row is 0)
* which data file (first file is 1)
In most cases, a ROWID value uniquely identifies a row in the
database. However, rows in different tables that are stored
together in the same cluster can have the same ROWID.
Values of the ROWID pseudocolumn have the datatype ROWID.
ROWID values have several important uses:
* They are the fastest means of accessing a single row.
* They can show you how a table's rows are stored.
* They are unique identifiers for rows in a table.
A ROWID does not change during the lifetime of its row. However,
you should not use ROWID as a table's primary key. If you delete
and reinsert a row with the Import and Export utilities, for
example, its ROWID may change. If you delete a row, Oracle may
reassign its ROWID to a new row inserted later.
Although you can use the ROWID pseudocolumn in the SELECT and WHERE
clauses of a query, these pseudocolumn values are not actually
stored in the database. You cannot insert, update, or delete a
value of the ROWID pseudocolumn.
EXAMPLE:
This statement selects the address of all rows that contain data for
employees in department 20:
SELECT ROWID, ename
FROM emp
WHERE deptno = 20
ROWID ENAME
----------------- ----------
0000000F.0000.0002 SMITH
0000000F.0003.0002 JONES
0000000F.0007.0002 SCOTT
0000000F.000A.0002 ADAMS
0000000F.000C.0002 FORD
-----------------------------------------------------------------------------
DBMS_ROWID包的使用
http://blog.itpub.net/post/3/7835
DBMS_ROWID是一个比较有用的系统自带的package，主要可以用来处理坏块的问题，于是仔细的研究了一下，这个包可以用来了解file、block、object id和rowid之间的关系，在Oracle8中被引用进来，Oracle7不支持这个包。这个包的定义可以在dbmsutil.sql中找到，在catproc.sql中被调用，并被给予public执行权限。
首先来了解一下这个包中使用的常量：
ROWID类型：
rowid_type_restricted RESTRICTED - Restricted ROWID
rowid_type_extended EXTENDED - Extended ROWID
ROWID验证结果：
rowid_is_valid VALID - Valid ROWID
rowid_is_invalid INVALID - Invalid ROWID
目标类型：
rowid_object_undefined UNDEFINED - Object Number not defined (for restricted ROWIDs)
ROWID转换类型：
rowid_convert_internal INTERNAL - convert to/from column of ROWID type
rowid_convert_external EXTERNAL - convert to/from string format
意外错误：
ROWID_INVALID invalid rowid format
ROWID_BAD_BLOCK block is beyond end of file
在DBMS_ROWID这个包里面可以使用下面的功能：
function ROWID_CREATE(rowid_type IN number,
object_number IN number,
relative_fno IN number,
block_number IN number,
row_number IN number)
return ROWID;
-- rowid_type - 类型(restricted=0/extended=1)
-- object_number - 对象号
-- relative_fno - relative file number
-- block_number - 文件包含的block号
-- row_number - block中的行的行号
下面具体的讨论一下DBMS_ROWID包的用法：
1． DBMS_ROWID.ROWID_BLOCK_NUMBER:返回一个rowid的block号
定义如下：
function dbms_rowid.rowid_block_number
(row_id in rowid)
return number
SQL> select dbms_rowid.rowid_block_number(rowid) "block" from test;
block
----------
23722
2． DBMS_ROWID.ROWID_CREATE:创建并返回一个基于单独行的rowid，创建的rowid类型是RESTRICTED或者是EXTENDED，这种功能一般都是用于测试目的，因为只有oracle才能创建一个合法的rowid指向数据。
定义如下：
function dbms_rowid.rowid_create
(rowid_type in number
,object_number in number
,relative_fno in number
,block_number in number
,row_number in number)
return rowid
例子：
创建一个restricted rowid：
SQL> select dbms_rowid.rowid_create(0, 6877,1,23722,0) from dual;
DBMS_ROWID.ROWID_C
------------------
00005CAA.0000.0001
创建一个extended rowid：
SQL> select dbms_rowid.rowid_create(1, 6877,1,23722,0) from dual;
DBMS_ROWID.ROWID_C
------------------
AAABrdAABAAAFyqAAA
3． DBMS_ROWID.ROWID_INFO: 返回一个单独组件的一个指定的rowid，它只能用于PL/SQL,而不能用于sql语句中。
定义如下：
procedure dbms_rowid.rowid_info
(rowid_in in rowid
,rowid_type out number
,object_number out number
,relative_fno out number
,block_number out number
,row_number out number)
例子：
SQL> set serverout on
SQL> set echo on
SQL> declare
2 my_rowid rowid;
3 rowid_type number;
4 object_number number;
5 relative_fno number;
6 block_number number;
7 row_number number;
8 begin
9 my_rowid :=dbms_rowid.rowid_create(1, 6877,1,23722,0);
10 dbms_rowid.rowid_info(my_rowid, rowid_type, object_number,
11 relative_fno, block_number, row_number);
12 dbms_output.put_line('ROWID: ' || my_rowid);
13 dbms_output.put_line('Object#: ' || object_number);
14 dbms_output.put_line('RelFile#: ' || relative_fno);
15 dbms_output.put_line('Block#: ' || block_number);
16 dbms_output.put_line('Row#: ' || row_number);
17 end;
18 /
ROWID: AAABrdAABAAAFyqAAA
Object#: 6877
RelFile#: 1
Block#: 23722
Row#: 0
PL/SQL 过程已成功完成。
4.DBMS_ROWID.ROWID_OBJECT:返回一个rowid的对象号。如果是restricted 的rowid，则返回0。
定义如下：
function dbms_rowid.rowid_object
(row_id in rowid)
return number
例子：
SQL> select dbms_rowid.rowid_object(rowid) "OBJECT" from test;
OBJECT
----------
6877
SQL> select dbms_rowid.rowid_object(dbms_rowid.rowid_to_restricted(rowid,0)) " OBJECT " from test;
OBJECT
----------
0
5． DBMS_ROWID.ROWID_RELATIVE_FNO: 返回一个rowid的相对文件号。
定义如下：
function dbms_rowid.rowid_relative_fno
(row_id in rowid)
return number
例子：
SQL> select dbms_rowid.rowid_relative_fno(rowid) "relative fno" from test;
relative fno
------------
1
6． DBMS_ROWID.ROWID_ROW_NUMBER:返回一个rowid的行号。（从零开始）
定义如下：
function dbms_rowid.rowid_row_number
(row_id in rowid)
return number
例子：
SQL> select dbms_rowid.rowid_row_number(rowid) "row" from test;
row
----------
0
7． DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO：返回一个rowid的完全文件号。
定义如下：
function dbms_rowid.rowid_to_absolute_fno
(rowid in rowid
,schema_name in varchar2
,object_name in varchar2)
return number
例子：
SQL> select dbms_rowid.rowid_to_absolute_fno (rowid, 'SYS', 'TEST') "absolute fno" from test;
absolute fno
------------
1
8． DBMS_ROWID.ROWID_TO_EXTENDED: 转换一个restricted rowid为一个extended rowid.如果原始的rowid存储在列中，转换的 就是internal类型；如果原始的rowid是以字符串形式存储的，那转换的就是external类型。
定义如下：
function dbms_rowid.rowid_to_extended
(old_rowid in rowid
,schema_name in varchar2
,object_name in varchar2
,conversion_type in integer)
return rowid
例子：
转换restricted internal rowid为extended格式
SQL>select dbms_rowid.rowid_to_extended (dbms_rowid.rowid_to_restricted(rowid,0),'SYS','TEST',0) "extended rowid" from test;
extended rowid
------------------
AAABrdAABAAAFyqAAA
转换restricted external rowid为extended格式
SQL> select dbms_rowid.rowid_to_extended ('00005CAA.0000.0001','SYS','TEST',1) from dual;
DBMS_ROWID.ROWID_T
------------------
AAABrdAABAAAFyqAAA
如果参数中的SCHEMA和OBJECT为null，则默认是当前的对象
SQL>select dbms_rowid.rowid_to_extended (dbms_rowid.rowid_to_restricted(rowid,0),null,null,0) "extended rowid" from test;
extended rowid
------------------
AAABrdAABAAAFyqAAA
9． DBMS_ROWID.ROWID_TO_RESTRICTED:转换一个exteneded的rowid为一个restricted的rowid，restricted的rowid格式为BBBBBBB.RRRR.FFFFF, BBBBBBB代表block，RRRR 代表在block中的行
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-84309/，如需转载，请注明出处，否则将追究法律责任。
