dump index 的层次结构

最新推荐文章于 2021-10-09 11:47:58 发布

cucanqi9387

最新推荐文章于 2021-10-09 11:47:58 发布

阅读量130

点赞数

SQL>create table test (a number);

SQL> begin
2 for i in 1..2000 loop
3 insert into test values(i);
4 end loop;
5 commit;
6 end;
7 /

PL/SQL procedure successfully completed.

SQL> create unique index ind_test on test(a);

Index created.

SQL> select data_object_id,object_id from dba_objects where object_name='IND_TES
T';

DATA_OBJECT_ID OBJECT_ID
-------------- ----------
6730 6730

dump unique index的层级结构

SQL> alter session set events 'immediate trace name treedump level 6730';

Session altered.

里面内容如下:

*** 2008-01-11 08:54:32.902
----- begin tree dump
branch: 0x100004c 16777292 (0: nrow: 4, level: 1)
   leaf: 0x100004d 16777293 (-1: nrow: 520 rrow: 520)
   leaf: 0x100004e 16777294 (0: nrow: 513 rrow: 513)
   leaf: 0x100004f 16777295 (1: nrow: 513 rrow: 513)
   leaf: 0x1000050 16777296 (2: nrow: 454 rrow: 454)
----- end tree dump

然后根据branch 得到文件号和块号

SQL> select dbms_utility.data_block_address_file(16777292) "file",dbms_utility.d
ata_block_address_block(16777292) "block" from dual;

file block
---------- ----------
4 76

dump branch block的实际内容

SQL> alter system dump datafile 4 block 76;

System altered.

header address 85200972=0x514104c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 3
kdxcofbo 34=0x22
kdxcofeo 8036=0x1f64
kdxcoavs 8002
kdxbrlmc 16777293=0x100004d
kdxbrsno 0
kdxbrbksz 8060
row#0[8052] dba: 16777294=0x100004e
col 0; len 3; (3): c2 06 16
row#1[8044] dba: 16777295=0x100004f
col 0; len 3; (3): c2 0b 23
row#2[8036] dba: 16777296=0x1000050
col 0; len 3; (3): c2 10 30

根据branch 16777294 得到更详细内容

SQL> select dbms_utility.data_block_address_file(16777294) "file",dbms_utility.d
ata_block_address_block(16777294) "block" from dual;

file block
---------- ----------
4 78

Start dump data blocks tsn: 4 file#: 4 minblk 78 maxblk 78
buffer tsn: 4 rdba: 0x0100004e (4/78)
scn: 0x0000.00175269 seq: 0x02 flg: 0x04 tail: 0x52690602
frmt: 0x02 chkval: 0x9009 type: 0x06=trans data
Block header dump: 0x0100004e
Object id on Block? Y
seg/obj: 0x1a4a csc: 0x00.175268 itc: 2 flg: E typ: 2 - INDEX
     brn: 0 bdba: 0x1000049 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 0x0000.00175268

Leaf block dump
===============
header address 85200996=0x5141064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0x426
kdxcofeo 1885=0x75d
kdxcoavs 823
kdxlespl 0
kdxlende 0
kdxlenxt 16777295=0x100004f
kdxleprv 16777293=0x100004d
kdxledsz 6
kdxlebksz 8036
row#0[8024] flag: -----, lock: 0, data:(6): 01 00 00 0c 02 08
col 0; len 3; (3): c2 06 16
row#1[8012] flag: -----, lock: 0, data:(6): 01 00 00 0c 02 09
col 0; len 3; (3): c2 06 17
row#2[8000] flag: -----, lock: 0, data:(6): 01 00 00 0c 02 0a
col 0; len 3; (3): c2 06 18
row#3[7988] flag: -----, lock: 0, data:(6): 01 00 00 0c 02 0b
col 0; len 3; (3): c2 06 19
row#4[7976] flag: -----, lock: 0, data:(6): 01 00 00 0c 02 0c
col 0; len 3; (3): c2 06 1a
row#5[7964] flag: -----, lock: 0, data:(6): 01 00 00 0c 02 0d
col 0; len 3; (3): c2 06 1b
row#6[7952] flag: -----, lock: 0, data:(6): 01 00 00 0c 02 0e
col 0; len 3; (3): c2 06 1c