How to check in which datafile rows are written

Every rowid has a four-peace format.

Rowids are base64 encoded, which means you have to use base64_decode from utl_encode package. This function requires raw input, that’s why we need to cast with utl_raw.cast_to_raw

For example:

AAAScXAAEAAAAKzAAA
AAAScX – is data object number
AAE – datafile number – (this is what we need)
AAAAKz – data block that contains the row
AAA – row in the block

After you get datafile number you can query v$datafiles to find the datafile name.
Because rowid contains relative datafile number you should match that number with RFILE# column in v$datafile. Relative datafile number is unique datafile number in parent tablespace, while absolute datafile number is unique datafile number in the database. These two are almost always same, they are different when the number of datafiles in database exceed treshold (1023).
You can use dbms_rowid package

select dbms_rowid.rowid_object(rowid) from table;

For more information read official Oracle documentation.

Leave a Reply