Dump leaf block of Oracle indexes, oracleleaf

Dump leaf block of Oracle indexes, oracleleaf

Dump the Oracle index leaf block

F: \ oracle \ product \ 10.2.0 \ db_1 \ BIN> sqlplus/as sysdbaSQL * Plus: Release 10.2.0.4.0-Production on Thu Apr 9 06:28:17 2015 Copyright (c) 1982,200 7, Oracle. all Rights Reserved. connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options06: 39: 43 sys @ FS> alter session set events 'immediate trace name treedump level 100'; ----> This 95503 is the object_idSession altered of the index. elapsed: 00:00:01. 3806: 39: 52 sys @ FS>

 

----> Get the trc file under udump

Dump file f: \ oracle \ product \ 10.2.0 \ admin \ fs \ udump \ fs_ora_4544.trcThu Apr 09 06:39:51 2015 ORACLE V10.2.0.4.0-64bit Production vsnsta = 0 vsnsql = 14 vsnxtr = 3 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 -64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsWindows NT Version V6.1 CPU: 4-type 8664, 2 Physical CoresProcess Affinity: 0x0000000000000000Memory (Avail/Total): Ph: 4189 M/7950 M, ph + PgF: 11446 M/16140 MInstance name: fsRedo thread mounted by this instance: 1 Oracle process number: 24 Windows thread id: 4544, image: ORACLE. EXE (SHAD) *** 06:39:51. 156 *** action name :() 06:39:51. 138 *** module name :( sqlplus.exe) 06:39:51. 138 *** service name :( SYS $ USERS) 06:39:51. 138 *** session id: (1074.5) 06:39:51. 138 ----- begin tree dumpbranch: 0x100150c 16782604 (0: nrow: 178, level: 1) leaf: 0x100150d 16782605 (-1: nrow: 288 rrow: 288) leaf: 0x100150e 16782606 (0: nrow: 289 rrow: 289) leaf: 0x100150f 16782607 (1: nrow: 288 rrow: 288) ---> in this example, 0x100150f is a hexadecimal DBA; 16782607 this is a decimal DBA leaf: 0x1001510 16782608 (2: nrow: 288 rrow: 288) leaf: 0x1001511 16782609 (3: nrow: 288 rrow: 288) leaf: 0x1001512 16782610 (4: nrow: 288 rrow: 288) leaf: 0x1001513 16782611 (5: nrow: 288 rrow: 288) leaf: 0x1001514 16782612 (6: nrow: 288 rrow: 288) leaf: 0x1001515 16782613 (7: nrow: 288 rrow: 288) leaf: 0x1001516 16782614 (8: nrow: 288 rrow: 288) leaf: 0x1001517 16782615 (9: nrow: 288 rrow: 288) leaf: 0x1001518 16782616 (10: nrow: 288 rrow: 288) leaf: 0x100151a 16782618 (11: nrow: 288 rrow: 288) leaf: 0x100151b 16782619 (12: nrow: 288 rrow: 288) leaf: 0x100151c 16782620 (13: nrow: 288 rrow: 288) leaf: 0x100151d 16782621 (14: nrow: 288 rrow: 288) leaf: 0x100151e 16782622 (15: nrow: 288 rrow: 288) leaf: 0x100151f 16782623 (16: nrow: 288 rrow: 288) leaf: 0x1001520 16782624 (17: nrow: 288 rrow: 288) leaf: 0x1001521 16782625 (18: nrow: 288 rrow: 288) leaf: 0x1001522 16782626 (19: nrow: 288 rrow: 288) leaf: 0x1001523 16782627 (20: nrow: 288 rrow: 288) leaf: 0x1001524 16782628 (21: nrow: 288 rrow: 288) leaf: 0x1001525 16782629 (22: nrow: 288 rrow: 288) leaf: 0x1001526 16782630 (23: nrow: 288 rrow: 288) leaf: 0x1001527 16782631 (24: nrow: 288 rrow: 288) leaf: 0x1001528 16782632 (25: nrow: 288 rrow: 288) leaf: 0x100152a 16782634 (26: nrow: 288 rrow: 288) leaf: 0x100152b 16782635 (27: nrow: 288 rrow: 288) leaf: 0x100152c 16782636 (28: nrow: 288 rrow: 288) leaf: 0x100152d 16782637 (29: nrow: 288 rrow: 288) leaf: 0x100152e 16782638 (30: nrow: 288 rrow: 288) leaf: 0x100152f 16782639 (31: nrow: 288 rrow: 288) leaf: 0x1001530 16782640 (32: nrow: 288 rrow: 288) leaf: 0x1001531 16782641 (33: nrow: 288 rrow: 288) leaf: 0x1001532 16782642 (34: nrow: 288 rrow: 288) leaf: 0x1001533 16782643 (35: nrow: 288 rrow: 288) leaf: 0x1001534 16782644 (36: nrow: 288 rrow: 288) leaf: 0x1001535 16782645 (37: nrow: 288 rrow: 288) leaf: 0x1001536 16782646 (38: nrow: 288 rrow: 288) leaf: 0x1001537 16782647 (39: nrow: 288 rrow: 288) leaf: 0x1001538 16782648 (40: nrow: 288 rrow: 288) leaf: 0x100153a 16782650 (41: nrow: 288 rrow: 288) leaf: 0x100153b 16782651 (42: nrow: 288 rrow: 288) leaf: 0x100153c 16782652 (43: nrow: 288 rrow: 288) leaf: 0x100153d 16782653 (44: nrow: 288 rrow: 288) leaf: 0x100153e 16782654 (45: nrow: 288 rrow: 288) leaf: 0x100153f 16782655 (46: nrow: 288 rrow: 288) leaf: 0x1001540 16782656 (47: nrow: 288 rrow: 288) leaf: 0x1001541 16782657 (48: nrow: 288 rrow: 288) leaf: 0x1001542 16782658 (49: nrow: 288 rrow: 288) leaf: 0x1001543 16782659 (50: nrow: 288 rrow: 288) leaf: 0x1001544 16782660 (51: nrow: 288 rrow: 288) leaf: 0x1001545 16782661 (52: nrow: 288 rrow: 288) leaf: 0x1001546 16782662 (53: nrow: 288 rrow: 288) leaf: 0x1001547 16782663 (54: nrow: 288 rrow: 288) leaf: 0x1001548 16782664 (55: nrow: 288 rrow: 288) leaf: 0x100154a 16782666 (56: nrow: 288 rrow: 288) leaf: 0x100154b 16782667 (57: nrow: 288 rrow: 288) leaf: 0x100154c 16782668 (58: nrow: 288 rrow: 288) leaf: 0x100154d 16782669 (59: nrow: 288 rrow: 288) leaf: 0x100154e 16782670 (60: nrow: 288 rrow: 288) leaf: 0x100154f 16782671 (61: nrow: 288 rrow: 288) leaf: 0x1001550 16782672 (62: nrow: 288 rrow: 288) leaf: 0x1001551 16782673 (63: nrow: 288 rrow: 288) leaf: 0x1001552 16782674 (64: nrow: 288 rrow: 288) leaf: 0x1001553 16782675 (65: nrow: 288 rrow: 288) leaf: 0x1001554 16782676 (66: nrow: 288 rrow: 288) leaf: 0x1001555 16782677 (67: nrow: 288 rrow: 288) leaf: 0x1001556 16782678 (68: nrow: 288 rrow: 288) leaf: 0x1001557 16782679 (69: nrow: 288 rrow: 288) leaf: 0x1001558 16782680 (70: nrow: 288 rrow: 288) leaf: 0x100155a 16782682 (71: nrow: 288 rrow: 288) leaf: 0x100155b 16782683 (72: nrow: 288 rrow: 288) leaf: 0x100155c 16782684 (73: nrow: 288 rrow: 288) leaf: 0x100155d 16782685 (74: nrow: 288 rrow: 288) leaf: 0x100155e 16782686 (75: nrow: 288 rrow: 288) leaf: 0x100155f 16782687 (76: nrow: 288 rrow: 288) leaf: 0x1001560 16782688 (77: nrow: 288 rrow: 288) leaf: 0x1001561 16782689 (78: nrow: 288 rrow: 288) leaf: 0x1001562 16782690 (79: nrow: 288 rrow: 288) leaf: 0x1001563 16782691 (80: nrow: 288 rrow: 288) leaf: 0x1001564 16782692 (81: nrow: 288 rrow: 288) leaf: 0x1001565 16782693 (82: nrow: 288 rrow: 288) leaf: 0x1001566 16782694 (83: nrow: 288 rrow: 288) leaf: 0x1001567 16782695 (84: nrow: 288 rrow: 288) leaf: 0x1001568 16782696 (85: nrow: 288 rrow: 288) leaf: 0x100156a 16782698 (86: nrow: 288 rrow: 288) leaf: 0x100156b 16782699 (87: nrow: 288 rrow: 288) leaf: 0x100156c 16782700 (88: nrow: 288 rrow: 288) leaf: 0x100156d 16782701 (89: nrow: 288 rrow: 288) leaf: 0x100156e 16782702 (90: nrow: 288 rrow: 288) leaf: 0x100156f 16782703 (91: nrow: 288 rrow: 288) leaf: 0x1001570 16782704 (92: nrow: 288 rrow: 288) leaf: 0x1001571 16782705 (93: nrow: 288 rrow: 288) leaf: 0x1001572 16782706 (94: nrow: 288 rrow: 288) leaf: 0x1001573 16782707 (95: nrow: 288 rrow: 288) leaf: 0x1001574 16782708 (96: nrow: 288 rrow: 288) leaf: 0x1001575 16782709 (97: nrow: 288 rrow: 288) leaf: 0x1001576 16782710 (98: nrow: 288 rrow: 288) leaf: 0x1001577 16782711 (99: nrow: 288 rrow: 288) leaf: 0x1001578 16782712 (100: nrow: 288 rrow: 288) leaf: 0x100157a 16782714 (101: nrow: 288 rrow: 288) leaf: 0x100157b 16782715 (102: nrow: 288 rrow: 288) leaf: 0x100157c 16782716 (103: nrow: 288 rrow: 288) leaf: 0x100157d 16782717 (104: nrow: 288 rrow: 288) leaf: 0x100157e 16782718 (105: nrow: 288 rrow: 288) leaf: 0x100157f 16782719 (106: nrow: 288 rrow: 288) leaf: 0x1001580 16782720 (107: nrow: 288 rrow: 288) leaf: 0x1001581 16782721 (108: nrow: 288 rrow: 288) leaf: 0x1001582 16782722 (109: nrow: 288 rrow: 288) leaf: 0x1001583 16782723 (110: nrow: 288 rrow: 288) leaf: 0x1001584 16782724 (111: nrow: 288 rrow: 288) leaf: 0x1001585 16782725 (112: nrow: 288 rrow: 288) leaf: 0x1001586 16782726 (113: nrow: 288 rrow: 288) leaf: 0x1001587 16782727 (114: nrow: 288 rrow: 288) leaf: 0x1001588 16782728 (115: nrow: 288 rrow: 288) leaf: 0x100158b 16782731 (116: nrow: 288 rrow: 288) leaf: 0x100158c 16782732 (117: nrow: 288 rrow: 288) leaf: 0x100158d 16782733 (118: nrow: 288 rrow: 288) leaf: 0x100158e 16782734 (119: nrow: 288 rrow: 288) leaf: 0x100158f 16782735 (120: nrow: 289 rrow: 289) leaf: 0x1001590 16782736 (121: nrow: 289 rrow: 289) leaf: 0x1001591 16782737 (122: nrow: 288 rrow: 288) leaf: 0x1001592 16782738 (123: nrow: 288 rrow: 288) leaf: 0x1001593 16782739 (124: nrow: 288 rrow: 288) leaf: 0x1001594 16782740 (125: nrow: 288 rrow: 288) leaf: 0x1001595 16782741 (126: nrow: 288 rrow: 288) leaf: 0x1001596 16782742 (127: nrow: 288 rrow: 288) leaf: 0x1001597 16782743 (128: nrow: 288 rrow: 288) leaf: 0x1001598 16782744 (129: nrow: 288 rrow: 288) leaf: 0x1001599 16782745 (130: nrow: 288 rrow: 288) leaf: 0x100159a 16782746 (131: nrow: 288 rrow: 288) leaf: 0x100159b 16782747 (132: nrow: 288 rrow: 288) leaf: 0x100159c 16782748 (133: nrow: 288 rrow: 288) leaf: 0x100159d 16782749 (134: nrow: 288 rrow: 288) leaf: 0x100159e 16782750 (135: nrow: 288 rrow: 288) leaf: 0x100159f 16782751 (136: nrow: 288 rrow: 288) leaf: 0x10015a0 16782752 (137: nrow: 288 rrow: 288) leaf: 0x10015a1 16782753 (138: nrow: 288 rrow: 288) leaf: 0x10015a2 16782754 (139: nrow: 288 rrow: 288) leaf: 0x10015a3 16782755 (140: nrow: 288 rrow: 288) leaf: 0x10015a4 16782756 (141: nrow: 288 rrow: 288) leaf: 0x10015a5 16782757 (142: nrow: 288 rrow: 288) leaf: 0x10015a6 16782758 (143: nrow: 288 rrow: 288) leaf: 0x10015a7 16782759 (144: nrow: 288 rrow: 288) leaf: 0x10015a8 16782760 (145: nrow: 288 rrow: 288) leaf: 0x10015a9 16782761 (146: nrow: 288 rrow: 288) leaf: 0x10015aa 16782762 (147: nrow: 288 rrow: 288) leaf: 0x10015ab 16782763 (148: nrow: 288 rrow: 288) leaf: 0x10015ac 16782764 (149: nrow: 288 rrow: 288) leaf: 0x10015ad 16782765 (150: nrow: 288 rrow: 288) leaf: 0x10015ae 16782766 (151: nrow: 288 rrow: 288) leaf: 0x10015af 16782767 (152: nrow: 288 rrow: 288) leaf: 0x10015b0 16782768 (153: nrow: 288 rrow: 288) leaf: 0x10015b1 16782769 (154: nrow: 288 rrow: 288) leaf: 0x10015b2 16782770 (155: nrow: 297 rrow: 297) leaf: 0x10015b3 16782771 (156: nrow: 299 rrow: 299) leaf: 0x10015b4 16782772 (157: nrow: 298 rrow: 298) leaf: 0x10015b5 16782773 (158: nrow: 296 rrow: 296) leaf: 0x10015b6 16782774 (159: nrow: 299 rrow: 299) leaf: 0x10015b7 16782775 (160: nrow: 299 rrow: 299) leaf: 0x10015b8 16782776 (161: nrow: 297 rrow: 297) leaf: 0x10015b9 16782777 (162: nrow: 297 rrow: 297) leaf: 0x10015ba 16782778 (163: nrow: 297 rrow: 297) leaf: 0x10015bb 16782779 (164: nrow: 295 rrow: 295) leaf: 0x10015bc 16782780 (165: nrow: 296 rrow: 296) leaf: 0x10015bd 16782781 (166: nrow: 297 rrow: 297) leaf: 0x10015be 16782782 (167: nrow: 298 rrow: 298) leaf: 0x10015bf 16782783 (168: nrow: 300 rrow: 300) leaf: 0x10015c0 16782784 (169: nrow: 300 rrow: 300) leaf: 0x10015c1 16782785 (170: nrow: 296 rrow: 296) leaf: 0x10015c2 16782786 (171: nrow: 296 rrow: 296) leaf: 0x10015c3 16782787 (172: nrow: 300 rrow: 300) leaf: 0x10015c4 16782788 (173: nrow: 298 rrow: 298) leaf: 0x10015c5 16782789 (174: nrow: 293 rrow: 293) leaf: 0x10015c6 16782790 (175: nrow: 301 rrow: 301) leaf: 0x10015c7 16782791 (176: nrow: 1 rrow: 1) ----- end tree dump

 

