(1)环境
create table test (a number);
insert into test values(1);
insert into test values(2);
insert into test values(3);
insert into test values(4);
commit;
(2)查询block
查询表所对应的block:
select dbms_rowid.rowid_relative_fno(rowid) rel_fno,
max(dbms_rowid.rowid_block_number(rowid)) max_block,
min(dbms_rowid.rowid_block_number(rowid)) min_block
from test
group by dbms_rowid.rowid_relative_fno(rowid);
REL_FNO MAX_BLOCK MIN_BLOCK
---------- ---------- ----------
21 1437734 1437734
(3)10046
Level 1: 等同于SQL_TRACE 的功能
Level 4: 在Level 1的基础上增加收集绑定变量的信息
Level 8: 在Level 1 的基础上增加等待事件的信息
Level 12:等同于Level 4+Level 8, 即同时收集绑定变量信息和等待事件信息。
alter session set tracefile_identifier='test';
alter session set events '10046 trace name context forever, level 12';
另外oradebug生成10046:
oradebug setmypid
oradebug event 10046 trace name context forever,level 8;
oradebug event 10046trace name context off;
oradebugtracefile_name
/u01/app/oracle/admin/anqing/udump/zxin_ora_47538_test.trc
select count(1) from test;
exit
cd $ORACLE_BASE/admin/zxin/udump/
ll |grep test
-rw-r----- 1 oracle dba 3805 05-30 15:54 zxin_ora_47538_test.trc
tkprof zxin_ora_47538_test.trc zxin_ora_47538_test.tkp
(4)block dump
SQL> oradebug setmypid
SQL> alter system dump datafile 21 block 1437734;
SQL> oradebug tracefile_name
/home/oracle/zxindbf/admin/zxin/udump/zxin_ora_10937.trc
SQL>!vi /home/oracle/zxindbf/admin/zxin/udump/zxin_ora_10937.trc
*** SESSION ID:(847.1921) 2013-05-30 16:15:05.650
Start dump data blocks tsn: 5 file#: 21 minblk 1437734 maxblk 1437734 --block的基本信息
。。。。。。
Block header dump: 0x0555f026
Object id on Block? Y
seg/obj: 0x4ec8 csc: 0x00.62a13b itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x555f009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x000a.026.00001921 0x00803c4b.04ae.09 --U- 1 fsc 0x0000.0062a13c
0x02 0x0003.027.00000068 0x008020d7.0058.13 --U- 3 fsc 0x0000.0062a144
data_block_dump,data header at 0x6bd1064
===============
tsiz: 0x1f98
hsiz: 0x1a
pbl: 0x06bd1064
bdba: 0x0555f026
76543210
flag=--------
ntab=1
nrow=4
frre=-1
fsbo=0x1a
fseo=0x1f80
avsp=0x1f5a
tosp=0x1f5a
0xe:pti[0] nrow=4 offs=0
0x12:pri[0] offs=0x1f92
0x14:pri[1] offs=0x1f8c
0x16:pri[2] offs=0x1f86
0x18:pri[3] offs=0x1f80
block_row_dump:
tab 0, row 0, @0x1f92
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 02 --只有一列 c102为列值
tab 0, row 1, @0x1f8c --
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 03
tab 0, row 2, @0x1f86
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 04
tab 0, row 3, @0x1f80
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 05
end_of_block_dump
End dump data blocks tsn: 5 file#: 21 minblk 1437734 maxblk 1437734
SQL> declare n number;
2 begin
3 dbms_stats.convert_raw_value('c102',n);
4 dbms_output.put_line(n);
5 end;
6 /
1 ---值