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(
  'aaaaaaaaaa','bbbbbbbbbb','cccccccccc','dddddddddd');

insert into dump_table values(
  'AAAAAAAAAA','BBBBBBBBBB','CCCCCCCCCC','DDDDDDDDDD');

commit;

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

HEADER_FILE HEADER_BLOCK
----------- ------------
          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     
                   Unlocked
     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)
is

  dump_file varchar2(4000);
   
begin

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

  select 
    u_dump.value || '/' || instance.value || '_ora_' || sys.v_$process.spid || '.trc'
  into 
    dump_file
  from 
               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 
   u_dump.name   = 'user_dump_dest' and 
   instance.name = 'instance_name' and
   sys.v_$session.audsid=sys_context('userenv','sessionid');

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


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

Dumping a block in Oracle

Dumping the Segment's Header Block

Location of dumpfile

Dumping a block from a rowid

Other things