Lesson : 30 - Pseudocolumn - ROWID

Pseudocolumn  column in Oracle

A pseudocolumn  is like a table column, but is not actually stored in the table. We can select from pseudocolumns, but we cannot insert, update, or delete their values.

 ROWID

 ROWID pseudocolumn returns thee address of the row which contains the data object number, the data block of the row, the row position and data file.

 Using the ROWID  is  the fastest way to search for a given row in the database.

The ROWID hexadecimal values consists for following information

•The data object number of the object (1-4 digits)

•The data block in the datafile in which the row resides (4 to 8th )

•The position of the row in the data block (first row is 0)  - 8th to 12th of rowid

•The datafile in which the row resides (first file is 1). The file number is relative to the tablespace. (12th to 16th)

  Example :

SQL> select rowid from emp;

ROWID

------------------

AAAsR4AAFAAABgzAAA

AAAsR4AAFAAABgzAAB

AAAsR4AAFAAABgzAAC

Note : ROWID values are not necessarily unique within a database. It is  possible for two rows of two different tables stored in the same cluster and have the same ROWID.  But it is unique within table.

 ROWID  values may change if the row is physically moved on disk, in the following  scenories  :

When we do  export or import of the table

 

·         ALTER TABLE  with MOVE option

 

·         When we  SHRINK SPACE with Alter table 

·         When do table FLASHBACK  

·         When we do  splitting a partition 

·         When combining two partitions