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 !!
Persistent Storage Solutions 