A ROWID is the physical address of a row: which object, which file, which block, and which slot in the block. It looks like an opaque string, but DBMS_ROWID can take it apart. That helps when checking how rows are spread over blocks, finding rows in a block reported as corrupt, or understanding why some rows are read together.
Code for This Guide
The main examples are in the examples/pkg-utilities folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
dbms_rowid.rowid_object(rowid) -- data object number dbms_rowid.rowid_relative_fno(rowid) -- file number within the tablespace dbms_rowid.rowid_block_number(rowid [, 'BIGFILE']) dbms_rowid.rowid_row_number(rowid) -- slot in the block dbms_rowid.rowid_type(rowid) -- 0 restricted, 1 extended dbms_rowid.rowid_create(type, object, file, block, row)
Take a ROWID Apart
Example:
select airport_code, rowid,
dbms_rowid.rowid_object(rowid) as object_no,
dbms_rowid.rowid_relative_fno(rowid) as file_no,
dbms_rowid.rowid_block_number(rowid) as block_no,
dbms_rowid.rowid_row_number(rowid) as row_no,
dbms_rowid.rowid_type(rowid) as type
from airports
where airport_code in ('DXB', 'LHR');Output:
AIRPORT_CODE ROWID OBJECT_NO FILE_NO BLOCK_NO ROW_NO TYPE _______________ _____________________ ____________ __________ ___________ _________ _______ DXB AAAUejAAAAAAATdAAA 83875 0 1245 0 1 LHR AAAUejAAAAAAATdAAB 83875 0 1245 1 1
Both airports are in block 1245 of the same object, in slots 0 and 1, so one block read returns both. The object number is the table's data object ID, which matches DATA_OBJECT_ID in USER_OBJECTS.
Bigfile Tablespaces
The file number is 0 because the table is in a bigfile tablespace, the default in Oracle AI Database 26ai. There the file part of the ROWID holds part of the block number instead.
Example:
select t.tablespace_name, t.bigfile
from user_tables u join user_tablespaces t on t.tablespace_name = u.tablespace_name
where u.table_name = 'AIRPORTS';
-- in a bigfile tablespace, pass 'BIGFILE' to decode the block number
select airport_code,
dbms_rowid.rowid_block_number(rowid) as smallfile_view,
dbms_rowid.rowid_block_number(rowid, 'BIGFILE') as bigfile_block
from airports
where airport_code in ('DXB', 'LHR');Output:
TABLESPACE_NAME BIGFILE __________________ __________ USERS YES AIRPORT_CODE SMALLFILE_VIEW BIGFILE_BLOCK _______________ _________________ ________________ DXB 1245 1245 LHR 1245 1245
For this small table both calls agree, but in a bigfile tablespace pass 'BIGFILE' to ROWID_BLOCK_NUMBER, or block numbers come out wrong once a segment grows past the range of the smallfile format.
Things to Know
- A ROWID can change when a row moves: after table shrink, partition key updates with row movement, or a table move.
- Index-organized tables use logical ROWIDs, which DBMS_ROWID cannot decode.
- Within a transaction, WHERE ROWID = :r is the fastest way back to a row you have just read.
Related Guides
Conclusion
DBMS_ROWID splits a ROWID into object, file, block, and row number, and builds ROWIDs from those parts. Use it to see how rows are stored, remember to pass 'BIGFILE' for bigfile tablespaces, and keep ROWIDs for short-term use only.
