Datatype of ROWID in Oracle/SQL

Asked 23/7, 2012 at 12:10 Answered 7/12, 2016 at 0:13

Solved sql oracle rowid

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

Circadian answered 23/7, 2012 at 12:10 Comment(1)

See: ROWID (oracle) - any use for it? – Merkle 27/8, 2013 at 9:44

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

ROWID is stored as a psuedocolumn.

Donn answered 23/7, 2012 at 12:15 Comment(2)

Oracle 11gR2 documentation: ROWID Pseudocolumn and Rowid Data Types – Merkle 27/8, 2013 at 9:49

Well technically, a rowid is not stored at all, which is why it is a pseudo column. – Cord 19/9, 2015 at 17:46

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.

Norton answered 19/9, 2015 at 15:18 Comment(1)

A rowid need not be unique to a particular row. Rows stored in clusters can have the same rowid, although they have different urowids. Some rows do not have rowids at all. Where ROWIDs are stored, they are string representations of concatenated hexadecimals, not hexadecimals themselves. – Cord 19/9, 2015 at 17:52

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.

Rollie answered 7/12, 2016 at 0:13 Comment(0)

Recommended topics

Hot tags