Oracle pseudo column RowID

ROWID stores the address of the row in the database, and it uses ROWIDs to construct index. But you can also use rowid to see how a table is organized. This part is often missed by many people.

 

Oracle has two type of ROWID:

Extended ROWID: Extendeds RowID use base 64 encoding and have the following components:

  data object number: identify the database segment of the object containing the required row

  relative file number: identify which data file in the segment contained the required row

  block number: identify which block in the data file contained the required row

  row number: slot number of the row in the block

Restricted ROWID: Restricted ROWID use a binary representation and contain the following components:

  Block number

  Row number

  file number

 Oracle provides a package to parse the rowid. - dbms_rowid. it has the function like:

   dbms_rowid.rowid_block_number(p_rowid)
   dbms_rowid.rowid_row_number(p_rowid)

   dbms_rowid.rowid_relative_fno(p_rowid)

    - The result of this function can be used to link to dba_data_files to get file name.

 

 The package also has many other function and procedure to process the ROWID data type.

 

 So we can get more detail about how data are stored in a table.