Peeking table block contents

Sometimes you want to know what’s inside a certain block. Of course, the most straightforward way to do it is by dumping block contents using ALTER SYSTEM DUMP DATAFILE contents and analyzing it. However, “straightforward” doesn’t mean “simple”. Block dumps represent its contents in binary format which is hard to read. Sure, there are various utilities (like utl_raw) that can help you convert everything to the human-readable format, but it’s going to be a tedious and time-consuming job, especially if you need more than just a few values from just a couple of blocks. Another problem is that you may not have access to the server OS shell (e.g. developers rarely have access to it even on non-production system, except maybe on private sandboxes).

There’s a better way, at least if the block you’re interested in is a standard table data block and all you want to know is what kind of data it contains (and not internal information like locks, flags, free space etc.). The idea is that rather than going to the block itself, you can use rowid to calculate the block address and the relative file number. If you know the table of interest, and if you do the calculation above for all its rows, then you simply filter out the particular block you’re interested in from the resultset. It’s really much simpler than it sounds, just bear with me a little and you’ll see.

The first step would be identifying the segment name. Normally you already know it from the very beginning. For example, if you found the address of the block that you want to look up in ASH or in a trace file, e.g. as p1 and p2 parameters of “db file sequential read” event, then you can simply take current_obj# and look up the object name in DBA_OBJECTS, using object_id (and/or data_object_id) as the key.

Once you have the table name, you can display block contents using rowid_relative_fno and rowid_block_number functions of dbms_rowid package:

select *
from
(
select id1,
id2,
dbms_rowid.rowid_relative_fno(rowid) fno,
dbms_rowid.rowid_block_number(rowid) block#
from &mytable
)
where fno = :fno
and block# = :blockno;

where &mytable obviously should be replaced with the name of the table identified in the previous step.

It could be convenient to aggregate the query above so that each block’s contents would be represented by a single row. For example, imagine that you have a table with a composite primary key (id1, id2), so that knowing these two values is enough to identify the row. Then you can use listagg to compactly represent block contents:

select listagg('(' || id1 || ',' || id2 || ')') within group (order by id1, id2) block_contents, fno, block#
from
(
select id1,
id2,
dbms_rowid.rowid_relative_fno(rowid) fno,
dbms_rowid.rowid_block_number(rowid) block#
from &mytable
)
group by fno, block#

This is particularly convenient when working with a relatively large number of blocks that can be extracted from another SQL statement, e.g. a query on an external table built on top of a trace or a dump file as described in one of my recent posts (e.g. here).

As a final touch, you can also add a query block to identify the table’s segment header to make sure you don’t miss it:

select 'segment header' block_contents,
header_file fno,
header_block block#
from dba_segments
where segment_name = '&mytable'

You can’t do the same trick on indexes as there’s no rowid for an index entry, but there are other ways to peek at contents of branch and leaf index blocks. If I get a chance, I’ll show one or two such methods in a separate blog post.