Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Datatype of ROWID in Oracle/SQL

Tags:

sql

oracle

rowid

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

like image 649
oneiros Avatar asked Jul 23 '12 12:07

oneiros


3 Answers

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.

like image 75
Kodima Zeebra Avatar answered Oct 18 '22 20:10

Kodima Zeebra


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

ROWID is stored as a psuedocolumn.

like image 9
THE DOCTOR Avatar answered Oct 18 '22 18:10

THE DOCTOR


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.

like image 2
Rob Heusdens Avatar answered Oct 18 '22 19:10

Rob Heusdens