ROWID

From Oracle FAQ
Jump to: navigation, search

Every record has a unique ROWID within a database representing the physical location on disk where the record lives.

From Oracle 8 the ROWID format and size changed from 8 to 10 bytes. Note that ROWID's will change when you reorganize or export/import a table. In case of a partitioned table, it also changes if the row migrates from a partition to another one during an UPDATE.

Oracle 7 format[edit]

The Oracle 7 format was on 8 bytes:

  • bytes 1 to 4 (bits 1 to 32): block number inside the file containing the row (0-4294967295)
  • bytes 5 and 6 (bits 33 to 48): row number inside the block containing the row (0-65535)
  • bytes 7 and 8 (bits 49 to 64): file number (0-65535)

When printed, each byte was displayed by 2 hexadecimal characters (0-9A-F) and each field separated by a dot: BBBBBBBB.RRRR.FFFF

Oracle 8 format[edit]

The Oracle 8 format is on 10 bytes:

  • bits 1 to 32 (bytes 1 to 4): data object id (0-4294967295)
  • bits 33 to 44 (byte 5 and half byte 6): file number inside the tablespace (0-4095)
  • bits 45 to 64 (half byte 6 and bytes 7 and 8): block number inside the file (0-1048575)
  • bits 65 to 80 (bytes 9 and 10): row number inside the block (0-65535)

When printed, each field is displayed in radix 64 (A-Za-z0-9+/): OOOOOOFFFBBBBBBRRR

Note that for a bigfile tablespace File and Block fields are combined to give Block number in the big file.

ROWID related functions[edit]

ROWIDTOCHAR allows to display a rowid:

SQL> select ROWIDTOCHAR(rowid) from dual;
ROWIDTOCHAR(ROWID)
------------------
AAAAECAABAAAAgiAAA

Object id: AAAAEC (=258), File id: AAB (=1), Block number: AAAAgi (=2082), Row number: AAA (=0)

CHARTOROWID allows to select get a row from its rowid representation:

SQL> select * from dual where rowid = CHARTOROWID('AAAAECAABAAAAgiAAA');
D
-
X

DBMS_ROWID package allows to split rowid information:

SQL> select DBMS_ROWID.ROWID_OBJECT(rowid) "OBJECT",
  2         DBMS_ROWID.ROWID_RELATIVE_FNO(rowid) "FILE",
  3         DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) "BLOCK",
  4         DBMS_ROWID.ROWID_ROW_NUMBER(rowid) "ROW"
  5  from dual
  6  /
    OBJECT       FILE      BLOCK        ROW
---------- ---------- ---------- ----------
       258          1       2082          0
Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #