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;
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.