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.
rem rem Script: dump_block.sql rem Author: Jonathan Lewis rem Dated: ca. 1997 rem Purpose: Dump a block close to the segment header rem 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 rem
column header_file new_value m_file column header_block new_value m_block
select
header_file,
header_block
from
dba_segments
where
segment_name = upper('&m_segment_required')
and owner = upper('&m_segment_owner')
;
column dba new_value m_dba
select dbms_utility.make_data_block_address( &m_file, &m_block + &m_offset_required ) dba from dual;
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
data_block_dump =============== tsiz: 0x1fa0 hsiz: 0x114 pbl: 0x0087257c bdba: 0x04000017 flag=------K-- ntab=6 ### Number of tables in block nrow=119 ### Total number of row entries in block frre=-1 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 block_row_dump:
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 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
end_of_block_dump