lych528 发表于 2015-8-26 16:27
这种方法定位了块数，但是不能定位访问了索引的什么块
不清楚你要定位访问的是什么块 是什么意思？ 定位他是哪个区里面的哪个块？还是索引的根块 枝块 叶块？
SQL> create tablespace tbs1 datafile '/u01/app/oracle/oradata/testdb/tbs1.dbf' size 100M autoextend on;
Tablespace created.
SQL> create user t identified by o default tablespace tbs1;
User created.
SQL> grant dba to t;
Grant succeeded.
SQL> conn t/o
Connected.
SQL> create table t1 as select object_id id,object_name name,object_type type from dba_objects;
Table created.
SQL> create index idx_t1 on t1(id);
Index created.
SQL> analyze table t1 compute statistics;
Table analyzed.
SQL> set linesize 1000
SQL> set pagesize 1000
SQL>col segment_name for a10
SQL>col blocks for 9999
SQL> select segment_name,segment_type,extent_id,file_id,block_id,blocks from dba_extents where segment_name='IDX_T1';
SEGMENT_NA SEGMENT_TYPEEXTENT_IDFILE_ID BLOCK_ID BLOCKS
---------- ------------------ ---------- ---------- ---------- ------
IDX_T1 INDEX
0
83938
IDX_T1 INDEX
1
84018
IDX_T1 INDEX
2
84098
IDX_T1 INDEX
3
84178
IDX_T1 INDEX
4
84258
IDX_T1 INDEX
5
84338
IDX_T1 INDEX
6
84418
IDX_T1 INDEX
7
84498
IDX_T1 INDEX
8
84578
IDX_T1 INDEX
9
84658
IDX_T1 INDEX
10
84738
IDX_T1 INDEX
11
84818
IDX_T1 INDEX
12
84898
IDX_T1 INDEX
13
84978
IDX_T1 INDEX
14
85058
IDX_T1 INDEX
15
85138
16 rows selected.
-------------------这里看出这个索引占用8号文件的393到 513+8-1=520号块总共16个区
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system checkpoint;
System altered.
彻底一点 全部刷回磁盘
select * from v$bh where file#=8 and block# between 393 and 520;
select * from x$bh where file#=8 and dbablk between 393 and 520;
这两个查询都可以第一个看status 全为free第二个看tch全为0
执行一个简单的查询
SQL> conn t/o
Connected.
SQL> select * from t1 where id=100;
ID NAME
TYPE
---------- -------------------------------------------------------------------------------------------------------------------------------- -------------------
100 I_TYPED_VIEW1
INDEX
再查询v$bh或x$bh
结果如下图
1.jpg (18.08 KB, 下载次数: 1)
下载附件
2015-8-28 15:40 上传
2.jpg (15.68 KB, 下载次数: 1)
下载附件
2015-8-28 15:40 上传
其他全部为free 或tch为0
证明 这一次select * from t1 where id=100 这个查询用到了这两个索引块
根据上面的查出来的区
SEGMENT_NA SEGMENT_TYPEEXTENT_IDFILE_ID BLOCK_ID BLOCKS
---------- ------------------ ---------- ---------- ---------- ------
IDX_T1 INDEX
0
83938
IDX_T1 INDEX
1
84018
IDX_T1 INDEX
2
84098
IDX_T1 INDEX
3
84178
393 alter system dump datafile 8 block 396;
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/testdb/udump/testdb_ora_8786.trc
SQL> alter system dump datafile 8 block 397;
System altered.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug tracefile_name
/u01/app/oracle/admin/testdb/udump/testdb_ora_8817.trc
SQL>
linux下
more /u01/app/oracle/admin/testdb/udump/testdb_ora_8786.trc 里面有下面的一部分内容
.........................
seg/obj: 0xd55dcsc: 0x00.140109itc: 1flg: Etyp: 2 - INDEX
brn: 0bdba: 0x2000189 ver: 0x01 opc: 0
inc: 0exflg: 0
Itl Xid
Uba FlagLckScn/Fsc
0x01 0xffff.000.000000000x00000000.0000.00C---0scn 0x0000.00140109
Branch block dump
=================
header address 150135372=0x8f2e24c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 112
kdxcofbo 252=0xfc
kdxcofeo 6956=0x1b2c
kdxcoavs 6704
kdxbrlmc 33554829=0x200018d
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
............................
more /u01/app/oracle/admin/testdb/udump/testdb_ora_8817.trc里面有这些内容
..........................................
Object id on Block? Y
seg/obj: 0xd55dcsc: 0x00.140109itc: 2flg: Etyp: 2 - INDEX
brn: 0bdba: 0x2000189 ver: 0x01 opc: 0
inc: 0exflg: 0
Itl Xid
Uba FlagLckScn/Fsc
0x01 0x0000.000.000000000x00000000.0000.00----0fsc 0x0000.00000000
0x02 0xffff.000.000000000x00000000.0000.00C---0scn 0x0000.00140109
Leaf block dump
===============
header address 176689764=0xa881264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 485
kdxcofbo 1006=0x3ee
kdxcofeo 1830=0x726
kdxcoavs 824
kdxlespl 0
kdxlende 0
kdxlenxt 33554830=0x200018e
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
..............................................
看看 这样是不是你要的定位到块 |
