4.8 How do I…Interpret the format of ROWID?

Problem

I need to use the ROWID pseudo-column for fast retrieval and location of records. I also need to determine where my actual data resides within a tablespace. How do I interpret the format of ROWID?

Technique

Use the various DBMS_ROWID functions to extract information about the ROWID pseudo-column. There are two ROWID formats: extended and restricted. Restricted is used mostly to be backward-compatible with Oracle7. The extended format takes advantage of new Oracle8 features. Table 4.2 shows the components of the extended ROWID format, and Table 4.3 shows the components of the restricted ROWID format.

Table 4.2 Description of the extended format for ROWID (10 bytes)

Portion Description
oooooo 6-character object number (base-64 encoded)
fff 3-character relative file number (base-64 encoded)
bbbbbb 6-character block number in file (base-64 encoded)
sss 3-character slot within the block (base-64 encoded)Table 4.3 Description of the restricted format for ROWID (6 bytes)

Table 4.3 Description of the restricted format for ROWID (6 bytes)

Portion Description
bbbbbbbb 6-character block (hexidecimal encoded)
rrrr 4-character row number in block (hexidecimal encoded)
ffff 4-character file number (hexidecimal encoded)

Steps

1. Connect to SQL*Plus as the Waite account. If you have not already done so, create the EMPLOYEE04 table by running the CHP4_1.SQL. Also, if you have not already done so, insert sample data into the EMPLOYEE04 table by running the CHP4_18.SQL script.

The DBMS_ROWID package has several procedures and functions to interpret the ROWIDs of records. Table 4.4 shows the DBMS_ROWID functions, most of which will be explained in this How-To.

Function Description
ROWID_BLOCK_NUMBER The block number that contains the record; 1 = extended ROWID.
ROWID_OBJECT The object number of the object that contains the record.
ROWID_RELATIVE_FNO The relative file number contains the record.
ROWID_ROW_NUMBER The row number of the record.
ROWID_TO_ABSOLUTE_FNO The absolute file number; you need to input rowid_val, schema, and object; the absolute file number is returned.
ROWID_TO_EXTENDED Converts the ROWID from restricted to extended; you need to input restr_rowid, schema, object; the extended number is returned.
ROWID_TO_RESTRICTED Converts the ROWID from extended to restricted.
ROWID_TYPE 0 = restricted ROWID, 1 = extended.
ROWID_VERIFY Verifies if the ROWID can be extended; 0 = can be converted to extended format; 1= cannot be converted to extended format.

2. Run the CHP4_26.SQL script to retrieve ROWID information on each record.

SQL> GET CHP4_26.sql

1 select

2 b.object_name||’.’||

3 dbms_rowid.rowid_relative_fno(a.rowid)||’.’||

4 dbms_rowid.rowid_block_number(a.rowid)||’.’||

5 dbms_rowid.rowid_row_number(a.rowid)||’.’||

6 ‘- type ‘||

7

decode(dbms_rowid.rowid_type(a.rowid),0,’RESTRICTED’,1,’EXTENDED’)

8 from employee04 a, all_objects b

9 where dbms_rowid.rowid_object(a.rowid) = b.object_id

SQL> /

EMPLOYEE04.2.1493.0.- type EXTENDED

EMPLOYEE04.2.1493.1.- type EXTENDED

EMPLOYEE04.2.1493.2.- type EXTENDED

EMPLOYEE04.2.1493.3.- type EXTENDED

EMPLOYEE04.2.1493.4.- type EXTENDED

EMPLOYEE04.2.1493.5.- type EXTENDED

EMPLOYEE04.2.1493.6.- type EXTENDED

EMPLOYEE04.2.1493.7.- type EXTENDED

EMPLOYEE04.2.1493.8.- type EXTENDED

EMPLOYEE04.2.1493.9.- type EXTENDED

EMPLOYEE04.2.1493.10.- type EXTENDED

EMPLOYEE04.2.1493.11.- type EXTENDED

EMPLOYEE04.2.1493.12.- type EXTENDED

EMPLOYEE04.2.1493.13.- type EXTENDED

EMPLOYEE04.2.1493.14.- type EXTENDED

EMPLOYEE04.2.1493.15.- type EXTENDED

EMPLOYEE04.2.1493.16.- type EXTENDED

EMPLOYEE04.2.1493.17.- type EXTENDED

EMPLOYEE04.2.1493.18.- type EXTENDED

EMPLOYEE04.2.1493.19.- type EXTENDED

EMPLOYEE04.2.1493.20.- type EXTENDED

EMPLOYEE04.2.1493.21.- type EXTENDED

EMPLOYEE04.2.1493.22.- type EXTENDED

EMPLOYEE04.2.1493.23.- type EXTENDED

EMPLOYEE04.2.1493.24.- type EXTENDED

EMPLOYEE04.2.1493.25.- type EXTENDED

EMPLOYEE04.2.1493.26.- type EXTENDED

EMPLOYEE04.2.1493.27.- type EXTENDED

EMPLOYEE04.2.1493.28.- type EXTENDED

EMPLOYEE04.2.1493.29.- type EXTENDED

EMPLOYEE04.2.1493.30.- type EXTENDED

EMPLOYEE04.2.1493.31.- type EXTENDED

31 rows selected.

How It Works

The DBMS_ROWID functions provide information such as block, row, relative file number, and absolute file number on each ROWID in a table. The ROWID is a pseudo-column that has a unique value associated with each record of the database.

CHP4_26.SQL will display the object name, the relative file number that contains the record, the block that contains the record, the row of the record, and the ROWID type. The ROWID type is determined by using the DBMS_ROWID.ROWID_TYPE function. If the function returns 0, the ROWID is restricted. If the function returns 1, the ROWID is extended. The object name is determined by joining the ALL_OBJECTS view with the result of the DBMS_ROWID.ROWID_OBJECT function.

Comments

The DBMS_ROWID package is created by the $ORACLE_HOME/RDBMS/ADMIN/DBMSUTIL.SQL script. This script is automatically run when the Oracle instance is created. This program is a good source to learn more about ROWID information.

The functions for DBMS_ROWID will return a NULL if the input is NULL.