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 SQL 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 dumpe

 

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