Understand ROWID column value in Oracle

Understand 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

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.