Datatype of ROWID in Oracle/SQL

Question

What is the data type of ROWID in Oracle/SQL? How is this value stored?

Answer 1

A ROWID data type stores information related to the disk location of table rows. They also uniquely identify the rows in your table. The ROWID data type is stored as a hexadecimal.

Therefore the hexadecimal string represents the unique address of a row in its table.

Answer 2

The following link explains what the data type for ROWID is - ROWID data type

ROWID is stored as a psuedocolumn.

Answer 3

The ROWID (since Oracle8 called extended ROWID, till Oracle7 now called restricted ROWID) stores/encodes the physical location of a row.

The (extended) ROWID encodes (in hexadecimal format) the following fields: OBJID (unique id of the object the row belongs to), the FILENO (relative file number within the TABLESPACE in which the object is created, the BLOCKNO (the relative block number within the file), and ROWNUM (the relative number of the row within the block).

The (restricted) ROWID only contained FILENO (relative file number within the database), BLOCKNO and ROWNUM (like in exteded ROWID) and no OBJID.

At any given time, the ROWID uniquely encodes the row in a database (except perhaps for clustered tales which share the same ROWID). ROWID's may change (due to reorganization of the database) and also may be re-used (for example after delete and insert, the new row may re-use a ROWID). Also exporting/importing tables will re-assign new ROWID's for tables.

Note that the FILENO is not necessarily unique per database in the extended ROWID format, but since the extended format also contains OBJID (which uniquely identifies the tablespace in which segments of the table object is stored), we can find a unique file.

BLOCKNO is unique per database file, and ROWNUM is unique per database block.

Exceptions: Index organized tables - ROWID can not locate the exact block due to index reorganisation (movement of leaf blocks).

ROWID's may change due to database reorganisation operations.