Oracle Indexed Dump leaf block

The Oracle index leaf block is dump

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 20 15Copyright (c) 1982, Oracle.  

----> Get a TRC file under Udump

Dump file F:\oracle\product\10.2.0\admin\fs\udump\fs_ora_4544.trcThu Apr 06:39:51 2015ORACLE v10.2.0.4.0-64bit Production vsnsta=0vsnsql=14 vsnxtr=3oracle 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:4189m/7950m, Ph+PgF:11446M/16140MI Nstance Name:fsredo thread mounted by this instance:1oracle process number:24windows thread id:4544, image:oracle. EXE (SHAD) * * * 2015-04-09 06:39:51.156*** ACTION Name: () 2015-04-09 06:39:51.138*** MODULE NAME: (sqlplus.exe) 2015-04-09 0 6:39:51.138*** SERVICE NAME: (sys$users) 2015-04-09 06:39:51.138*** SESSION ID: (1074.5) 2015-04-09 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)---> With this behavior example: 0x100150f This is the 16 binary dba;16782607 This is the 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 Rro w:288) leaf:0x1001515 16782613 (7:nrow:288 rrow:288) leaf:0x1001516 16782614 (8:nrow:288 rrow:288) leaf:0x 1001517 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 R row: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 1678 2625 (18:nrow:288 Rrow: 288) leaf:0x1001522 16782626 (19:nrow:288 rrow:288) leaf:0x1001523 16782627 (20:nrow:288 rrow:288) leaf:0 x1001524 16782628 (21:nrow:288 rrow:288) leaf:0x1001525 16782629 (22:nrow:288 rrow:288) leaf:0x1001526 1678263  0 (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) le AF:0X100152C 16782636 (28:nrow:288 rrow:288) leaf:0x100152d 16782637 (29:nrow:288 rrow:288) leaf:0x100152e 16 782638 (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:0x10015 16782645 (37:nrow:2rrow: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:NR ow:288 rrow:288) leaf:0x100153e 16782654 (45:nrow:288 rrow:288) leaf:0x100153f 16782655 (46:nrow:288 rrow:28 8) leaf:0x1001540 16782656 (47:nrow:288 rrow:288) leaf:0x1001541 16782657 (48:nrow:288 rrow:288) leaf:0x100 1542 16782658 (49:nrow:288 rrow:288) leaf:0x1001543 16782659 (50:nrow:288 rrow:288) leaf:0x1001544 16782660 (5 1:nrow:288 rrow:288) leaf:0x1001545 16782661 (52:nrow:288 rrow:288) leaf:0x1001546 16782662 (53:nrow:288 Rro w: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:0x1 00154f 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 R row: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 1678 2680 (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) le af:0x1001563 16782691 (80:nrow:288 rrow:288) leaf:0x1001564 16782692 (81:nrow:288 rrow:288) leaf:0x1001565 16 782693 (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:0x10015  6d 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:0x100157216782706 (94:nrow:288 rrow:288) leaf:0x1001573 16782707 (95:nrow:288 rrow:288) leaf:0x1001574 16782708 (96:NR ow:288 rrow:288) leaf:0x1001575 16782709 (97:nrow:288 rrow:288) leaf:0x1001576 16782710 (98:nrow:288 rrow:28 8) leaf:0x1001577 16782711 (99:nrow:288 rrow:288) leaf:0x1001578 16782712 (100:nrow:288 rrow:288) leaf:0x10  0157a 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:2   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:0x1001 583 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:0x10  01588 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:2   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:0x1001 593 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:2   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:0x1001 5A1 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:0x10015a A 16782762 (147:nrow:288 rrow:288) leaf:0x10015ab 16782763 (148:nrow:288 rrow:288) leaf:0x10015ac 16782764 (14 9:nrow:288 rrow:288)  LEAF:0X10015AD 16782765 (150:nrow:288 rrow:288) leaf:0x10015ae 16782766 (151:nrow:288 rrow:288) leaf:0x1001 5AF 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:0x10015b 8 16782776 (161:nrow:297 rrow:297) leaf:0x10015b9 16782777 (162:nrow:297 rrow:297) leaf:0x10015ba 16782778 (16 3:nrow:297 rrow:297) leaf:0x10015bb 16782779 (164:nrow:295 rrow:295) LEAF:0X10015BC 16782780 (165:nrow:296 R row:296) leaf:0x10015bd 16782781 (166:nrow:297 rrow:297) leaf:0x10015be 16782782 (167:nrow:298 rrow:298) le AF: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:0x10015c 6 16782790 (175:nrow:301 rrow:301) leaf:0x10015c7 16782791 (176:nrow:1 rrow:1)-----End Tree Dump

06:56:42 [Email protected]> execute:file#:=dbms_utility.data_block_address_file (to_number (' 100150f '), ' Xxxxxxxxx '));P L/sql procedure successfully completed. elapsed:00:00:00.0006:57:09 [Email protected]> execute:block#:=dbms_utility.data_block_address_block (to_number (' 100150f ', ' xxxxxxxxx ')); PL/SQL procedure successfully completed. elapsed:00:00:00.0006:57:21 [email protected]> print file#     file#----------         406:57:27 [email protected] >  print block#    block#----------      5391

Exit Sqlplus and re-enter Sqlplus (in order to generate 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 20 15Copyright (c) 1982, Oracle.  All rights reserved.connected to:oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit productionwith the part Itioning, OLAP, Data Mining and Real application testing options06:59:57 [email protected]> alter system dump DATAFILE 4 block 5391; System altered. elapsed:00:00:00.1407:00:00 [Email protected]>

Obtain TRC file under Udump: The file has more than 1600 lines, so not all listed. Search for the file with the "Leaf block dump" keyword:

Leaf block Dump===============header address 128074340=0x7a24264kdxcolev 0KDXCOLEV Flags =---kdxcolok 0kdxcoopc 0x80:o Pcode=0:iot flags=---is Converted=ykdxconco 3kdxcosdc 0kdxconro 288kdxcofbo 612=0x264kdxcofeo 1442=0x5a2kdxcoavs 830KDXLESPL 0kdxlende 0kdxlenxt 16782608=0x1001510kdxleprv 16782606=0x100150ekdxledsz 0kdxlebksz 8036row#0[8013] Flag :------, lock:0, Len=23col 0; Len 5; (5):--------------> This is the value of the indexed column Col 1; Len 7; (7): 0f 3c---------> This is the value of the indexed column Col 2; Len 6; (6):------------> This is 16 binary rowid row#1[7990] flag:------, lock:0, Len=23col 0; Len 5; (5): 32col 1; Len 7; (7): 0f 3c 19col 2; Len 6; (6): 67row#2[7967] Flag:------, lock:0, Len=23col 0; Len 5; (5): 33col 1; Len 7; (7): 0f 3c 19col 2; Len 6; (6): 68row#3[7944] Flag:------, lock:0, Len=23col 0; Len 5; (5): 34col 1; Len 7; (7): 0f 3c 19col 2; Len 6; (6): 69.....................................row#287[1442] Flag:------, lock:0, Len=23col 0; Len 5; (5): 30col 1; Len 7; (7): 0f 3c 19col 2; Len 6; (6): The EB-----End of leaf block dump-----End dump Data blocks Tsn:4 file#: 4 minblk 5391 maxblk 5391

The following is an analysis of the 16 binary ROWID:

  ---> This is 16 binary 00000001 00000000 00010100 00100110 00000001 01100110  ---> This is converted into 2 after the (01 with calc.exe conversion, 00 with calc.exe conversion, 14 with calc.exe conversion, etc.) 00000001----> File number: 4---------------------------- --------->4 is the value converted into 10 binary (using calc.exe conversion)           000000 00010100 00100110-----> Block Number: 5158------------------------- >5158 is the value converted to 10 (using calc.exe conversion)                                    00000001 01100110   ----> Line number: 358-------------> 358 is the value converted into 10 binary (using calc.exe conversion) The top ten means the file number is the middle 22 bits represents the last 16 bits of the block number represents the line number

The following is an analysis of 2 index values:

Col 0; Len 5; (5):--------------> This is the value of the indexed column Col 1; Len 7; (7): 0f 3c---------> This is the value of the indexed column using the function F_get_from_dump convert the above value (the function 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 13:32:40 2015Copyright (c) 1982, Oracle. All rights reserved.connected to:oracle Database 10g Enterprise Edition Release 10.2.0.4.0-64bit production13:32:41 [EMA Il protected]> 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 ')------------------------------------------------------------------ --------------10611--------------------------------------------------------> The actual value of the indexed column elapsed: 00:00:00.0013:32:58 [email protected]> Select Scott.f_get_from_dump (' 78,73,04,08,0f,3c,19 ', ' Date ') from Dual SCOTT. F_get_from_dump (' 78,73,04,08,0f,3c,19 ', ' DATE ')--------------------------------------------------------------------------------2015-4-8 14:59:24-------------------------- --------------------> The actual value of the indexed column Elapsed:00:00:00.0013:33:10 [email protected]> or use this function: 13:33:10 [email  protected]> Select Utl_raw.cast_to_varchar2 (' 3130363131 ') name from dual; NAME----------------------------------------------------------------------------------------------------10611

Oracle Indexed Dump leaf block