Oracle Rowid 介绍-优快云博客

本文详细介绍了Oracle数据库中的RowID概念，包括其内部结构、格式及如何使用DBMS_ROWID包解析RowID。此外还探讨了RowID的变化条件以及扩展RowID的特点。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一.官网说明

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 an18-character string with a hexadecimal encoding of the datablock, row, and datafile components of the ROWID.

1.2Extended ROWID

The extended ROWID datatypeincludes 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 ofan 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 canuse 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.3Rowid Format

Oracle Database uses arowidto 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-1queries the ROWIDpseudocolumnto 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-8illustrates 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

Thedata object numberidentifies the segment (data object AAAPec inExample 12-1). A data object number is assigned to every database segment. Schema objects in the same segment, such as atable cluster, have the same data object number.

（2）FFF

Thetablespace-relative data file numberidentifies the data file that contains the row (file AAF inExample 12-1).

（3）BBBBBB

Thedata block numberidentifies the block that contains the row (block AAAABS inExample 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

Therow numberidentifies the row in the block (row AAA inExample 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_NAFILE_IDRELATIVE_FNOBLOCK_ID

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

SYSBL1162129

SQL> desc dbms_rowid.rowid_info

PACKAGE SYS.DBMS_ROWID

PROCEDURE ROWID_INFO

Argument NameTypeIn/Out

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

ROWID_INROWIDIN

ROWID_TYPENUMBEROUT

OBJECT_NUMBERNUMBEROUT

RELATIVE_FNONUMBEROUT

BLOCK_NUMBERNUMBEROUT

ROW_NUMBERNUMBEROUT

TS_TYPE_INVARCHAR2IN

SQL> set serveroutput on

SQL>DECLARE

v_rowid_typeNUMBER;

v_OBJECT_NUMBERNUMBER;

v_RELATIVE_FNONUMBER;

v_BLOCK_NUMBERE_FNONUMBER;

v_ROW_NUMBERNUMBER;

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