06:56:42 sys@FS> execute :file#:=dbms_utility.data_block_address_file(to_number('100150f','xxxxxxxxx'));PL/SQL procedure successfully completed.Elapsed: 00:00:00.0006:57:09 sys@FS> execute :block#:=dbms_utility.data_block_address_block(to_number('100150f','xxxxxxxxx'));PL/SQL procedure successfully completed.Elapsed: 00:00:00.0006:57:21 sys@FS> print file#     FILE#----------         406:57:27 sys@FS>  print block#    BLOCK#----------      5391

 

Exit sqlplus and enter sqlplus again (in order to be generated in a new trc)

F:\oracle\product\10.2.0\db_1\BIN>sqlplus / as sysdbaSQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 9 06:59:56 2015Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options06:59:57 sys@FS> alter system dump datafile 4 block 5391;System altered.Elapsed: 00:00:00.1407:00:00 sys@FS>

 

Get the trc file under udump: The file has more than 1600 lines, so not all are listed. Search for this file with the keyword "Leaf block dump:

Leaf block dump =================== header address 128074340 = 0x7a241_kdxcolev0 kdxcolevflags =---kdxcolok 0 kdxcoopc 0x80: opcode = 0: iot flags = --- is converted = Ykdxconco 3 kdxcosdc 0 kdxconro 288 kdxcofbo 612 = running 1442 running 0 kdxlende 0 kdxlenxt 830 = running 16782608 = running 0 kdxlebksz 8036row #0 [16782606] flag: ------, lock: 0, len = 23col 0; len 5; (5): 31 30 36 31 31 --------------> This is the index column value col 1; len 7; (7 ): 78 73 04 08 0f 3c 19 ---------> This is the index column value col 2; len 6; (6 ): 01 00 14 26 01 66 ------------> This Is The hexadecimal rowid row #1 [7990] flag: ------, lock: 0, len = 23col 0; len 5; (5 ): 31 30 36 31 32col 1; len 7; (7): 78 73 04 08 0f 3c 19col 2; len 6; (6 ): 01 00 14 26 01 67row #2 [7967] flag: ------, lock: 0, len = 23col 0; len 5; (5): 31 30 36 31 33col 1; len 7; (7): 78 73 04 08 0f 3c 19col 2; len 6; (6): 01 00 14 26 01 68row #3 [7944] flag: ------, lock: 0, len = 23col 0; len 5; (5): 31 30 36 31 34col 1; len 7; (7): 78 73 04 08 0f 3c 19col 2; len 6; (6 ): 01 00 14 26 01 69 ..................................... row #287 [1442] flag: ------, lock: 0, len = 23col 0; len 5; (5): 31 30 38 37 30col 1; len 7; (7 ): 78 73 04 08 0f 3c 19col 2; len 6; (6): 01 00 14 27 00 eb ----- end of leaf block dump ----- End dump data blocks tsn: 4 file #: 4 minblk 5391 maxblk 5391

 

