Dumps

Individual Buffers

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';