Oracle Rowid 介绍

 

一. 官网说明

       owid的定义：A globally unique address for a row in a database.

 

       rowid 分为extended rowid 和 restricted rowied.

 

1.1 Restricted ROWID

       Internally, the ROWID is a structure that holds information that the database server needs to access a row. The restricted internal ROWID is 6 bytes on most platforms.

 

Each restricted rowid includes the following data:

       （1）Datafile identifier

       （2）Block identifier

       （3）Row identifier

 

       The restricted ROWID pseudocolumn is returned to client applications in the form of an 18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the ROWID.

 

1.2  Extended ROWID

       The extended ROWID datatype includes the data in the restricted rowid plus a data object number. The data object number is an identification number assigned to every database segment. The extended internal ROWID is 10 bytes on most platforms.

       Data in an extended ROWID pseudocolumn is returned to the client application in the form of an 18-character string (for example, "AAAA8mAALAAAAQkAAA"), which represents a base 64 encoding of the components of the extended ROWID in a four-piece format, OOOOOOFFFBBBBBBRRR. Extended rowids are not available directly. You can use a supplied package, DBMS_ROWID, to interpret extended rowid contents. The package functions extract and provide information that would be available directly from a restricted rowid as well as information specific to extended rowids.

 

1.3 Rowid Format

       Oracle Database uses a rowid to uniquely identify a row. Internally, the rowid is a structure that holds information that the database needs to access a row. A rowid is not physically stored in the database, but is inferred from the file and block on which the data is stored.

       An extended rowid includes a data object number. This rowid type uses a base 64 encoding of the physical address for each row. The encoding characters are A-Z, a-z, 0-9, +, and /.

 

Example 12-1 queries the ROWID pseudocolumn to show the extended rowid of the row in the employees table for employee 100.

Example 12-1 ROWID Pseudocolumn

SQL> SELECT ROWID FROM employees WHERE employee_id = 100;

 

ROWID

------------------

AAAPecAAFAAAABSAAA

 

Figure 12-8 illustrates the format of an extended rowid.

Figure 12-8 ROWID Format

An extended rowid is displayed in a four-piece format, OOOOOOFFFBBBBBBRRR, with the format divided into the following components:

 

