Oracle ROWID - its Meaning

ROWID pseudo-column provides the address of a row of a table. Hence it’s the fastest way a row can be accessed in Oracle. When you look at the plan of execution for queries that uses indexes, it shows “Table access by ROWID”. This means that the scan of the indexes has decided to get the row from a set of ROWID’s and the access of the row data will be through the ROWID value for the row.

The ROWID value contains the following information

The data object number of the object –For e.g. if the row belongs to table “Employee”, the data object number can be obtained from the query “select object_id from dba_objects where object_name=’EMPLOYEE’”

The data block number in the data file that holds the row – The block number is relative to the tablespace which holds the block.

Position of the row in the data block – This is the position of the row inside the block. If the row is the first one to get inserted in the block, the position would be zero.

The data file in the database that holds the row – This would point to the data file that holds the block. The number is directly mapped to the File# in the V$DATAFILE table.

The ROWID value, when selected directly using the queries will not display in an understandable format. We need to use dbms_rowid package to retrieve the values.

select rowid,dbms_rowid.ROWID_ROW_NUMBER(rowid) ROWPOSITION,

dbms_rowid.ROWID_OBJECT(rowid) OBJECTNUMBER,

dbms_rowid.ROWID_RELATIVE_FNO(rowid) DATAFILENUM,

dbms_rowid.ROWID_BLOCK_NUMBER(rowid) BLOCKNUMBER,

name from EMPLOYEE

ROWID	ROWPOSITION	OBJECTNUMBER	DATAFILENUM	BLOCKNUMBER	NAME
AAAcv2AAOAAANHIAAA	0	117750	14	53704	Myname

The above result with respect to the row that has the name as “Myname” is

Row is placed in the data file identified with File# as 14

Row is part of the object with the id as 117750

Row holds the first position in the block

Row is placed in the block 53704 in the tablespace.