Individual Buffer Dump

In Oracle 8.0 and above is is possible to dump buffer all buffers currently in the cache for a specific block

For example where a block has been modified and is subject to consistent read from a number of transactions, there may be more than one copy of the block in the buffer cache

First identify the tablespace number for the block e.g for tablespace TS01:

  SELECT ts# FROM sys.ts$ WHERE name = 'TS01';

Set the tablespace number using:

  ALTER SESSION SET EVENTS 'immediate trace name set_tsn_p1 level level';

where level is the tablespace number + 1

Identify the relative DBA for the block

This is equal to:

  RelativeFileNumber * 4194304 + BlockNumber

e.g. for a block with relative file number of 5 and a block number of 127874:

  5 * 4194304 + 127874 = 21099394

Dump the buffer using:

  ALTER SESSION SET EVENTS 'immediate trace name buffer level level';

where level is the relative DBA e.g.

  ALTER SESSION SET EVENTS 'immediate trace name buffer level 21099394';