（1）OOOOOO

       The data object number identifies the segment (data object AAAPec in 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.

（2）FFF

       The tablespace-relative data file number identifies the data file that contains the row (file AAF in Example 12-1).

（3）BBBBBB

       The data block number identifies the block that contains the row (block AAAABS in 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.

（4）RRR

       The row number identifies the row in the block (row AAA in Example 12-1).

 

 

       After a rowid is assigned to a row piece, the rowid can change in special circumstances. For example,

（1）if row movement is enabled, then the rowid can change because of partition key updates, Flashback Table operations, shrink table operations, and so on.

（2）If row movement is disabled, then a rowid can change if the row is exported and imported using Oracle Database utilities.

-- rowid 改变的条件

 

Note:

       Internally, the database performs row movement as if the row were physically deleted and reinserted. However, row movement is considered an update, which has implications for triggers.

 

 

二.  rowid 说明

 

       rowid是伪列（pseudocolumn），伪劣的意思是实际上这一列本身在数据字典中并不存在，在查询结果输出时它被构造出来的。

       rowid并不会真正存在于表的data block中,但是他会存在于index当中，用来通过rowid来寻找表中的行数据。

 

2.1 利用rowid来得到相关信息

SQL> conn sys/admin as sysdba

已连接。

SQL> create table bl(id number);

表已创建。

SQL> insert into bl values(1);

已创建 1 行。

 

SQL> select owner,segment_name,file_id,RELATIVE_FNO,block_id from dba_extents where owner='SYS' and segment_name='BL';

OWNER SEGMENT_NA   FILE_ID  RELATIVE_FNO   BLOCK_ID

-----    ----------           ----------    ------------          ----------

SYS    BL                  1           1            62129

 

SQL> desc dbms_rowid.rowid_info

PACKAGE SYS.DBMS_ROWID

PROCEDURE ROWID_INFO

 Argument Name                  Type                    In/Out

 ------------------------------ ----------------------- ------

 ROWID_IN                       ROWID                   IN  

 ROWID_TYPE                     NUMBER                  OUT  

 OBJECT_NUMBER                  NUMBER                  OUT  

 RELATIVE_FNO                   NUMBER                  OUT  

 BLOCK_NUMBER                   NUMBER                  OUT  

 ROW_NUMBER                     NUMBER                  OUT  

 TS_TYPE_IN                     VARCHAR2                IN

 

     

SQL> set serveroutput on

SQL>DECLARE

   v_rowid_type          NUMBER;

   v_OBJECT_NUMBER       NUMBER;

   v_RELATIVE_FNO        NUMBER;

   v_BLOCK_NUMBERE_FNO   NUMBER;

   v_ROW_NUMBER          NUMBER;

BEGIN

   DBMS_ROWID.rowid_info (

                               rowid_in        => 'AAAJVnAANAAAACiAAA',

                 rowid_type      => v_rowid_type,

                 object_number   => v_OBJECT_NUMBER,

                 relative_fno    => v_RELATIVE_FNO,

                 block_number    => v_BLOCK_NUMBERE_FNO,

                 ROW_NUMBER      => v_ROW_NUMBER);

   DBMS_OUTPUT.put_line ('ROWID_TYPE:  ' || TO_CHAR (v_rowid_type));

DBMS_OUTPUT.put_line ('OBJECT_NUMBER:  ' || TO_CHAR (v_OBJECT_NUMBER));

   DBMS_OUTPUT.put_line ('RELATIVE_FNO:  ' || TO_CHAR (v_RELATIVE_FNO));

   DBMS_OUTPUT.put_line ('BLOCK_NUMBER:  ' || TO_CHAR (v_BLOCK_NUMBERE_FNO));

   DBMS_OUTPUT.put_line ('ROW_NUMBER:  ' || TO_CHAR (v_ROW_NUMBER));

END;

/

 

结果：

ROWID_TYPE:  1

OBJECT_NUMBER:  38247

RELATIVE_FNO:  13

BLOCK_NUMBER:  162

ROW_NUMBER:  0

 

2.2 . Rowid的结构

 

 

 

ROWID 格式:

       扩展的ROWID 在磁盘上需要10 个字节的存储空间，并使用18 个字符来显示。

 

它包含下列组成元素：

       1. 数据对象编号：每个数据对象（如表或索引）在创建时都分配有此编号，并且此编号在数据库中是唯一的

       2. 相关文件编号：此编号对于表空间中的每个数据文件是唯一的

       3. 块编号：表示包含此行的块在数据文件中的位置

       4. 行编号：标识块头中行目录位置的位置

 

在内部，存储的10个字节(bytes)，即80位(bit)又按如下规则进行划分：

       （1）数据对象编号需要32 bit

       （2）相关文件编号需要10 bit

       （3）块编号需要22 bit

       （4）行编号需要16 bit

 

 

       在oracle 8以前，一个rowid占用6个字节大小的存储空间(10bit file#+22bit block#+16bit row#), rowid格式为:BBBBBBBB.RRRR.FFFF。

       在oracle 8以后， rowid的存储空间扩大到了10个字节(32bit object#+10bit rfile#+22bit block#+16bit row#),文件号仍然用10位表示,只是不再需要置换,为了向后兼容,同时引入了相对文件号(rfile#),所以从Oracle7到Oracle8,Rowid仍然无需发生变化.

 

       Rdba(Tablespace relative database block address)就是rowid中的rfile#+block#.

 

       rowid这样改变之后，数据库中数据库文件个数的限制从整个数据库最多只能有的2^10-2=1022个数据文件(去掉全0和全1), 变为了每个表空间中可以最多有2^10-2个数据文件。

       所以说，数据库能支持的数据文件最大数是受rowid的长度限制的。

 

       需要注意的是: local index中存储的rowid是6个字节，而global index中存储的rowid是10个字节。

 

       那么增加的32bit object# 这个前缀主要就是用来定位表空间的，同时这个object#其实对应的就是data_object_id，由于一个段对象只能属于一个表空间，同时data_object_id就是标识了一个段的物理存储id.因此object#+rfile#就可以唯一定位当前的rowid是在那个数据文件上了。

 

 

可以通过dbms_rowid这个包来转换我们的rowid成不同组成部分：

       dbms_rowid.rowid_object(rowid)---> 32bit object#
       dbms_rowid.rowid_relative_fno(rowid)---> 10bit rfile#
       dbms_rowid.rowid_block_number(rowid)---> 22bit block#
       dbms_rowid.rowid_row_number(rowid)---> 16bit row#

 

       扩展的ROWID 使用以64 为基数的编码方案来显示，该方案将六个位置用于数据对象编号、三个位置用于相关文件编号、六个位置用于块编号、三个位置用于行编号。

       以64 为基数的编码方案使用字符“A-Z”、“a-z”、“0-9” 和“/”。共有64 个字符，如下例所示：

 

SQL> SELECT department_id, rowid FROM hr.departments;

     EMPNO ROWID            

---------- ------------------

      7488 AAAMfPAAEAAAAAgAAA

      7499 AAAMfPAAEAAAAAgAAB

      7521 AAAMfPAAEAAAAAgAAC

      7566 AAAMfPAAEAAAAAgAAD

      7654 AAAMfPAAEAAAAAgAAE

      7698 AAAMfPAAEAAAAAgAAF

      7782 AAAMfPAAEAAAAAgAAG

      7788 AAAMfPAAEAAAAAgAAH

      7839 AAAMfPAAEAAAAAgAAI

      7844 AAAMfPAAEAAAAAgAAJ

      7876 AAAMfPAAEAAAAAgAAK

      7900 AAAMfPAAEAAAAAgAAL

 

在本例中：

       AAAMfP 是数据对象编号

       AAE 是相关文件编号

       AAAAAg是块编号

       AAA是EMPNO=7488 的部分的行编号

 

其他内容参考：

       Oracle 数据块 Block 说明

       http://blog.csdn.net/tianlesoftware/archive/2011/05/12/6414765.aspx

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

Blog： http://blog.csdn.net/tianlesoftware

Email: dvd.dba@gmail.com

DBA1 群：62697716(满);   DBA2 群：62697977(满)   DBA3 群：62697850(满)  

DBA 超级群：63306533(满);  DBA4 群： 83829929  DBA5群： 142216823   

DBA6 群：158654907  聊天 群：40132017   聊天2群：69087192

--加群需要在备注说明Oracle表空间和数据文件的关系，否则拒绝申请

转载于:https://www.cnblogs.com/tianlesoftware/archive/2009/12/16/3610303.html