10

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

user272735
  • 10,473
  • 9
  • 65
  • 96
oneiros
  • 3,527
  • 12
  • 44
  • 71

3 Answers3

9

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

ROWID is stored as a psuedocolumn.

THE DOCTOR
  • 4,399
  • 10
  • 43
  • 64
  • 1
    Oracle 11gR2 documentation: [ROWID Pseudocolumn](http://docs.oracle.com/cd/E11882_01/server.112/e41084/pseudocolumns008.htm#sthref63) and [Rowid Data Types](http://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements001.htm#sthref213) – user272735 Aug 27 '13 at 09:49
  • Well technically, a rowid is not stored at all, which is why it is a pseudo column. – David Aldridge Sep 19 '15 at 17:46
2

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.

Kodima Zeebra
  • 55
  • 3
  • 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. – David Aldridge Sep 19 '15 at 17:52
2

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.

Rob Heusdens
  • 155
  • 5