JL Computer Consultancy

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

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;

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