Oracle ROWID Format

<<Back to DB Administration Main Page

Oracle ROWID Explained

From  Oracle 8 onwards Oracle ROWID format is on 10 bytes and represented as OOOOOOFFFBBBBBBRRR.

Bits 1 to 32 (bytes 1 to 4) presents data object id (Possible unique DB Object 4294967295)
Bits 33 to 44 (byte 5 and half byte 6): represent file number inside the tablespace (Possible unique datafile 4095)
Bits 45 to 64 (half byte 6 and bytes 7 and 8): presents block number inside the file ( possible unique block ids 1048575)
Bits 65 to 80 (bytes 9 and 10):presents  row number inside the block ( possible number of rows in a block 65535)
When printed, each field is displayed in radix 64 (A-Za-z0-9+/):

The first 6 characters of the ROWID presents Object ID, Next 3 characters presents datafile# the object belongs to, next 6 characters presents Block ID in the datafile and the last 3 character presents actual row number in the block. That's said lets understand this with an example.

SQL> select rowid from emp where rownum=1;

ROWID
------------------
AH640SADnAAL/UHAAA

1 row selected.

SQL> Select DBMS_ROWID.ROWID_OBJECT('AH640SADnAAL/UHAAA') "Object_ID" from dual;

 Object_ID
----------
 132877586

1 row selected.

SQL> Select DBMS_ROWID.ROWID_RELATIVE_FNO('AH640SADnAAL/UHAAA') "Relative File#" from dual;

Relative File#
--------------
           231

1 row selected.

SQL> Select DBMS_ROWID.ROWID_BLOCK_NUMBER('AH640SADnAAL/UHAAA') "Block ID" from dual;

  Block ID
----------
   3142919

1 row selected.

SQL> Select DBMS_ROWID.ROWID_ROW_NUMBER('AH640SADnAAL/UHAAA') "ROW Number" from dual;

ROW Number
----------
         0

1 row selected.

So As you can see my row id AH640SADnAAL/UHAAA belongs to datafile file#231, object id 132877586 and Block ID 3142919 and Its row number is 0(means 1st row)in the block

Lets do some reverse engineering to verify the fact.

SQL> Select DBMS_ROWID.ROWID_CREATE(1,132877586,231,3142919,0) "ROW Number" from dual;

ROW Number
------------------
AH640SADnAAL/UHAAA

1 row selected.

I created the row id using ROWID_CREATE function of  DBMS_ROWID package. And as you can see I get the same ROWID.

Lets find the object using Object_ID

SQL> select OWNER,OBJECT_NAME,OBJECT_ID,DATA_OBJECT_ID,OBJECT_TYPE from dba_objects where OBJECT_ID='132877586';

OWNER    OBJECT_NAME     OBJECT_ID    DATA_OBJECT_ID   OBJECT_TYPE
------------  -------------------------- ----------          --------------                   ---------------
TEST            EMP                       132877586      132877586                    TABLE

1 row selected.

Lets find the datafile details using Relative file ID

SQL>  select FILE_NAME,FILE_ID,TABLESPACE_NAME,RELATIVE_FNO from dba_data_files where RELATIVE_FNO='231';

FILE_NAME                                                                 FILE_ID TABLESPACE_NAME      RELATIVE_FNO
---------------------------------------------------------------------- ---------- -------------------- ------------
+DATA/ORCL/datafile/users.2691.871237915                                   231 USERS                         231

1 row selected.

Lets find the block object details using block number
SQL> select OWNER,SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME,FILE_ID,BLOCK_ID from dba_extents where BLOCK_ID='3142919';

OWNER           SEGMENT_NAME              SEGMENT_TYPE         TABLESPACE_NAME         FILE_ID   BLOCK_ID
--------------- ------------------------- -------------------- -------------------- ---------- ----------
TEST             EMP                      TABLE                USER                        231   3142919

1 row selected.

ROWID Format and Big File Tablespace in Oracle

At this point its worth mentioning about Bigfile tablespace. As you know a big file tablespace can have just one datafile (file#1024), this means one and half (1.5) bytes space which oracle was using to number the datafile is saved. Oracle combined this space with the block number. So in Bigfile tablespace the block number bytes is increased to 4 Bytes (2.5+1.5 bytes). This means a single datafile in Bigfile tablespace can now hold more oracle database blocks and can be much larger than the datafile in small file tablespace.

The ROWID in this case has no datadile field associate because its fixed (1024) for each tablespace

Below you can see the presentation of ROWID for a BIG File Tablespace