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> create table TAB1 as select rownum id, rpad(‘x’,100) c1, rpad(‘y’,200) c2 from dba_objects where rownum<10;

Table created.

1.PNG

 

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 theDBMS_ROWID package.

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

SQL> select 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 tab1;

ID FILE_NUM BLOCK_NUM ROW_NUM
———- ———- ———- ———-
1     1          101705                             0
2     1          101705                            1
3      1          101705                            2
4      1          101705                              3
5      1           101705                             4
6     1           101705                              5
7     1           101705                             6
8     1           101705                              7
9     1           101705                              8

9 rows selected.

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s