Tag Archives: Find the physical location of rows of a table

Understand ROWID column value in Oracle

Leave a reply

Use of ROWID function value in Oracle

ROWID is basically define as the address of the row in a particular table.
Each row has its physical address in a table. You can fetch row with following particular matrix defined as ROWID:
1. Datafiles Number
2. Block Number
3. Location of the row within Block
4. Object Number

Example of ROWID with HR.EMPLOYEES table

SQL> select rowid,employee_id from hr.employees;

ROWID              EMPLOYEE_ID
------------------ -----------
AAASCTAAMAAAADLAAA         100
AAASCTAAMAAAADLAAB         101
AAASCTAAMAAAADLAAC         102
AAASCTAAMAAAADLAAD         103
AAASCTAAMAAAADLAAE         104

Find the physical location of rows of a table

With help of the following Query, we find the file number, block number, rownum from ROWID function to generate the location of our tables rows where my table row is stored physically.

select employee_id,
dbms_rowid.rowid_to_absolute_fno(rowid,schema_name=>'HR',object_name=>'EMPLOYEES') file_num,dbms_rowid.rowid_block_number(rowid) block_num, dbms_rowid.rowid_row_number(rowid) row_num from hr.employees;

EMPLOYEE_ID   FILE_NUM  BLOCK_NUM    ROW_NUM
----------- ---------- ---------- ----------
        100         41        203          0
        101         41        203          1
        102         41        203          2
        103         41        203          3
        104         41        203          4
        105         41        203          5
        106         41        203          6
        107         41        203          7
        108         41        203          8
        109         41        203          9