Rowid Formats

David Clement

September 2004; revised 10 October 2007.

Oracle versions 7-8

Oracle provides a nearly unique row identifier in the form of a pseudocolumn called rowid. A pseudocolumn is the output of an Oracle internal function that can be treated in SQL queries as if it were a column in the queried table; sysdate is a well-known example. The pseudocolumn rowid identifies the row uniquely within the table, and encodes information about the block where the row is stored. You would think it would be highly useful for determining block usage in those cases where you think the HWM might be too high.

Indeed it is, but there's a gotcha.

The difficulty is that Oracle Corp. decided to change the format of rowid with the release of Oracle 8i. They also supplied a handy package for rowid inspection and manipulation, dbms_rowid. So if you need to support a client who is still running 7.3.4, or if you need to work in a mixed environment of old and new instances, you will need to keep the rowid format conversion in mind.

The old format was BBBBBBBB.RRRR.FFFF and the new is OOOOOOFFFBBBBBBRRR. In the old format, the first eight bytes of output were a block identifier, then, after the dot delimiter, the next four bytes were a row identifier, and then, after another dot, the last four bytes were a file identifier. In the new format, the first six bytes identify the database object (such as a table or index), the next three identify the file, the next six identify the block, and the last three identify the row.

For tasks such as counting the blocks that a table actually uses, it is easier, with the new format, to use dbms_rowid. In any case the block identifier and file identifier must both be used, because the block identifier is for the block within the file, not for the block in the whole environment of operating system, filesystem, hard disk, and so on.

To count the distinct blocks used for storing rows under Oracle 7.3.4, you can use the following SQL.

select count (distinct (substr (rowid, 1, 8)      -- block ID
|| substr (rowid, 15, 4) ) )                      -- file ID
from davidc.mytable;
To count the distinct blocks used for storing rows under Oracle 8i and higher, you can use the following SQL.
select count (dbms_rowid.rowid_block_number (rowid)  -- block ID
|| dbms_rowid.rowid_relative_fno (rowid) )           -- file ID
from davidc.mytable;

A note about the reliability of rowid. Each of these ID values points to the initial piece of a row, which is guaranteed not to move, except under one of two circumstances. So you can almost always trust a rowid value in an update statement.

The only exceptions are the two circumstances that Tom Kyte pointed out on page 632 of the first edition of Expert One-on-One Oracle. First, if the table is partitioned, and row movement is enabled, which allows a row to migrate from one partition to another if its partition key changes, then the rowid will change as well. It has to, since the row has been copied to a new storage location and deleted from its old one. Second, if the table is an IOT (index-organized table), changing its primary key will cause the row to migrate.