September 2004; revised 10 October 2007.
Oracle versions 7-8
Oracle provides a nearly unique row identifier in the form of a
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;
a well-known example. The pseudocolumn
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
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.
To count the distinct blocks used for storing rows under Oracle 8i
and higher, you can use the following SQL.
select count (distinct (substr (rowid, 1, 8) -- block ID
|| substr (rowid, 15, 4) ) ) -- file ID
select count (dbms_rowid.rowid_block_number (rowid) -- block ID
|| dbms_rowid.rowid_relative_fno (rowid) ) -- file ID
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
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.