René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback

Dumping a block in Oracle

It is possible to peek into the datafiles and see what data they contain. In order to demonstrate that, I create a table (dump_table), fill some data in it and dump it into a file for further inspecting.
The statement to dump one or more blocks is
alter system dump datafile <number datafile> block min <first number> block max <last block>
Here's the table that's going to be filled:
create table dump_table (
  a varchar2(4000),
  b varchar2(4000),
  c varchar2(4000),
  d varchar2(4000));
insert into dump_table values(

insert into dump_table values(


Dumping the Segment's Header Block

Each table (actually each segment) has a segment header.
First, the file and the block number must be determined before the block can be dumped
SQL> select header_file, header_block from dba_segments where segment_name = 'DUMP_TABLE';

----------- ------------
          3       108639
alter system dump datafile 3 block 108639;

Location of dumpfile

This command dumps the block in a more or less readable form. Where is it dumped? The directory in which it is dumped is found like this:
select value from v$parameter where name = 'user_dump_dest';
The name of the dumped file is then determined like so:
select spid
from v$session s, v$process p
where p.addr = s.paddr
and s.audsid = sys_context('userenv','sessionid')
This returns a number. Append this number to ORA and append a suffix .trc
See find the trace file for a complete script.
Here's the interesting part of the dump file:
Start dump data blocks tsn: 3 file#: 3 minblk 49 maxblk 49
buffer tsn: 3 rdba: 0x00c00031 (3/49)
scn: 0x0000.0005c24e seq: 0x01 flg: 0x04 tail: 0xc24e1001
frmt: 0x02 chkval: 0x49ed type: 0x10=DATA SEGMENT HEADER - UNLIMITED
  Extent Control Header
  Extent Header:: spare1: 0      spare2: 0      #extents: 1      #blocks: 15    
                  last map  0x00000000  #maps: 0      offset: 2080  
      Highwater::  0x00c00033  ext#: 0      blk#: 1      ext size: 15    
  #blocks in seg. hdr's freelists: 1     
  #blocks below: 1     
  mapblk  0x00000000  offset: 0     
     Map Header:: next  0x00000000  #extents: 1    obj#: 5927   flag: 0x40000000
  Extent Map
   0x00c00032  length: 15    
  nfl = 1, nfb = 1 typ = 1 nxf = 0 ccnt = 1
  SEG LST:: flg: USED   lhd: 0x00c00032 ltl: 0x00c00032 
End dump data blocks tsn: 3 file#: 3 minblk 49 maxblk 49
The number after obj# (that is 5927) is found in sys.obj$:
select name from obj$ where obj# = 5927;
returns DUMP_TABLE.

Dumping a block from a rowid

The following procedure can be used to dump a database block that contains a row whose rowid is known.
The user who compiles this procedure must have the rights to select v$parameter, v$process, v$session, otherwise he'll get an ora-00942 table or view does not exist.
create or replace procedure dump_block_from_rowid(p_rowid rowid)

  dump_file varchar2(4000);

  execute immediate '
    alter system dump datafile ' ||
      dbms_rowid.rowid_relative_fno(p_rowid) || '
    block ' ||

    u_dump.value || '/' || instance.value || '_ora_' || sys.v_$process.spid || '.trc'
               sys.v_$parameter u_dump 
    cross join sys.v_$parameter instance
    cross join sys.v_$process 
          join sys.v_$session 
            on sys.v_$process.addr = sys.v_$session.paddr
  where   = 'user_dump_dest' and = 'instance_name' and

  dbms_output.put_line('  dumped block to: ');
  dbms_output.put_line('  ' ||  dump_file);

Other things