Formatted Data Block Dumps

Using an undocumented command, ALTER SYSTEM DUMP, you can create a formatted block dump of a data block. Below is the syntax of the ALTER SYSTEM DUMP command.

ALTER SYSTEM DUMP { DATAFILE | TEMPFILE } { file_num | ‘file_name’ }
{ BLOCK block_num | [ BLOCK MIN block_num ] BLOCK MAX block_num } ;

A single block can be dumped using:

ALTER SYSTEM DUMP { DATAFILE | TEMPFILE } { file_num | ‘file_name’ }
BLOCK block_num;

To dump multiple blocks within a range use:

ALTER SYSTEM DUMP { DATAFILE | TEMPFILE } { file_num | ‘file_name’ }
BLOCK MIN block_num BLOCK MAX block_num;

Below are some examples of using the ALTER STSTEM DUMP command to dump blocks from a table.

To dump the header block for table T query DBA_SEGMENTS to obtain the header file number and header block number.

SQL> select header_file, header_block 
  2  from dba_segments
  3  where segment_name = 'T';

HEADER_FILE HEADER_BLOCK     
----------- ------------ 
          4         1842          

SQL>

Using this information we can dump the header block for the table T using the ALTER SYSTEM DUMP command below.

SQL> alter session set tracefile_identifier='T_HEADER_DUMP';

Session altered.

SQL> alter system dump datafile 4 block 1842;

System altered.

SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

The alter session command was issued first to append T_HEADER_DUMP to the name of the trace file to make locating the file easier. The name set in the command will be appended to the name of all trace files generated for the session in which it was set so you will need to disconnect to set a new name.

Block dumps are found in ORACLE_BASE/diag/rdbms/< dbname >/< dbname >/trace for Oracle Database 11g and higher and USER_DUMP_DEST for Oracle Database 10g and earlier.

In order to dump all of the blocks in the table T use the following command.

SQL> alter session set tracefile_identifier='ALLBLOCKS_T_DUMP';

Session altered.

SQL> alter system dump datafile 4 block min 1842 block max 1850;

System altered.

SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>

If you want to dump a data block that contains a specific row you first will need to find the file number and the block number for the row. The function DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO will return the file number for a given rowid and the function DBMS_ROWID.ROWID_BLOCK_NUMBER will return the block number for a given rowid.

The function DBMS_ROWID.ROWID_TO_ABSOLUTE_FNO requires the rowid, the schema name and the object name. The function DBMS_ROWID.ROWID_BLOCK_NUMBER requires the rowid and the table space type SMALLFILE or BIGFILE. The table space type defaults to SMALLFILE if not specified. Below is an example of obtaining the file number and block number for a specific row.

SQL> select dbms_rowid.rowid_to_absolute_fno(rowid, 'EJENKINSON', 'T') FILE_NO,
  2         dbms_rowid.rowid_block_number(rowid) BLOCK_NO
  3  from t
  4  where col1 = 1

   FILE_NO   BLOCK_NO
---------- ----------
         4       1844

SQL> 

Using the results from above the block can be dumped with the following command.

SQL> alter session set tracefile_identifier='SPECBLOCK_T_DUMP';

Session altered.

SQL> alter system dump datafile 4 block 1844;

System altered.

SQL> disconnect
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>