dump index 的層次結構

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