Inside Index block – Oracle Database 11g

I ran into a scenario where I really wanted to check whats inside the leaf block of an index.

This post is to dig inside the index leaf block and find out various facts stored inside a block.

We will start with taking a dump of a block

SYS.ORCL>alter session set tracefile_identifier='INDEX_BLOCK_DUMP';
Session altered.
SYS.ORCL>alter system dump datafile 292 block 245790;
System altered.
SYS.ORCL>

You will get a trace file having name something like “<DB_NAME>_ora_<PID>_INDEX_BLOCK_DUMP.trc” in your trace directory

Even if you dont use tracefile_identifier, you can still see the name of the dump file using following query

SYS.ORCL>select value from v$diag_info where name = 'Default Trace File';
VALUE
-----------------------------------------------------------------
/dumps-01/diag/rdbms/orcl_b/orcl/trace/orcl_ora_11416.trc
1 row selected.
SYS.ORCL>

At the start of the block you will see following details

*********************************************************************
Start dump data blocks tsn: 17 file#:292 minblk 245790 maxblk 245790
Block dump from cache:
Dump of buffer cache at level 4 for tsn=17, rdba=1224982558
Block dump from disk:
buffer tsn: 17 rdba: 0x4903c01e (292/245790)
scn: 0x06ca.4d4684c7 seq: 0x01 flg: 0x04 tail: 0x84c70601
frmt: 0x02 chkval: 0xe178 type: 0x06=trans data
*********************************************************************

tsn –> Tablespace Number = 17
file# –> File # = 292
minblk & maxblk –> Min block and Max block numbers = 245790
rdba –> Relative data block address. Its value is 0x4903c01e in hexa decimal and 1224982558 in decimal.
We can get rdba using dbms_utility.make_data_block_address

SYS.ORCL>select dbms_utility.make_data_block_address(292,245790) from dual;
DBMS_UTILITY.MAKE_DATA_BLOCK_ADDRESS(292,245790)
------------------------------------------------
 1224982558

After above lines we have dump of memory. You will see a line something as below

Dump of memory from 0x00002B5C26C2DA00 to 0x00002B5C26C2FA00

So at each address it will print the content. Its very difficult to read this format.

