JL Computer Consultancy

Dumping an Oracle 8 database block

January 1999


Update 7-Sep-1999: Modified SQL to dump data from segments of partitioned objects.

From time to time it is useful to be able to dump an Oracle block to find out what is stored in it. In fact, until relatively recently this was the only way to find out where the high water mark was in a data segment.

With the arrival of the dbms_space package in Oracle 7.3.x, block dumping took a bit of a back seat; however we are now at Oracle 8.0 and some of the new segment types such as LOBINDEX and LOBSEGMENT are not catered for. This script is an example of how to dump a block in Oracle 8 and is followed by the header dump from a LOBSEGMENT

Back to Main Index of Topics


rem
rem     Script:        dump_blk.sql
rem     Author:        J.P.Lewis
rem     Dated:         12-Sep-1998
rem     Purpose:       Dump a block from Oracle 8
rem
rem     Use:
rem     ----
rem     User must have access to dba_segments view
rem     User must be able to read Oracle trace files
rem     Execute script, then supply, when prompted:
rem            Owner of segment
rem            Name of segment
rem            Name of partition
rem            Offset from start of segment
rem
rem     Note:
rem     -----
rem     This script is only good for dumping blocks in the first
rem     extent of the segment.
rem     
rem     Dumping block at offset 0 (the segment header block) will
rem     give you access to free-space information for segments for
rem     which the dbms_space is not yet implemented.
rem

set pagesize 0
set def =
set def &

column  header_block new_value m_block
column  header_file new_value m_file

select
        header_file,
        header_block + &m_offset  header_block
from 
        dba_segments 
where 
        segment_name = upper('&m_segment')
and     nvl(partition_name,'xxx') = nvl(upper('&m_partition'),'xxx')
and     owner = upper('&m_owner')
;

alter system dump datafile &m_file block min &m_block block max &m_block;

Back to Main Index of Topics


In the trace file generated by this script, the most interesting, or useful, item is the line 11 lines down starting with the word: highwater - which tells us that the high water mark is in extent 17 (counting from 0) at block 5. The dump also tells you explicitly (unlike the old Oracle 7 dumps) that we currently have 93 blocks below the highwater mark.


Wed Jan  6 14:13:15 1999
Start dump data blocks tsn: 4 file#: 5 minblk 604 maxblk 604
buffer tsn: 4 rdba: 0x0140025c (5/604)
scn:0x0000.00060e9a seq:0x03 flg:0x00 tail:0x0e9a1703
       frmt:0x02 chkval:0x0000 type:0x17=BITMAPPED DATA SEGMENT HEADER
 
  Extent Control Header
  -----------------------------------------------------------------
  Extent Header:: spare1: 0      tsn: 4      #extents: 18     #blocks: 93    
                  last map rdba: 0x00000000  #maps: 0      offset: 1056  
      Highwater:: rdba: 0x0140023e  ext#: 17     blk#: 5      ext size: 5     
  #blocks in seg. hdr's freelists: 0     
  #blocks below: 93    
  mapblk rdba: 0x00000000  offset: 17    
                   Unlocked
     Map Header:: next rdba: 0x00000000  #extents: 18   obj#: 16068  flag: 0x40000000
  Extent Map
  -----------------------------------------------------------------
  rdba: 0x0140025e  length: 8     
  rdba: 0x014000bb  length: 5     
  rdba: 0x01400084  length: 5     
  rdba: 0x01400106  length: 5     
  rdba: 0x01400057  length: 5     
  rdba: 0x01400020  length: 5     
  rdba: 0x014000d4  length: 5     
  rdba: 0x01400257  length: 5     
  rdba: 0x014000c0  length: 5     
  rdba: 0x014000f7  length: 5     
  rdba: 0x0140004d  length: 5     
  rdba: 0x014000b6  length: 5     
  rdba: 0x0140023e  length: 5     
  rdba: 0x014000a7  length: 5     
  rdba: 0x01400101  length: 5     
  rdba: 0x0140010b  length: 5     
  rdba: 0x014000ed  length: 5     
  rdba: 0x01400239  length: 5     
  
 block size = 2024, nfb = 2, type = 3
  fatblk = 1, states = 2
End dump data blocks tsn: 4 file#: 5 minblk 604 maxblk 604

Back to Main Index of Topics