Next we will analyze the hexadecimal rowid:

01 00 14 26 01 66 ---> This is hexadecimal 00000001 00000000 00010100 00100110 00000001 01100110 ---> swap conversion, etc.) 00000001 00 ----> file number: 4 ---------------------------------------> 4 is the value after 10 entries (converted using calc.exe) 000000 00010100 00100110 -----> block number: 5158 -------------------------> swap conversion) 00000001 01100110 ----> row number: 358 ---------------> 358is the value after the conversion into 10 (use calc.exe to convert) the first 10 digits indicate the 22 digits in the middle of the file number. The last 16 digits indicate the row number.

 

The following two index values are analyzed:

Col 0; len 5; (5): 31 30 36 31 31 --------------> This is the index column value col 1; len 7; (7 ): 78 73 04 08 0f 3c 19 ---------> This is the value of the index column. Use the f_get_from_dump function to convert the above value (this function comes from travel master: http://www.traveldba.com/wp-content/uploads/scripts/f_get_from_dump. SQL) F: \ oracle \ product \ 10.2.0 \ db_1 \ BIN> sqlplus/as sysdbaSQL * Plus: Release 10.2.0.4.0-Production on Fri Apr 10 13:32:40 2015 Copyright (c) 1982,200 7, Oracle. all Rights Reserved. connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit Production13: 32: 41 sys @ FS> select scott. f_get_from_dump ('31, 30,36, 31,31 ', 'varchar2') from dual; SCOTT. f_GET_FROM_DUMP ('31, 30,36, 31,31 ', 'varchar2') limit 10611 ----------------------------------------------------> the actual value of the index column Elapsed: 00:00:00. 0013:32:58 sys @ FS> select scott. f_get_from_dump ('78, 73,04, 08, 0f, 3c, 19', 'date') from dual; SCOTT. f_GET_FROM_DUMP ('78, 08, 0F, 3C, 19', 'date') 14:59:24 ------------------------------------------------------> the actual index column value Elapsed: 00:00:00. 0013:33:10 sys @ FS> or use this function: 13: 33: 10 sys @ FS> select utl_raw.cast_to_varchar2 ('123') name from dual; NAME --------------------------------------- ---------------------------------------- ---10611