2B5C26C2DA00 0000A206 4903C01E 4D4684C7 040106CA [.......I..FM....]
2B5C26C2DA10 0000E178 00000002 000551A1 4CFB5368 [x........Q..hS.L]
2B5C26C2DA20 000006CA 0032001D 4903C009 000F0046 [......2....IF...]
2B5C26C2DA30 00221BD6 01460CC8 0001F2FC 06CAC000 [.."...F.........]
2B5C26C2DA40 40FE45EE 001D00EE 0008E5F9 0086EC85 [.E.@............]
2B5C26C2DA50 003EC8BA 06CA8000 40FA9BFF 00000000 [..>........@....]
2B5C26C2DA60 00000000 00000000 00000000 00000000 [................]
2B5C26C2DA70 00000000 00100040 0027C6F8 0085DF28 [....@.....'.(...]

But once this memory dump is complete, we can see some readable information stored in index block. Here is what comes next

seg/obj: 0x551a1 csc: 0x6ca.4cfb5368 itc: 29 flg: E typ: 2 - INDEX
 brn: 0 bdba: 0x4903c009 ver: 0x01 opc: 0
 inc: 0 exflg: 0

seg/obj is the object_id. 0x551a1 = 348577 in 348577 in decimal.

SYS.ORCL>select object_name from dba_objects where data_object_id = 348577;
OBJECT_NAME
-------------------------
UNIQ_FPM_MSG_WHSE_CAT_ID
1 row selected.

This is the index for which we dumped the block.

csc: 0x6ca.4cfb5368 –> This is the comit SCN number of the block
itc: 29 –> Number of interested transaction list(ITL) entry in the block.
typ: 2 – INDEX –> Indicates this is an index block

Next comes the Interested Transaction List(ITL) entries. We had 29 ITL entries as indicated above by itc: 29

Itl  Xid                 Uba                Flag Lck   Scn/Fsc
0x01 0x0046.00f.00221bd6 0x01460cc8.f2fc.01 CB-- 0 scn 0x06ca.40fe45ee
0x02 0x00ee.01d.0008e5f9 0x0086ec85.c8ba.3e C--- 0 scn 0x06ca.40fa9bff
0x03 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x04 0x0040.010.0027c6f8 0x0085df28.cb0f.0b C--- 0 scn 0x06ca.40fb14e2
0x05 0x00f0.017.00090d20 0x37860557.c767.36 C--- 0 scn 0x06ca.40fb15a7
0x06 0x013d.020.0002205c 0x3802952d.202e.26 C--- 0 scn 0x06ca.40fd6d83
0x07 0x001d.012.0049340a 0x38063ebe.c0d6.48 C--- 0 scn 0x06ca.40fd6e3e
0x08 0x00b5.018.000e6ee9 0x384279bf.95f1.07 C--- 0 scn 0x06ca.40fe4336
0x09 0x010e.007.000a4514 0x0088535e.b343.19 C--- 0 scn 0x06ca.40fa9b08
0x0a 0x00df.00f.0009db56 0x014571c8.e27e.0a C--- 0 scn 0x06ca.40fe446d
0x0b 0x00f4.00b.00092665 0x37c65073.c520.27 C--- 0 scn 0x06ca.4100f955
0x0c 0x0129.017.00035f0e 0x0187980c.2a1a.04 C--- 0 scn 0x06ca.4100fa77
0x0d 0x007a.002.0010db5b 0x38458cb5.5bbd.20 C--- 0 scn 0x06ca.41168fad
0x0e 0x0069.00e.0017dd7d 0x3807b2e2.1390.30 C--- 0 scn 0x06ca.411691b0
0x0f 0x00d7.006.0009a7ee 0x38417fbc.dd00.19 C--- 0 scn 0x06ca.41169313
0x10 0x0181.003.000170b0 0x00c78696.190d.2b C--- 0 scn 0x06ca.41169bc6
0x11 0x00bd.014.000d2ddf 0x3805ab0d.72f9.19 C--- 0 scn 0x06ca.41169ecc
0x12 0x0001.019.00634f4c 0x3802e302.4fd4.0d C--- 0 scn 0x06ca.4116ab9e
0x13 0x0035.014.003a9a1d 0x014691a5.cda1.3d C--- 0 scn 0x06ca.4116ad76
0x14 0x017a.017.00018a03 0x37c4d213.17c4.0d C--- 0 scn 0x06ca.4116b388
0x15 0x006a.012.0017f16a 0x37878e97.329f.2b C--- 0 scn 0x06ca.4165b46c
0x16 0x00f0.01b.00090d4e 0x3846d24e.c76b.36 C--- 0 scn 0x06ca.4165b57c
0x17 0x0168.004.00019960 0x00c77875.18ab.08 C--- 0 scn 0x06ca.4165b7a5
0x18 0x0104.002.0006b6fd 0x38401fbe.a734.0e C--- 0 scn 0x06ca.4165b951
0x19 0x0044.018.00275fa5 0x38417b7e.cf86.3c C--- 0 scn 0x06ca.41035e03
0x1a 0x0127.01c.0006d44b 0x008602ca.5584.04 C--- 0 scn 0x06ca.40f95f82
0x1b 0x00ba.004.000dbc9a 0x3786425e.74f4.32 C--- 0 scn 0x06ca.40f96071
0x1c 0x0194.004.00014665 0x38464daf.1aca.46 C--- 0 scn 0x06ca.40f9c995
0x1d 0x0014.00c.0066b5d6 0x01865ee3.e323.08 C--- 0 scn 0x06ca.40f9ca31

Itl –> Slot Id of interested transaction
Xid –> Transaction ID od transaction occupying that ITL
Uba –> Undo block address
Flag –> State for current transaction (C – Commited)
Lck –> Number of Lock held by the transacion
Scn/Fsc –> SCN number of the current transaction

Once ITL list is displayed, next comes the leaf block dump.
At the start of leaf block dump it gives the header information of the block

Leaf block dump
===============
header address 47674787290348=0x2b5c26c2dcec     => Header start and end address
kdxcolev 0                                       => Index Level. 0 represent leaf block
KDXCOLEV Flags = - - - 
kdxcolok 0                                       => Indicates that no block transaction happening right now
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y    => Internal Operation code
kdxconco 3                                        => Index Column Count
kdxcosdc 8                                        => Count of index structural changes involving block
kdxconro 48                                       => Number of index records in this block
kdxcofbo 132=0x84                                 => Offset from start to the beginning of free space
kdxcofeo 1435=0x59b                               => Offset to end of free space (ie. first portion of block containing index data)
kdxcoavs 1303                                     => Available space in the block (effective space between kdxcofbo and kdxcofeo)
kdxlespl 0                                        => Bytes of uncommited data at the time of block split that have been cleaned out
kdxlende 0                                        => Number of deleted entries
kdxlenxt 830726637=0x3183e1ed                     => Pointer to next leaf block
kdxleprv 830721132=0x3183cc6c                     => Pointer to previous leaf block
kdxledsz 10                                       => Deleted space
kdxlebksz 7384                                    => Size of usable block space

After header, comes the index information as shown below

row#0[3602] flag: ------, lock: 0, len=61, data:(10): 00 0c 13 af 37 47 af ee 00 05
col 0; len 26; (26):
 35 35 39 32 35 38 5f 31 33 35 33 32 35 30 31 34 33 5f 36 38 5e 32 34 31 34
 30
col 1; len 4; (4): 4c 45 4a 31
col 2; len 16; (16): 43 68 61 6e 67 65 50 69 63 6b 53 74 61 74 75 73
row#1[3663] flag: ------, lock: 0, len=57, data:(10): 00 0c 13 af 37 47 af 24 00 42
col 0; len 26; (26):
 35 35 39 22 35 38 5f 31 33 32 33 32 35 30 32 32 33 5f 36 38 5f 32 34 31 34
 30
col 1; len 4; (4): 4c 45 4f 33
col 2; len 12; (12): 50 69 63 6b 43 6f 6d 70 6c 65 74 65

Here is what above values mean

row#0 -> First row in the block. Row starts from 0
[3602] -> Starting location within the block
flag: -> Flag to give information above the record in index leaf block. If you delete a row, the flag will be marked D, meaning deleted.
lock -> Lock information at row level
len -> Total length of a index record
data -> I guess this should be the length of rowid which is following this “00 0c 13 af 37 47 af ee 00 05”

Each row in this index has 3 columns. Columns start with col0. So we have column information in col0, col1 and col2

col0 -> First column
len -> Length of the column. 26 in this case.
Value after that is the actual value in base 16 stored in the block.

We can actually get real value for the values stored above using following function SQL.
You need to pass the hex value seen in block dump to a function dbms_stats.convert_raw_value and it returns the required values.

Lets say we want to find out the value for first row (Value for each column in first row)

SQL>var col1 varchar2(1000);
SQL>exec dbms_stats.convert_raw_value(replace('35 35 39 32 35 38 5f 31 33 32 33 32 35 30 30 32 33 5f 36 38 5f 32 34 31 34 30',' ',''),:col1);
PL/SQL procedure successfully completed.
SQL>var col2 varchar2(10);
SQL>exec dbms_stats.convert_raw_value(replace('4c 45 4a 31',' ',''),:col2);
PL/SQL procedure successfully completed.
SQL>var col3 varchar2(100);
SQL>exec dbms_stats.convert_raw_value(replace('43 68 61 6e 67 65 50 69 63 6b 53 74 61 74 75 73',' ',''),:col3);
PL/SQL procedure successfully completed.
COL1
-----------------------------
559258_1323250023_68_24140

COL2
-----------------------------
ABC

COL3
-----------------------------
EmployeeStatus
SQL>

Similarly you can write your own PLSQL code and find out all the values stored inside an index block.

Hope this helps !!