oracle rowid详解
今天是2013-09-15，存储在数据库中的每一行数据都有一个地址，oracle使用rowid数据类型在存储地址。rowid有如下类别：
1）physical rowids，存储堆表、簇表以及表和索引分区中每行地址
2）logical rowids，存储索引组织表的每行的地址
3）foreign rowids，定义在外部表中。如通过gateway访问db2表，这不是标准数据rowids
在oracle数据库中有一个伪劣的叫rowid，这个伪劣像一个表中的字段一样，但是不存在表中。只能查询伪劣，但是不能够
insert、delete他们的值。
参考：
http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT88811
eg：
SQL> select rowid,grade,losal,hisal from amy;
ROWID GRADE LOSAL HISAL
------------------ ---------- ---------- ----------
AAAVw1AAHAAAACTAAA 1 700 1200
AAAVw1AAHAAAACTAAB 2 1201 1400
AAAVw1AAHAAAACTAAC 3 1401 2000
AAAVw1AAHAAAACTAAD 4 2001 3000
AAAVw1AAHAAAACTAAE 5 3001 9999
SQL>
二）rowid 结构：
oracle数据库使用rowid在数据库中唯一的定义一行。在内部，这个rowid的结构信息保留了数据库可以访问的那一行数据。
Note:
Beginning with Oracle8, Oracle SQL incorporated an extended format for rowids to efficiently support partitioned tables and indexes and tablespace-relative data block addresses without ambiguity.
查看一个网友的说法如下，挺合理的。
rowid就是唯一标志记录物理位置的一个id，在oracle 8版本以前，rowid由file# block# row#组成，占用6个bytes的空间，10 bit 的 file# ，22bit 的 block# ，16 bit 的 row#。
从oracle 8开始rowid变成了extend rowid，由data_object_id# rfile# block# row#组成，占用10个bytes的空间， 32bit的 data_object_id#,10 bit 的 rfile#,22bit 的 block#,16 bit 的 row#.由于rowid的组成从file#变成了rfile#，所以数据文件数的限制也从整个库不能超过1023个变成了每个
表空间
不能超过1023个 数据文件。
说了rowid的组成，那么我们再来看看rowid在索引里面占用的字节数又是什么样子的。在oracle 8以前索引中存储的rowid占用字节数也是6bytes,在oracle8之后，虽然oracle使用了extend rowid，但是在普通索引里面依然存储了bytes的rowid，只有在global index中存储的是10bytes的extend rowid，而extend rowid也是global index出现的一个必要条件，下面我们会解释原因。
为什么golbal index需要把data_object_id#也包含在index rowid entry中呢？如果不包含会这么样？首先我们需要知道index的rowid entry的存在是为了能根据它找到表的这条记录存在哪个具体的物理位置，我们需要知道它在哪个数据文件，在哪个block，在那一行，普通的索引 oracle根据rfile#,block#,row#就可以知道了，但是partition table可以分布在多个
表空间
，也就是可以分布在多个数据文件，当我们建立local index时，index rowid entry并不包含data_object_id#，因为oracle可以知道这个index对应的是哪一个table分区，并可以得到table分区的 ts#(tablespace号)，那么oracle根据ts#和rfile#就可以找到具体的数据文件。但是如果换成是golbal index，如果不包含data_object_id#，那么我们并不能知道这个索引对应着哪个表分区，也自然不能知道它的rfile#和file#的转 换关系，所以它将找不到所对应的记录。包含data_object_id#后,oracle可以根据data_object_id#实现rfile#和 file#的转换然后找到记录对应的物理位置。
一个扩展的rowid保护数据对象号，使用64编码指定每行数据的物理地址。这64个编码为：A-Z,a-z,0-9,+,and /.
32bit的 data_object_id#（AAAPec）,10 bit 的 rfile#（AAF),22bit 的 block#（AAABS),16 bit 的 row#.（AAA）
An extended rowid is displayed in a four-piece format,
OOOOOOFFFBBBBBBRRR
, with the format divided into the following components:
-
OOOOOOThe data object number identifies the segment (data object
AAAPecin Example 12-1 ). A data object number is assigned to every database segment. Schema objects in the same segment, such as a table cluster , have the same data object number. -
FFFThe tablespace-relative data file number identifies the data file that contains the row (file
AAFin Example 12-1 ). -
BBBBBBThe data block number identifies the block that contains the row (block
AAAABSin Example 12-1 ). Block numbers are relative to their data file, not their tablespace. Thus, two rows with identical block numbers could reside in different data files of the same tablespace. -
RRRThe row number identifies the row in the block (row
AAAin Example 12-1 ).
After a rowid is assigned to a row piece, the rowid can change in special circumstances. For example, if row movement is enabled, then the rowid can change because of partition key updates, Flashback Table operations, shrink table operations, and so on. If row movement is disabled, then a rowid can change if the row is exported and imported using Oracle Database utilities.
三）rowid data type
Rowids contain the following information:
-
The data block of the data file containing the row. The length of this string depends on your operating system.
-
The row in the data block.
-
The database file containing the row. The first data file has the number 1. The length of this string depends on your operating system.
-
The data object number , which is an identification number assigned to every database segment. You can retrieve the data object number from the data dictionary views
USER_OBJECTS,DBA_OBJECTS, andALL_OBJECTS. Objects that share the same segment (clustered tables in the same cluster, for example) have the same object number.
Rowids are stored as base 64 values that can contain the characters A-Z, a-z, 0-9, and the plus sign (+) and forward slash (/). Rowids are not available directly. You can use the supplied package
DBMS_ROWID
to interpret rowid contents. The package functions extract and provide information on the four rowid elements listed above.
另外注意索引组织表与外部表的urowid；
UROWID Data Type
The rows of some tables have addresses that are not physical or permanent or were not generated by Oracle Database. For example, the row addresses of index-organized tables are stored in index leaves, which can move. Rowids of foreign tables (such as DB2 tables accessed through a gateway) are not standard Oracle rowids.
Oracle uses universal rowids (
urowids
) to store the addresses of index-organized and foreign tables. Index-organized tables have logical urowids and foreign tables have foreign urowids. Both types of urowid are stored in the
ROWID
pseudocolumn (as are the physical rowids of heap-organized tables).
Oracle creates logical rowids based on the primary key of the table. The logical rowids do not change as long as the primary key does not change. The
ROWID
pseudocolumn of an index-organized table has a data type of
UROWID
. You can access this pseudocolumn as you would the
ROWID
pseudocolumn of a heap-organized table (using a
SELECT
...
ROWID
statement). If you want
to
store the rowids of an index-organized table, then you can define a column of type
UROWID
for the table and retrieve the value of the
ROWID
pseudocolumn into that column.
四）dbms_rowid
The
DBMS_ROWID
package lets you create
ROWIDs
and obtain information about
ROWID
s from PL/SQL programs and SQL statements. You can find the data block number, the object number, and other
ROWID
components without writing code to interpret the base-64 character external
ROWID
.
DBMS_ROWID
is intended for upgrading from Oracle database version 7 to Oracle database version 8.X.
然后看看这个包的几个function
如下：
1、rowid block number function：查看rowid的数据块号
2、rowid create function：为 了测试创建一个rowid列
3、rowid info procedure ：查看rowid中包含的类型
4、rowid object function：然后扩展rowid的对象号
5、rowid relative fno funciton：返回rowid的文件号
6、rowid row number function：返回行号
7、rowid to absolute fno function：在指定表中返回与rowid相关的绝对文件号
8、rowid to extended function：从受限的rowid转为扩展rowid
9、rowid to restricted function:从扩展rowid转为受限rowid
10、rowid type function：返回rowid的类型，1为扩展rowid，0为受限rowid
11、rowid verify function：验证从rowid_to_extended函数转换来的rowid是否正确：
eg：
我想知道amy这张表中每行数据位于哪个数据文件，那个数据块，行号是多少；
SQL> select dbms_rowid.rowid_object(rowid) as object,dbms_rowid.rowid_relative_fno(rowid) as filenum,
2 dbms_rowid.rowid_block_number(rowid) as block,dbms_rowid.rowid_row_number(rowid) as rown,grade,losal,hisal from amy;
OBJECT FILENUM BLOCK ROWN GRADE LOSAL HISAL
---------- ---------- ---------- ---------- ---------- ---------- ----------
89141 7 147 0 1 700 1200
89141 7 147 1 2 1201 1400
89141 7 147 2 3 1401 2000
89141 7 147 3 4 2001 3000
89141 7 147 4 5 3001 9999
SQL>
SQL> select object_name,object_type,created from user_objects where object_id in (select dbms_rowid.rowid_object(rowid) from amy);
OBJECT_NAME OBJECT_TYPE CREATED
-------------------------------------------------------------------------------------------------------------------------------- ------------------- ---------
AMY TABLE 15-SEP-13
SQL>
这是我们经常可能需要的信息：
另外还有一个rowid_info procedure
先看一下该 过程的源码：
如下：“
-- ROWID_INFO breaks ROWID into its components and returns them:
--
-- rowid_in - ROWID to be interpreted
-- rowid_type - type (restricted/extended)
-- object_number - data object number (rowid_object_undefined for restricted)
-- relative_fno - relative file number
-- block_number - block number in this file
-- file_number - file number in this block
-- ts_type_in - type of tablespace which this row belongs to
-- 'BIGFILE' indicates Bigfile Tablespace
-- 'SMALLFILE' indicates Smallfile (traditional pre-10i) TS.
-- NOTE: These two are the only allowed values for this param
--
procedure 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,
ts_type_in IN varchar2 default 'SMALLFILE');
pragma RESTRICT_REFERENCES(rowid_info,WNDS,RNDS,WNPS,RNPS);
好了，那么我想使用该procedure查看amy中的grade为1的相关信息如下：
SQL> declare
2 amy_rowid_type number;
amy_object_number number;
3 4 amy_relative_fno number;
5 amy_block_number number;
amy_row_number number;
6 7 begin
8 dbms_rowid.rowid_info(
9 rowid_in=>'AAAVw1AAHAAAACTAAA',
10 rowid_type=>amy_rowid_type,
11 object_number=>amy_object_number,
12 relative_fno=>amy_relative_fno,
13 block_number=>amy_block_number,
14 row_number=>amy_row_number);
15 dbms_output.put_line('rowid_type: '||amy_rowid_type);
16 dbms_output.put_line('object_number: '||amy_object_number);
17 dbms_output.put_line('relative_fno: '||amy_relative_fno);
18 dbms_output.put_line('block_number: '||amy_block_number);
19 dbms_output.put_line('row_number: '||amy_row_number);
20 dbms_output.put_line('The name is:'||'amy');
21 end ;
22 /
rowid_type: 1
object_number: 89141
relative_fno: 7
block_number: 147
row_number: 0
The name is:amy
PL/SQL procedure successfully completed.
SQL>
