建立一張表T，及索引ITN

SQL> create table t as select * from dba_objects;

Table created.

SQL> create index itn on t(object_name);

Index created.

檢視索引結構

SQL> select object_id from dba_objects where object_name = 'ITN';

OBJECT_ID

----------

87926

alter session set events 'immediate trace name treedump level 87926';

/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_2924.trc

branch: 0x417179 4288889 (0: nrow: 2, level: 2) <<<<<<<<<< 根節點level最大，是2，葉子節點level是0

branch: 0x418f38 4296504 (-1: nrow: 309, level: 1) <<<<<<<<除了根節點之外，其它節點的編號從-1開始

leaf: 0x41717a 4288890 (-1: nrow: 187 rrow: 187)

leaf: 0x41717b 4288891 (0: nrow: 184 rrow: 184)

...

根節點4288889

SQL> select dbms_utility.data_block_address_file(4288889) file_id, dbms_utility.data_block_address_block(4288889) block_id from dual;

FILE_ID BLOCK_ID

---------- ----------

1 94585

alter system dump datafile 1 block 94585;

/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_3155.trc

...

Branch block dump

=================

header address 140576901511748=0x7fda9c473a44

kdxcolev 2 <<<<<<<<<<<<<<<<<<< level=2

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 1

kdxcofbo 30=0x1e

kdxcofeo 8039=0x1f67

kdxcoavs 8009

kdxbrlmc 4296504=0x418f38 <<<<<<<<<<<<<<<<<< 4296504是freedump中的第一個分支節點

kdxbrsno 0

kdxbrbksz 8056

kdxbr2urrc 0

row#0[8039] dba: 4296627=0x418fb3

col 0; len 11; (11): 4d 52 56 5f 4f 4c 41 50 32 5f 50 <<<<<<<<<<索引的健值

col 1; TERM

----- end of branch block dump -----

把健值轉化成字串

select chr(to_number('4d','xx'))||

chr(to_number('52','xx'))||

chr(to_number('56','xx'))||

chr(to_number('5f','xx'))||

chr(to_number('4f','xx'))||

chr(to_number('4c','xx'))||

chr(to_number('41','xx'))||

chr(to_number('50','xx'))||

chr(to_number('32','xx'))||

chr(to_number('5f','xx'))||

chr(to_number('50','xx')) from dual;

CHR(TO_NUMB

-----------

MRV_OLAP2_P

SQL> select object_name from t where object_name like 'MRV_OLAP2_P%';

OBJECT_NAME

--------------------------------------------------------------------------------

MRV$OLAP2_POP_CUBES

MRV$OLAP2_POP_DIMENSIONS

MRV_OLAP2_POP_CUBES

MRV_OLAP2_POP_DIMENSIONS

檢視第一個分支節點

SQL> select dbms_utility.data_block_address_file(4296504) file_id, dbms_utility.data_block_address_block(4296504) block_id from dual;

FILE_ID BLOCK_ID

---------- ----------

1 102200

alter system dump datafile 1 block 102200;

/u01/app/oracle/diag/rdbms/test/test/trace/test_ora_3766.trc

...

Branch block dump

=================

header address 140454403013188=0x7fbe16cc7a44

kdxcolev 1 <<<<<<<<<<<<< level=1

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 308

kdxcofbo 644=0x284

kdxcofeo 652=0x28c

kdxcoavs 8

kdxbrlmc 4288890=0x41717a <<<<<<<<<<<<第一個葉子節點

kdxbrsno 0

kdxbrbksz 8056

kdxbr2urrc 0

row#0[8020] dba: 4288891=0x41717b <<<<<<<<<<<<是從第二個葉子節點開始的，第一個葉子節點的鍵已經在根節點（上一級分支）處顯示了。

col 0; len 24; (24):

2f 31 30 65 62 35 66 65 66 5f 41 6e 79 4e 6f 64 65 43 6f 75 6e 74 65 72 <<<<<<<<<<<<<<鍵值

col 1; len 6; (6): 00 41 8c 31 00 39 <<<<<<<<<<<<<<葉子節點的dba

row#1[7978] dba: 4288892=0x41717c

col 0; len 30; (30):

2f 31 31 66 32 37 39 35 61 5f 44 54 44 47 72 61 6d 6d 61 72 43 68 69 6c 64

72 65 6e 4c 69

col 1; len 6; (6): 00 41 8b 18 00 07

row#2[7937] dba: 4288893=0x41717d

col 0; len 29; (29):

2f 31 32 65 32 65 32 38 65 5f 53 6e 6d 70 4d 69 62 54 72 65 65 54 72 65 65

4e 6f 64 65

col 1; len 6; (6): 00 41 8a 37 00 15

...

row0 col1:

select

chr(to_number('2f','xx'))||chr(to_number('31','xx'))||chr(to_number('30','xx'))||chr(to_number('65','xx'))||

chr(to_number('62','xx'))||chr(to_number('35','xx'))||chr(to_number('66','xx'))||chr(to_number('65','xx'))||

chr(to_number('66','xx'))||chr(to_number('5f','xx'))||chr(to_number('41','xx'))||chr(to_number('6e','xx'))||

chr(to_number('79','xx'))||chr(to_number('4e','xx'))||chr(to_number('6f','xx'))||chr(to_number('64','xx'))||

chr(to_number('65','xx'))||chr(to_number('43','xx'))||chr(to_number('6f','xx'))||chr(to_number('75','xx'))||

chr(to_number('6e','xx'))||chr(to_number('74','xx'))||chr(to_number('65','xx'))||chr(to_number('72','xx'))

from dual;

CHR(TO_NUMBER('2F','XX')

------------------------

/10eb5fef_AnyNodeCounter

SQL> select object_name, object_type from t where object_name like '/10eb5fef_AnyNodeCounter%';

OBJECT_NAME OBJECT_TYPE

------------------------------ -------------------

/10eb5fef_AnyNodeCounter JAVA CLASS

/10eb5fef_AnyNodeCounter SYNONYM

dump索引結構

相關文章