ROWID in Oracle

ROWID :- For each row in the database, the ROWID pseudocolumn returns the address of the row. Oracle Database rowid values contain information necessary to locate a row.

An extended ROWID needs 10 bytes of storage on disk and is displayed by using 18 characters. It consists of the following components:

• Data object number: Is assigned to each data object, such as table or index when it is created, and it is unique within the database

• Relative file number: Is unique to each file within a tablespace

• Block number: Represents the position of the block, containing the row, within the file

• Row number: Identifies the position of the row directory slot in the block header

Internally, the data object number needs 32 bits, the relative file number needs 10 bits, block number needs 22 bits, and the row number needs 16 bits, adding up to a total of 80 bits or 10 bytes.

An extended ROWID is displayed using a base-64 encoding scheme, which uses six positions for the data object number, three positions for the relative file number, six positions for the block number, and three positions for the row number. The base-64 encoding scheme uses characters A-Z, a-z, 0-9, and /. This is a total of 64 characters, as in the following example:

SQL> select rowid,empid,name,sal from emp where empid <= 105;

ROWID                                        EMPID        NAME                                  SAL

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

AAAFDwAABAAARcJAAA        101                aman                                 3000

AAAFDwAABAAARcJAAB        102                rk                                          400

AAAFDwAABAAARcJAAC        103               am                                      1000

AAAFDwAABAAARcJAAD        104               vinod                                  1000

AAAFDwAABAAARcJAAE        105               kamal                                   900

AAAFDwAABAAARcJAAI         100               akhlesh                              3000

In this example:

• AAAFDw is the data object number

• AAB is the relative file number

• AAARcJ is the block number

• AAA is the row number for the emp with EMPID = 101;

• Row header: Used to store the number of columns in the row, the chaining information, and the row lock status

    • Row data: For each column, the Oracle server stores the column length and value (One byte is needed to store the column length if the column will require more than 250 bytes of storage in which case three bytes will be used for column length. The column value is stored immediately following the column length bytes.)

Using dbms_rowid one can map rowid back to object id, file id so the tablespace name and also using the file_id and block_no one can map it back to object_name.

declare

      my_rowid rowid := 'AAAFDwAABAAARcJAAA';

      rowid_type number;

     object_id number;

     relative_fno number;

     block_no number;

     row_no number;

begin

     dbms_rowid.rowid_info(my_rowid, rowid_type, object_id, relative_fno, block_no, row_no);

    dbms_output.put_line('ROWID: ' || my_rowid);

    dbms_output.put_line('Object#: ' || object_id);

    dbms_output.put_line('RelFile#: ' || relative_fno);

    dbms_output.put_line('Block#: ' || block_no);

   dbms_output.put_line('Row#: ' || row_no);

end;

Output:--

ROWID: AAAFDwAABAAARcJAAA

Object#: 20720

RelFile#: 1

Block#: 71433

Row#: 1

Using the object_id you can can map the row id to object_name

SQL> select object_name,object_type from dba_objects where object_id = 20720;

OBJECT_NAME                                                OBJECT_TYPE

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

EMP                                                                    TABLE

By using the below query you can know know the segment,tablespace and owner name.

select owner, segment_name,tablespace_name from dba_extents where file_id = 1 and block_id<= 71433 and block_id + blocks > 71433;

OWNER                          SEGMENT_NAME              TABLESPACE_NAME              

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

SYSTEM                         EMP                                       SYSTEM