轉載自：http://tolywang.itpub.net/post/48/22612

我們可能對oracle的rowid的使用並不陌生，不過，如果仔細分析一下，發現其還是有些知識點。

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中的行

Oracle ROWID-1

相關文章