Under Standing The Oracle RowID

Every row in every table has a physical address. The address of a row is determined from a combination of the following:

·         Datafile number

·         Block number

·         Location of the row within the block

·         Object number

You can display the address of a row in a table by querying the ROWID pseudo-column—for example:

SQL> select rowid, emp_id from emp;

Here’s some sample output:

ROWID EMP_ID

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

AAAFWXAAFAAAAlWAAA 1

The ROWID pseudo-column value isn’t physically stored in the database. Oracle calculates its value when

you query it. The ROWID contents are displayed as base-64 values that can contain the characters A–Z, a– z, 0–9, +, and /. You can translate the ROWID value into meaningful information via the DBMS_ROWID

package. For example,to display the file number, block number, and row number in which a row is stored,issue this statement:

select emp_id  ,

dbms_rowid.rowid_relative_fno(rowid) file_num ,

dbms_rowid.rowid_block_number(rowid) block_num

,dbms_rowid.rowid_row_number(rowid) row_num

from emp;

Here’s some sample output:

EMP_ID FILE_NUM            BLOCK_NUM ROW_NUM

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

2960         4                                144     126

2961       4                                144      127

You can use the ROWID value in the SELECT and WHERE clauses of a SQL statement. In most cases, the

ROWID uniquely identifies a row. However, it’s possible to have rows in different tables that are stored in

the same cluster and so contain rows with the same ROWID.