Donald K. Burleson Oracle Tips

Dumping Oracle database blocks

Oracle provides several undocumented ALTER SESSION commands that can be used to dump details from the Oracle internal control structures. These can be very useful for investigating Oracle corruptions and peeking into the Oracle internal structures.

To do this, you must first start by getting the data block address (DBA) for the block that you want to display. This is done by taking the file number and block number and running the dbms_utility.make_data_block_address function.

For example, you can use the dbms_utility package to get a data block address.  Many Oracle scripts will provide you with the file number and block number, but you must then translate this information into the actual data block address (DBA) for the block.

For example, to dump file number 101, block 50, you could enter the following PL/SQL.

Next, you take the resulting DBA and use it with the alter session command to dump the contents of the data block.  In this example, we assume that we have returned data block address 10059

We can then go to our trace file directory (usually BDUMP) and view the trace file with the detailed block contents.

While Oracle does not provide data descriptions (DESCTS) for the data blocks, with a little work and intuition, you can reckon the details of the block header and footers, while seeing the actual contents of the data rows.