更详细的treedump说明参考Oracle DSI
具体实验如下：
1 建立测试表
create table test_idx as select * from all_objects;
2 建立索引
create index test_pk on test_idx(object_id);
3 treedump出索引层次结构
首先查出data_object_id
SQL> select data_object_id,object_id,subobject_name from dba_objects where wner='GZDC' and object_name='TEST_PK';
DATA_OBJECT_ID OBJECT_ID SUBOBJECT_NAME
-------------- ---------- ------------------------------
33631 33631
alter session set events 'immediate trace name treedump level 33632';
这种方式dump出的是哪个块呢?
这种方式的dump文件不是具体的哪个block,是一个总体的信息.
在udump目录下，找出trace文件，查看文件内容
oracle@Z813:/opt/oracle/admin/mydb/udump> cat mydb_ora_29882.trc
/opt/oracle/admin/mydb/udump/mydb_ora_29882.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /opt/oracle/product/9ir2
System name: Linux
Node name: Z813
Release: 2.6.5-7.244-smp
Version: #1 SMP Mon Dec 12 18:32:25 UTC 2005
Machine: x86_64
Instance name: mydb
Redo thread mounted by this instance: 1
Oracle process number: 17
Unix process pid: 29882, image: oracle@Z813 (TNS V1-V3)
*** 2010-07-05 14:18:32.914
*** SESSION ID:(16.6451) 2010-07-05 14:18:32.913
kdxdtr: specified index object 33631 does not exist
*** 2010-07-05 14:21:48.437
----- begin tree dump
branch: 0x2422a9c 37890716 (0: nrow: 68, level: 1)
leaf: 0x2422a9d 37890717 (-1: nrow: 485 rrow: 485)
leaf: 0x2422a9e 37890718 (0: nrow: 479 rrow: 479)
leaf: 0x2422a9f 37890719 (1: nrow: 479 rrow: 479)
leaf: 0x2422aa0 37890720 (2: nrow: 478 rrow: 478)
leaf: 0x2422aa1 37890721 (3: nrow: 479 rrow: 479)
leaf: 0x2422aa2 37890722 (4: nrow: 479 rrow: 479)
leaf: 0x2422aa3 37890723 (5: nrow: 478 rrow: 478)
leaf: 0x2422aa4 37890724 (6: nrow: 478 rrow: 478)
leaf: 0x2422aa5 37890725 (7: nrow: 479 rrow: 479)
leaf: 0x2422aa6 37890726 (8: nrow: 479 rrow: 479)
leaf: 0x2422aa7 37890727 (9: nrow: 478 rrow: 478)
leaf: 0x2422aa8 37890728 (10: nrow: 479 rrow: 479)
leaf: 0x2422aaa 37890730 (11: nrow: 479 rrow: 479)
leaf: 0x2422aab 37890731 (12: nrow: 478 rrow: 478)
leaf: 0x2422aac 37890732 (13: nrow: 479 rrow: 479)
leaf: 0x2422aad 37890733 (14: nrow: 479 rrow: 479)
leaf: 0x2422aae 37890734 (15: nrow: 479 rrow: 479)
leaf: 0x2422aaf 37890735 (16: nrow: 478 rrow: 478)
leaf: 0x2422ab0 37890736 (17: nrow: 479 rrow: 479)
leaf: 0x2422ab1 37890737 (18: nrow: 463 rrow: 463)
leaf: 0x2422ab2 37890738 (19: nrow: 449 rrow: 449)
leaf: 0x2422ab3 37890739 (20: nrow: 449 rrow: 449)
leaf: 0x2422ab4 37890740 (21: nrow: 449 rrow: 449)
leaf: 0x2422ab5 37890741 (22: nrow: 449 rrow: 449)
leaf: 0x2422ab6 37890742 (23: nrow: 449 rrow: 449)
leaf: 0x2422ab7 37890743 (24: nrow: 449 rrow: 449)
leaf: 0x2422ab8 37890744 (25: nrow: 449 rrow: 449)
leaf: 0x2422aba 37890746 (26: nrow: 449 rrow: 449)
leaf: 0x2422abb 37890747 (27: nrow: 449 rrow: 449)
leaf: 0x2422abc 37890748 (28: nrow: 449 rrow: 449)
leaf: 0x2422abd 37890749 (29: nrow: 449 rrow: 449)
leaf: 0x2422abe 37890750 (30: nrow: 449 rrow: 449)
leaf: 0x2422abf 37890751 (31: nrow: 449 rrow: 449)
leaf: 0x2422ac0 37890752 (32: nrow: 449 rrow: 449)
leaf: 0x2422ac1 37890753 (33: nrow: 449 rrow: 449)
leaf: 0x2422ac2 37890754 (34: nrow: 449 rrow: 449)
leaf: 0x2422ac3 37890755 (35: nrow: 449 rrow: 449)
leaf: 0x2422ac4 37890756 (36: nrow: 449 rrow: 449)
leaf: 0x2422ac5 37890757 (37: nrow: 449 rrow: 449)
leaf: 0x2422ac6 37890758 (38: nrow: 449 rrow: 449)
leaf: 0x2422ac7 37890759 (39: nrow: 449 rrow: 449)
leaf: 0x2422ac8 37890760 (40: nrow: 449 rrow: 449)
leaf: 0x2422aca 37890762 (41: nrow: 449 rrow: 449)
leaf: 0x2422acb 37890763 (42: nrow: 449 rrow: 449)
leaf: 0x2422acc 37890764 (43: nrow: 449 rrow: 449)
leaf: 0x2422acd 37890765 (44: nrow: 449 rrow: 449)
leaf: 0x2422ace 37890766 (45: nrow: 449 rrow: 449)
leaf: 0x2422acf 37890767 (46: nrow: 449 rrow: 449)
leaf: 0x2422ad0 37890768 (47: nrow: 449 rrow: 449)
leaf: 0x2422ad1 37890769 (48: nrow: 449 rrow: 449)
leaf: 0x2422ad2 37890770 (49: nrow: 449 rrow: 449)
leaf: 0x2422ad3 37890771 (50: nrow: 449 rrow: 449)
leaf: 0x2422ad4 37890772 (51: nrow: 448 rrow: 448)
leaf: 0x2422ad5 37890773 (52: nrow: 449 rrow: 449)
leaf: 0x2422ad6 37890774 (53: nrow: 449 rrow: 449)
leaf: 0x2422ad7 37890775 (54: nrow: 449 rrow: 449)
leaf: 0x2422ad8 37890776 (55: nrow: 449 rrow: 449)
leaf: 0x2422ada 37890778 (56: nrow: 449 rrow: 449)
leaf: 0x2422adb 37890779 (57: nrow: 449 rrow: 449)
leaf: 0x2422adc 37890780 (58: nrow: 449 rrow: 449)
leaf: 0x2422add 37890781 (59: nrow: 449 rrow: 449)
leaf: 0x2422ade 37890782 (60: nrow: 449 rrow: 449)
leaf: 0x2422adf 37890783 (61: nrow: 449 rrow: 449)
leaf: 0x2422ae0 37890784 (62: nrow: 449 rrow: 449)
leaf: 0x2422ae1 37890785 (63: nrow: 449 rrow: 449)
leaf: 0x2422ae2 37890786 (64: nrow: 449 rrow: 449)
leaf: 0x2422ae3 37890787 (65: nrow: 449 rrow: 449)
leaf: 0x2422ae4 37890788 (66: nrow: 252 rrow: 252)
----- end tree dump
####################################################################
结构说明:
leaf: 表示该数据块是leaf block
0x2422a9d: 对应索引数据块的十六进位地址
37890717：对应索引数据块的十进位地址
-1: 表示索引数据块的编号，编号起始是-1
nrow: 485 ：表示该索引数据块中总的行数，包含被删除的行
rrow: 485：表示该索引数据块中实际存在有效行数
####################################################################
表示共有69个索引块
branch:表示为根节点.1个
leaf:表示为叶子节点68个.
通过视图查询索引的详细.有些列不是很清晰?
SQL> Analyze index TEST_PK validate structure;
Index analyzed
SQL> SELECT NAME, BLOCKS, HEIGHT,LF_ROWS, LF_BLKS, BR_ROWS, BR_BLKS, BTREE_SPACE, USED_SPACE FROM INDEX_STATS WHERE NAME='TEST_PK';
NAME BLOCKS HEIGHT LF_ROWS LF_BLKS BR_ROWS BR_BLKS BTREE_SPACE USED_SPACE
------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ----------- ----------
TEST_PK 80 2 30918 68 67 1 551756 485739
4、根据branch的dba值得到文件号和块号。如果索引很小，
那么就可能没有branch，直接从leaf取。如果仅仅看某个leaf的内容，也可以在这里直接从leaf的dba得到文件号和块号。
SQL> select dbms_utility.data_block_address_file(37890716),dbms_utility.data_block_address_block(37890716) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
9 141980
1419980为根节点块.这与上面的是相吻合的.
SQL> select dbms_utility.data_block_address_file(37890718),dbms_utility.data_block_address_block(37890718) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
9 141981
叶子节点块
SQL> select dbms_utility.data_block_address_file(37890718),dbms_utility.data_block_address_block(37890718) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRES DBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------ ------------------------------
9 141982
叶子节点块
5、dump数据块
SQL> alter system dump datafile 9 block 141980;
System altered
SQL> alter system dump datafile 9 block 141981;
System altered
SQL> alter system dump datafile 9 block 141982;
System altered
详细参考<
5 分析数据块的dump信息
opt/oracle/admin/mydb/udump/mydb_ora_26575.trc
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production
ORACLE_HOME = /opt/oracle/product/9ir2
System name: Linux
Node name: Z813
Release: 2.6.5-7.244-smp
Version: #1 SMP Mon Dec 12 18:32:25 UTC 2005
Machine: x86_64
Instance name: mydb
Redo thread mounted by this instance: 1
Oracle process number: 18
Unix process pid: 26575, image: oracle@Z813 (TNS V1-V3)
*** 2010-07-05 14:36:48.961
*** SESSION ID:(19.167) 2010-07-05 14:36:48.959
Start dump data blocks tsn: 9 file#: 9 minblk 141980 maxblk 141980
buffer tsn: 9 rdba: 0x02422a9c (9/141980)
scn: 0x0273.bbd81a0c seq: 0x01 flg: 0x04 tail: 0x1a0c0601
frmt: 0x02 chkval: 0x70bb type: 0x06=trans data
Block header dump: 0x02422a9c
Object id on Block? Y
seg/obj: 0x8360 csc: 0x273.bbd819f7 itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2422a99 ver: 0x01
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0273.bbd819f7
Branch block dump (开始根节点块信息)
=================
header address 66832460=0x3fbc84c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 67
kdxcofbo 162=0xa2
kdxcofeo 7405=0x1ced
kdxcoavs 7243
kdxbrlmc 37890717=0x2422a9d
kdxbrsno 0
kdxbrbksz 8056
row#0[8047] dba: 37890718=0x2422a9e(rowid)
col 0; len 3; (3): c2 06 29
col 1; TERM
row#1[8038] dba: 37890719=0x2422a9f
col 0; len 3; (3): c2 0b 1c
.................
row#65[7415] dba: 37890787=0x2422ae3
col 0; len 4; (4): c3 04 1b 13
col 1; TERM
row#66[7405] dba: 37890788=0x2422ae4
col 0; len 4; (4): c3 04 20 28
col 1; TERM
----- end of branch block dump -----
End dump data blocks tsn: 9 file#: 9 minblk 141980 maxblk 141980 (结束根节点块信息)
Start dump data blocks tsn: 9 file#: 9 minblk 141981 maxblk 141981(开始第一个叶子节点块信息)
buffer tsn: 9 rdba: 0x02422a9d (9/141981)
scn: 0x0273.bbd819fa seq: 0x01 flg: 0x04 tail: 0x19fa0601
frmt: 0x02 chkval: 0xba19 type: 0x06=trans data
Block header dump: 0x02422a9d
Object id on Block? Y
seg/obj: 0x8360 csc: 0x273.bbd819f7 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2422a99 ver: 0x01
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0273.bbd819f7
Leaf block dump
===============
header address 66832484=0x3fbc864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 485
kdxcofbo 1006=0x3ee
kdxcofeo 1825=0x721
kdxcoavs 819
kdxlespl 0
kdxlende 0
kdxlenxt 37890718=0x2422a9e
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
###########################################################################
结构说明:
header address 55128156=0x349305c
kdxcolev 0 (index level, 0表示是leaf block)
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y 内部操作代码
kdxconco 2 索引列数量
kdxcosdc 0 索引结构改变次数
kdxconro 67 索引记录数量BR_ROWS
kdxcofbo 162=0xa2 空闲空间开始偏移量
kdxcofeo 7405=0x1ced 空闲空间结束偏移量
kdxcoavs 7243 所提供的空闲空间
kdxlespl 0 在数据块被cleaned out时，还未进行commit的数量
kdxlende 122 被删除索引记录的数量
kdxlenxt 67120396=0x4002d0c下一个链接数据块的地址
kdxleprv 0=0x0上一个链接数据块地址
kdxledsz 0 被删除的空间大小
kdxlebksz 8036 使用的数据块空间
kdxcolev: index level (0 represents leaf blocks)
kdxcolok: denotes whether structural block transaction is occurring
kdxcoopc: internal operation code
kdxconco: index column count
kdxcosdc: count of index structural changes involving block
kdxconro: number of index entries (does not include kdxbrlmc pointer)
kdxcofbo: offset to beginning of free space within block
kdxcofeo: offset to the end of free space (ie. first portion of block containing index data)
kdxcoavs: available space in block (effectively area between the two fields above)
###########################################################################
row#0[8020] flag: -----, lock: 0
col 0; len 2; (2): c1 04
col 1; len 6; (6): 02 42 2a 94 00 3d
row#1[8008] flag: -----, lock: 0
col 0; len 2; (2): c1 05
col 1; len 6; (6): 02 42 2b 19 00 2d
............
row#483[1838] flag: -----, lock: 0
col 0; len 3; (3): c2 06 27
col 1; len 6; (6): 02 42 2a 95 00 28
row#484[1825] flag: -----, lock: 0
col 0; len 3; (3): c2 06 28
col 1; len 6; (6): 02 42 2a 95 00 29
----- end of leaf block dump -----
###########################################################################
结构说明:
row#0[8047] dba: 37890718=0x2422a9e(rowid)
col 0; len 3; (3): c2 06 29
col 1; TERM
row#0[8020] flag: -----, lock: 0
col 0; len 2; (2): c1 04
col 1; len 6; (6): 02 42 2a 94 00 3d
col 0: 为keyvalue.
col 1: 为Rowid
关于rowid的换算：索引中的rowid由48个bit构成，前10个bit构成文件号，中间22个bit构成块号，最后16个bit 构成块中的行号。
(根据rowid如何访问数据?)
关于整数key值的换算
先去掉c2，因为c2只表示最高位的位置，后面才是真正数据，06转换为十进制为6，减去1为5，3b转换为十进制为48＋11=59，
减去1为 58，两个结果合并到一起，为258，这就是其存储的真正数据，简单表示一下：
1. c2 03 3b －> 03 3b
2. 03 －> 0*16+3=3－>3-1=2
3. 3b －> 3*16+14=59 －> 59-1=58
4. 结果合并得到258
关于字符串的换算：直接由十六进制得到各个ascii码，然后对应到相应字符。
这个换算没有得到验证???????
这种dump信息要参考DSI
################################################################################################################
End dump data blocks tsn: 9 file#: 9 minblk 141981 maxblk 141981(结束第一个叶子节点块信息)
Start dump data blocks tsn: 9 file#: 9 minblk 141982 maxblk 141982(开始第二个叶子节点块信息)
buffer tsn: 9 rdba: 0x02422a9e (9/141982)
scn: 0x0273.bbd819fa seq: 0x01 flg: 0x04 tail: 0x19fa0601
frmt: 0x02 chkval: 0x1745 type: 0x06=trans data
Block header dump: 0x02422a9e
Object id on Block? Y
seg/obj: 0x8360 csc: 0x273.bbd819f7 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2422a99 ver: 0x01
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0273.bbd819f7
Leaf block dump
===============
header address 66832484=0x3fbc864
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 479
kdxcofbo 994=0x3e2
kdxcofeo 1810=0x712
kdxcoavs 816
kdxlespl 0
kdxlende 0
kdxlenxt 37890719=0x2422a9f -- 下个节点RBA地址
kdxleprv 37890717=0x2422a9d -- 上一个节点RBA地址.
kdxledsz 0
kdxlebksz 8032
row#0[8019] flag: -----, lock: 0
col 0; len 3; (3): c2 06 29
col 1; len 6; (6): 02 42 2a 95 00 2a
row#1[8006] flag: -----, lock: 0
col 0; len 3; (3): c2 06 2a
col 1; len 6; (6): 02 42 2b 17 00 13
row#2[7993] flag: -----, lock: 0
col 0; len 3; (3): c2 06 2b
col 1; len 6; (6): 02 42 2b 17 00 14
...............
row#477[1823] flag: -----, lock: 0
col 0; len 3; (3): c2 0b 1a
col 1; len 6; (6): 02 42 2b cf 00 13
row#478[1810] flag: -----, lock: 0
col 0; len 3; (3): c2 0b 1b
col 1; len 6; (6): 02 42 2b 20 00 05
----- end of leaf block dump -----
End dump data blocks tsn: 9 file#: 9 minblk 141982 maxblk 141982(结束第二个叶子节点块信息)