對 Oracle 索引leaf block 進行dump
F:\oracle\product\10.2.0\db_1\BIN>sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 9 06:28:17 2015 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 06:39:43 sys@FS> alter session set events 'immediate trace name treedump level 95503'; ---->該95503是該索引的object_id Session altered. Elapsed: 00:00:01.38 06:39:52 sys@FS>
---->在udump下獲得trc文件
Dump file f:\oracle\product\10.2.0\admin\fs\udump\fs_ora_4544.trc Thu 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 Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Windows NT Version V6.1 CPU : 4 - type 8664, 2 Physical Cores Process Affinity : 0x0000000000000000 Memory (Avail/Total): Ph:4189M/7950M, Ph+PgF:11446M/16140M Instance name: fs Redo thread mounted by this instance: 1 Oracle process number: 24 Windows 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 06: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 dump branch: 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)--->以此行為例子：0x100150f這是16進制的DBA；16782607 這是十進制的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.00
06: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.00
06:57:21 sys@FS> print file#
FILE#
----------
4
06:57:27 sys@FS> print block#
BLOCK#
----------
5391
退出sqlplus，並再次進入sqlplus（為的是能生成在一個新的trc中）
F:\oracle\product\10.2.0\db_1\BIN>sqlplus / as sysdba SQL*Plus: Release 10.2.0.4.0 - Production on Thu Apr 9 06:59:56 2015 Copyright (c) 1982, 2007, Oracle. All Rights Reserved. Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 06:59:57 sys@FS> alter system dump datafile 4 block 5391; System altered. Elapsed: 00:00:00.14 07:00:00 sys@FS>
在udump下獲得trc文件：該文件有1600多行，因此不全部列出。以“Leaf block dump”為關鍵字搜索該文件：
Leaf block dump =============== header address 128074340=0x7a24264 kdxcolev 0 KDXCOLEV Flags = - - - kdxcolok 0 kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y kdxconco 3 kdxcosdc 0 kdxconro 288 kdxcofbo 612=0x264 kdxcofeo 1442=0x5a2 kdxcoavs 830 kdxlespl 0 kdxlende 0 kdxlenxt 16782608=0x1001510 kdxleprv 16782606=0x100150e kdxledsz 0 kdxlebksz 8036 row#0[8013] flag: ------, lock: 0, len=23 col 0; len 5; (5): 31 30 36 31 31 -------------->這是索引列的值 col 1; len 7; (7): 78 73 04 08 0f 3c 19--------->這是索引列的值 col 2; len 6; (6): 01 00 14 26 01 66------------>這是16進制的rowid row#1[7990] flag: ------, lock: 0, len=23 col 0; len 5; (5): 31 30 36 31 32 col 1; len 7; (7): 78 73 04 08 0f 3c 19 col 2; len 6; (6): 01 00 14 26 01 67 row#2[7967] flag: ------, lock: 0, len=23 col 0; len 5; (5): 31 30 36 31 33 col 1; len 7; (7): 78 73 04 08 0f 3c 19 col 2; len 6; (6): 01 00 14 26 01 68 row#3[7944] flag: ------, lock: 0, len=23 col 0; len 5; (5): 31 30 36 31 34 col 1; len 7; (7): 78 73 04 08 0f 3c 19 col 2; len 6; (6): 01 00 14 26 01 69 ..................................... row#287[1442] flag: ------, lock: 0, len=23 col 0; len 5; (5): 31 30 38 37 30 col 1; len 7; (7): 78 73 04 08 0f 3c 19 col 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
下面來分析16進制的rowid：
01 00 14 26 01 66 --->這是16進制
00000001 00000000 00010100 00100110 00000001 01100110 --->這是換算成2進制後的（對01用calc.exe換算，對00用calc.exe換算，對14用calc.exe換算，等等）
00000001 00 ---->文件號 ：4 ------------------------------------->4是換算成10進制後的值（使用calc.exe換算）
000000 00010100 00100110 ----->塊號 ：5158 ------------------------->5158是換算成10進制後的值（使用calc.exe換算）
00000001 01100110 ---->行號：358 ------------->358是是換算成10進制後的值（使用calc.exe換算）
前十位表示文件號
中間22位表示塊號
最後16位表示行號
下面來分析2個索引值：
col 0; len 5; (5): 31 30 36 31 31 -------------->這是索引列的值
col 1; len 7; (7): 78 73 04 08 0f 3c 19--------->這是索引列的值
使用函數f_get_from_dump將上述值轉換 （該函數來自travel大師：http://www.traveldba.com/wp-content/uploads/scripts/f_get_from_dump.sql）
F:\oracle\product\10.2.0\db_1\BIN>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 10 13:32:40 2015
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
13: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')
--------------------------------------------------------------------------------
10611 -------------------------------------------------------->索引列的實際值
Elapsed: 00:00:00.00
13:32:58 sys@FS> select scott.f_get_from_dump('78,73,04,08,0f,3c,19','date') fro
m dual;
SCOTT.F_GET_FROM_DUMP('78,73,04,08,0F,3C,19','DATE')
--------------------------------------------------------------------------------
2015-4-8 14:59:24---------------------------------------------->索引列的實際值
Elapsed: 00:00:00.00
13:33:10 sys@FS>
或者用這個函數：
13:33:10 sys@FS> select utl_raw.cast_to_varchar2('3130363131') name from dual;
NAME
--------------------------------------------------------------------------------
--------------------
10611
