Re: interpreting block dump

From: koert54 <koert54_at_nospam.com>
Date: Tue, 14 May 2002 17:42:34 GMT
Message-ID: <e0cE8.78136$Ze.12018@afrodite.telenet-ops.be>

Good paper on block internals by Dan Hotka http://www.informit.com/isapi/product_id~{3E0BB63F-D402-40CA-A537-AF45CF8475 A8}/session_id~{72A5501D-3F3B-4C6C-8A4E-AC03E9193238}/content/index.asp

Example of block dump explained (for clustered table)

data block dump (oracle 8.0.4)

example cluster block dump

object id on block? y
seg/obj: 0x140 csc: 0x00.9d6 itc: 2 flg: - typ: 1 - data

fsl: 0 fnx: 0x0 ver: 0x01

 itl           xid                  uba         flag  lck        scn/fsc

0x01 0x0002.013.00000003 0x00800078.0000.07 c--- 0 scn 0x0000.000009d6
0x02 0x0002.011.00000003 0x00800077.0000.0f --u- 2 fsc 0x0000.000009e0
==> --u- flag denotes the rows are committed but no full cleanout has been
done

data_block_dump

tsiz: 0x7a0 ==> total data area size
hsiz: 0x1e ==> data header size (16+#tabs*2 + #rows*2)

pbl: 0x0109f2bc  ==> ptr to buffer holding the block
bdba: 0x00400722 ==> block dba / rdba
flag=------k--  ==> n=pctfree hit (clusters),f=dont put on freelist
                            k=flushable cluster keys
ntab=2   ==> n tables (>1 so this is a cluster)
nrow=4   ==> n rows
frre=-1   ==> first free row index entry, -1=you have to add one
fsbo=0x1e  ==> free space begin offset
fseo=0x768  ==> free space end offset

avsp=0x742 ==> available space in the block tosp=0x742 ==> total available space when all txs commit 0xe:pti[0] nrow=2 offs=0 ==> first table (cluster key) -.

0x12:pti[1] nrow=2 offs=2 ==> second table  |
0x16:pri[0] offs=0x78a     |   <-----------------------'
0x18:pri[1] offs=0x76e     |
0x1a:pri[2] offs=0x784   <-'
0x1c:pri[3] offs=0x768

block_row_dump:
tab 0, row 0, @0x78a
tl: 22 fb: k-h-fl-- lb: 0x0 cc: 1 ==> cluster key curc: 1 comc: 1 pk: 0x00400722.0 nk: 0x00400722.0
==> curc: current row count for this key in this block
==> comc: committed row count for this key in this block
==> pk: rowid of previous block for this cluster key.
==> nk: rowid of next block for this cluster key.
==> ^ blocks are linked in a chain for each key.
==> in this example the pk & nk point back to this block so
==> this is the only block for this cluster key.
col 0: [ 2] 52 31 ==> the cluster key value

tab 0, row 1, @0x76e
tl: 22 fb: k-h-fl-- lb: 0x0 cc: 1
curc: 1 comc: 1 pk: 0x00400722.1 nk: 0x00400722.1 col 0: [ 2] 52 32
==> this is a second cluster key in this block

tab 1, row 0, @0x784
tl: 6 fb: -ch-fl-- lb: 0x2 cc: 1 cki: 0
col 0: [ 1] 61
==> this is table data.
==> cki: shows us which cluster key this row is for
tab 1, row 1, @0x768
tl: 6 fb: -ch-fl-- lb: 0x2 cc: 1 cki: 1
col 0: [ 1] 61
==> table data for the second cluster key
end_of_block_dump

"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:3ce14702.92152618_at_news.globix.com...

> This is a dump of an index block.
> I need to interpret the actual data (my understanding it should be
> rowid, col1, col2 (it's a PK on two columns).
>
> Any hints, links appreciated.
>
> Thanx.
>
>
>
> *** 2002-05-14 11:12:39.954
> *** SESSION ID:(1597.11880) 2002-05-14 11:12:39.941
> Start dump data blocks tsn: 6 file#: 30 minblk 243909 maxblk 243909
> buffer tsn: 6 rdba: 0x0783b8c5 (30/243909)
> scn: 0x0000.7e377287 seq: 0x02 flg: 0x00 tail: 0x72870602
> frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
>
> Block header dump:  0x0783b8c5
>  Object id on Block? Y
>  seg/obj: 0x4a38  csc: 0x00.7e020f28  itc: 4  flg: -  typ: 2 - INDEX
>      fsl: 0  fnx: 0x0 ver: 0x01
>
>  Itl           Xid                  Uba         Flag  Lck
> Scn/Fsc
> 0x01   xid:  0x0004.014.0019b136    uba: 0x0081d8c6.5435.02  C---    0
> scn 0x0000.7b
> 260df8
> 0x02   xid:  0x0006.01c.000202ac    uba: 0x0f415996.0422.08  ----   30
> fsc 0x050a.00
> 000000
> 0x03   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0
> fsc 0x0000.00
> 000000
> 0x04   xid:  0x0000.000.00000000    uba: 0x00000000.0000.00  ----    0
> fsc 0x0000.00
> 000000
>
> Leaf block dump
> ===============
> header address 27579884=0x1a4d5ec
> kdxcolev 0
> kdxcolok 0
> kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
> kdxconco 2
> kdxcosdc 1
> kdxconro 49
> kdxcofbo 134=0x86
> kdxcofeo 1899=0x76b
> kdxcoavs 1765
> kdxlespl 0
> kdxlende 30
> kdxlenxt 50522048=0x302e7c0
> kdxleprv 96712106=0x5c3b5aa
> kdxledsz 6
> kdxlebksz 3888
> row#0[1899] flag: ---D-, lock: 2, data:(6):  02 01 96 f0 00 13
> col 0; len 24; (24):
>  30 30 32 30 66 30 31 39 63 61 35 35 37 34 33 62 64 30 30 39 37 64 30
> 33
> col 1; len 7; (7):  75 73 65 72 5f 69 64
> row#1[1940] flag: ---D-, lock: 2, data:(6):  02 01 96 f0 00 2c
> col 0; len 24; (24):
>  30 30 32 30 66 30 31 39 63 61 35 35 37 34 33 62 64 30 30 39 38 38 30
> 36
> col 1; len 7; (7):  75 73 65 72 5f 69 64
> ...
> .......
> We use Oracle 8.1.7.3 on Solaris 2.7 boxes
> remove NSPAM to email

Received on Tue May 14 2002 - 12:42:34 CDT