oracle dump 索引

create table testindex as select spid from v$process;


select count(*) from testindex;


create index spidindex on testindex(spid);






 select object_id, object_name from user_objects  where 
 OBJECT_NAME='SPIDINDEX' and
  OBJECT_TYPE='INDEX';   --- object_id:234083
  
  alter system set events 'immediate trace name treedump level 234083';
  
  
  
--WIN下的


SELECT    d.VALUE
       || '\'
       || LOWER (RTRIM (i.INSTANCE, CHR (0)))
       || '_ora_'
       || p.spid
       || '.trc' trace_file_name
  FROM (SELECT p.spid
          FROM v$mystat m, v$session s, v$process p
         WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,
       (SELECT t.INSTANCE
          FROM v$thread t, v$parameter v
         WHERE v.NAME = 'thread'
           AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,
       (SELECT VALUE
          FROM v$parameter
         WHERE NAME = 'user_dump_dest') d   ---c:\app\xxx\diag\rdbms\orcl\orcl\trace\orcl_ora_7024.trc
         
         
         ----- begin tree dump
--leaf: 0x1c1800b 29458443 (0: nrow: 29 rrow: 29)
----- end tree dump


    select dbms_utility.data_block_address_file(29458443),
dbms_utility.data_block_address_block(29458443) 
 from dual;  -- 7, 98315
 
 
alter system dump datafile 7 block 98315; --- dump index 


查看 c:\app\xxx\diag\rdbms\orcl\orcl\trace\orcl_ora_7024.trc 文件

 
 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.00f36aaa
Leaf block dump
===============
header address 266502756=0xfe28264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 29
kdxcofbo 94=0x5e
kdxcofeo 7632=0x1dd0
kdxcoavs 7538
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8022] flag: ------, lock: 0, len=14
col 0; len 4; (4):  32 31 35 32
col 1; len 6; (6):  01 c1 80 03 00 16
row#1[8008] flag: ------, lock: 0, len=14
col 0; len 4; (4):  32 32 38 30
col 1; len 6; (6):  01 c1 80 03 00 11
row#2[7995] flag: ------, lock: 0, len=13
col 0; len 3; (3):  32 39 32
col 1; len 6; (6):  01 c1 80 03 00 18
row#3[7981] flag: ------, lock: 0, len=14
col 0; len 4; (4):  33 36 31 32
col 1; len 6; (6):  01 c1 80 03 00 0f
row#4[7967] flag: ------, lock: 0, len=14
col 0; len 4; (4):  34 33 35 32
col 1; len 6; (6):  01 c1 80 03 00 0e
row#5[7953] flag: ------, lock: 0, len=14
col 0; len 4; (4):  34 33 35 36
col 1; len 6; (6):  01 c1 80 03 00 0d
row#6[7939] flag: ------, lock: 0, len=14
col 0; len 4; (4):  34 33 36 30
col 1; len 6; (6):  01 c1 80 03 00 0c
row#7[7925] flag: ------, lock: 0, len=14
col 0; len 4; (4):  34 33 36 34
col 1; len 6; (6):  01 c1 80 03 00 0b
row#8[7911] flag: ------, lock: 0, len=14
col 0; len 4; (4):  34 33 36 38
col 1; len 6; (6):  01 c1 80 03 00 09
row#9[7897] flag: ------, lock: 0, len=14
col 0; len 4; (4):  34 33 37 32
col 1; len 6; (6):  01 c1 80 03 00 0a
row#10[7883] flag: ------, lock: 0, len=14
col 0; len 4; (4):  34 35 31 36
col 1; len 6; (6):  01 c1 80 03 00 08
row#11[7869] flag: ------, lock: 0, len=14
col 0; len 4; (4):  35 34 32 34
col 1; len 6; (6):  01 c1 80 03 00 19
row#12[7855] flag: ------, lock: 0, len=14
col 0; len 4; (4):  35 34 35 36
col 1; len 6; (6):  01 c1 80 03 00 07
row#13[7841] flag: ------, lock: 0, len=14
col 0; len 4; (4):  35 34 36 30
col 1; len 6; (6):  01 c1 80 03 00 06
row#14[7827] flag: ------, lock: 0, len=14
col 0; len 4; (4):  35 34 36 34
col 1; len 6; (6):  01 c1 80 03 00 05
row#15[7813] flag: ------, lock: 0, len=14
col 0; len 4; (4):  35 34 36 38
col 1; len 6; (6):  01 c1 80 03 00 04
row#16[7799] flag: ------, lock: 0, len=14
col 0; len 4; (4):  35 34 37 32
col 1; len 6; (6):  01 c1 80 03 00 03
row#17[7785] flag: ------, lock: 0, len=14
col 0; len 4; (4):  35 34 37 36
col 1; len 6; (6):  01 c1 80 03 00 02
row#18[7771] flag: ------, lock: 0, len=14
col 0; len 4; (4):  35 34 38 34
col 1; len 6; (6):  01 c1 80 03 00 01
row#19[7757] flag: ------, lock: 0, len=14
col 0; len 4; (4):  36 31 36 38
col 1; len 6; (6):  01 c1 80 03 00 12
row#20[7743] flag: ------, lock: 0, len=14
col 0; len 4; (4):  36 32 35 32
col 1; len 6; (6):  01 c1 80 03 00 1a
row#21[7729] flag: ------, lock: 0, len=14
col 0; len 4; (4):  36 33 32 34
col 1; len 6; (6):  01 c1 80 03 00 1c
row#22[7715] flag: ------, lock: 0, len=14
col 0; len 4; (4):  37 30 32 34
col 1; len 6; (6):  01 c1 80 03 00 1d
row#23[7701] flag: ------, lock: 0, len=14
col 0; len 4; (4):  37 35 39 32
col 1; len 6; (6):  01 c1 80 03 00 17
row#24[7688] flag: ------, lock: 0, len=13
col 0; len 3; (3):  38 38 34
col 1; len 6; (6):  01 c1 80 03 00 10
row#25[7674] flag: ------, lock: 0, len=14
col 0; len 4; (4):  39 30 39 32
col 1; len 6; (6):  01 c1 80 03 00 1b
row#26[7660] flag: ------, lock: 0, len=14
col 0; len 4; (4):  39 31 33 36
col 1; len 6; (6):  01 c1 80 03 00 15
row#27[7646] flag: ------, lock: 0, len=14
col 0; len 4; (4):  39 36 31 36
col 1; len 6; (6):  01 c1 80 03 00 13
row#28[7632] flag: ------, lock: 0, len=14
col 0; len 4; (4):  39 37 39 32
col 1; len 6; (6):  01 c1 80 03 00 14
----- end of leaf block dump -----

End dump data blocks tsn: 9 file#: 7 minblk 98315 maxblk 98315

参考  http://123304258.blog.163.com/blog/static/1235470201308716920/

http://blog.chinaunix.net/uid-20785090-id-1844435.html 堂主第三条sql 写错了 应该从 user_objects里查