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