JL Computer Consultancy

Dumping an Oracle 7 database block

May 1999

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 Method:

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.

Back to Main Index of Topics

rem     Script:        dump_block.sql
rem     Author:        Jonathan Lewis
rem     Dated:         ca. 1997
rem     Purpose:       Dump a block close to the segment header
rem     Notes:
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
        )       dba
alter session set events 'immediate trace name blockdump level &m_dba';

Back to Main Index of Topics

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.
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
tsiz: 0x1fa0
hsiz: 0x114
pbl: 0x0087257c
bdba: 0x04000017
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[0]     nrow=10 offs=0         ### There are 10 rows in table 0, starting at row 0
0x12:pti[1]    nrow=5  offs=10        ### There are 5 rows in table 1, starting at row 10
0x16:pti[2]    nrow=5  offs=15        ### etc.
0x1a:pti[3]    nrow=9  offs=20
0x1e:pti[4]    nrow=11 offs=29
0x22:pti[5]    nrow=79 offs=40
0x26:pri[0]    offs=0x1f8a            ### Pointer/Row index to 1st row of table 0
0x28:pri[1]    offs=0x1ead
0x2a:pri[2]    offs=0x1e11
0x2c:pri[3]    offs=0x1d73
0x2e:pri[4]    offs=0x1cd4
0x30:pri[5]    offs=0x1c33
0x32:pri[6]    offs=0x18fe
0x34:pri[7]    offs=0x16b7
0x36:pri[8]    offs=0x1321
0x38:pri[9]    offs=0x11c6
0x3a:pri[10]   offs=0x1be7            ### Pointer/Row Index to 1st row of table 1
0x3c:pri[11]   offs=0x18b2
0x3e:pri[12]   offs=0x166b
0x40:pri[13]   offs=0x12d5
0x42:pri[14]   offs=0x117a
0x44:pri[15]   offs=0x1f5e            ### etc.
0x46:pri[16]   offs=0x1e84
0x48:pri[17]   offs=0x1de7
0x112:pri[118] offs=0xfb7
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-FL-- lb: 0x2 cc: 21 cki: 5     ### cki = Cluster Key Index (?)
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: [26]
 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

ack to Main Index of Topics