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