Dumping an Oracle 7 database block
A few months ago I posted a short script showing how to produce a symbolic dump of an Oracle 8 block. This note does the same for Oracle 7 blocks.
Once upon a time, in the days of Oracle 6, it was possible to read a rowid and translate it into the correct block address using a simple piece of arithmetic - the DBA (database block address) was equal to
block_id + file_id x power(2,N)
where N was platform dependent (possibly 26 for most Unix flavours, and 24 for VMS and AIX). This translation was a convenient and easy one for Oracle Corp when the database was limited to only 64 (or 62) data files, but with the advent of Oracle 7 came the enhancement that databases could go up to about 1024 data files.
For backwards compatibility it was desirable to keep the same functionality in place for the first 64 files, but what could be done about the rest ? The answer was to fiddle about with byte-swapping and bit-shifting, which made life a little difficult for me until Oracle introduced a couple of procedures to help out.
If you haven't previously found the dbms_util package (in script $ORACLE_HOME/rdbms/admin/dbmsutil.sql) now is the time to look at it - and keep revisiting it each time your version of Oracle changes. One of the procedures in this package under Version 7 of Oracle is the procedure make_data_block_address which returns the absolute database block address given the file id and block id, and this function can be used to help you dump Oracle addresses. Of course, this is has all changed with Oracle 8, but this note is only about Oracle 7.
The basic method is to call the blockdump event which uses the following syntax under SQL*Plus.
alter session set events 'immediate trace name blockdump level NNNN';
where the value NNNN is the block address of the block you want to dump.
Since the blocks I want to look at are typically the segment header blocks, or the first couple of blocks in a table or index segment I have embedded this statement is a little script that picks up segment header information from dba_segments, requests an offset, calculates the required block address and dumps it.
A sample of a dumped table block appears at the end of this note.
rem Script: dump_block.sql
rem Author: Jonathan Lewis
rem Dated: ca. 1997
rem Purpose: Dump a block close to the segment header
rem Make sure that TERMOUT is on, or you won't see the prompt.
rem The user must have SELECT on the dba_segments view, and
rem EXECUTE on the DBMS_UTILITY package
column header_file new_value m_file
column header_block new_value m_block
segment_name = upper('&m_segment_required')
and owner = upper('&m_segment_owner')
column dba new_value m_dba
&m_block + &m_offset_required
alter session set events 'immediate trace name blockdump level &m_dba';
Sample of output
This sample is a block dump from the first data block (i.e. offset = 1) of the C_OBJ$ cluster. I chose this as an example of how much goes into a single block, especially when there are multiple tables in the block.
I have added a few blank lines to the dump to aid clarity, and a few comments preceded by ###
*** SESSION ID:(29.64) 1999.05.17.11.10.26.000
buffer dba: 0x04000017 inc: 0x00000801 seq: 0x0000007c
ver: 1 type: 6=trans data
Block header dump: dba: 0x04000017
Object id on Block? Y
seg/obj: 0x1 csc: 0x00.34 itc: 2 flg: - typ: 1 - DATA
fsl: 0 fnx: 0x0
### itc = interested transaction count
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.030.00000002 0x04000004.0000.3e C--- 0 scn 0x0000.00000034
0x02 0x0000.022.00000002 0x0400000b.0000.66 --U- 109 fsc 0x0000.0000004f
### The ITL - interested transaction list. one committed and showing an SCN
### the other committed under a fast commit (delayed logging) and apparently
### showing a freed space value, but in fact showing the SCN
ntab=6 ### Number of tables in block
nrow=119 ### Total number of row entries in block
fsbo=0x114 ### Free Space Beginning Of
fseo=0xf78 ### Free Space End Of
avsp=0xe64 ### Available space
tosp=0xe64 ### Total space
0xe:pti nrow=10 offs=0 ### There are 10 rows in table 0, starting at row 0
0x12:pti nrow=5 offs=10 ### There are 5 rows in table 1, starting at row 10
0x16:pti nrow=5 offs=15 ### etc.
0x1a:pti nrow=9 offs=20
0x1e:pti nrow=11 offs=29
0x22:pti nrow=79 offs=40
0x26:pri offs=0x1f8a ### Pointer/Row index to 1st row of table 0
0x3a:pri offs=0x1be7 ### Pointer/Row Index to 1st row of table 1
0x44:pri offs=0x1f5e ### etc.
tab 0, row 0, @0x1f8a ### 1st (0th) row of table 0 (a clusted key value)
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1 ### pk/nk - previous/next key occurence in the form
curc: 6 comc: 6 pk: 0x04000017.0 nk: 0x04000017.0 ### block_id.row index
col 0: [ 2] c1 08 ### In this block pk= nk=current row, so no chain.
tab 0, row 1, @0x1ead
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1 ### tl = total length, cc = column count
curc: 4 comc: 4 pk: 0x04000017.1 nk: 0x04000017.1
col 0: [ 2] c1 06
tab 0, row 2, @0x1e11
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 4 comc: 4 pk: 0x04000017.2 nk: 0x04000017.2
col 0: [ 2] c1 18
tab 0, row 9, @0x11c6
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 16 comc: 16 pk: 0x04000017.9 nk: 0x04000017.9
col 0: [ 2] c1 16
tab 1, row 0, @0x1be7 ### 1st (0th) row of table 1
tl: 76 fb: -CH-
lb: 0x2 cc: 21 cki: 5 ### cki = Cluster Key Index (?) FL--
col 0: [ 1] 80 ### i.e. row 5 of table 0
col 1: [ 2] c1 02
col 2: [ 2] c1 17
col 3: [ 2] c1 02
col 4: [ 2] c1 04
col 5: [ 2] c1 14
col 6: [ 2] c1 02
col 7: [ 1] 80
col 8: [ 1] 80
col 9: [ 1] 80
col 10: [ 1] 80
col 11: [ 2] c1 02
col 12: 
2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d 2d
col 13: *NULL*
col 14: *NULL*
col 15: [ 1] 80
col 16: [ 1] 80
col 17: [ 1] 80
col 18: [ 1] 80
col 19: [ 1] 80
col 20: [ 1] 80