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     Script:        dump_blk.sql
rem     Author:        J.P.Lewis
rem     Dated:         12-Sep-1998
rem     Purpose:       Dump a block from Oracle 8
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     Note:
rem     -----
rem     This script is only good for dumping blocks in the first
rem     extent of the segment.
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.

set pagesize 0
set def =
set def &

column  header_block new_value m_block
column  header_file new_value m_file

        header_block + &m_offset  header_block
        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    
     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