主要看下块清除
undo blk 未commit前 已经写回 datafile, 此时commit,延迟块清除产生datablk 不会被从datafile读入buffer cache,undo blk呢
SQL> select * from test;
A
----------
9
9
SQL> select * from test;
A
----------
9
9
SQL> select file#,block# from (select dbms_rowid.rowid_relative_fno(rowid) file#
,dbms_rowid.rowid_block_number(rowid) block# from test);
FILE# BLOCK#
---------- ----------
6 50
6 50
SQL> update test set a=9;
2 rows updated.
SQL> select XIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK
---------- ---------- ---------- ---------- ----------
4 25 407 2 1630
SQL> select name from v$rollname where usn=4;
NAME
------------------------------
_SYSSMU4$
SQL> alter system dump datafile 6 block 50;
System altered.
SQL> alter system dump undo header '_SYSSMU4$';
System altered.
buffer tsn: 7 rdba: 0x01800032 (6/50)
scn: 0x0000.00277541 seq: 0x01 flg: 0x04 tail: 0x75410601
frmt: 0x02 chkval: 0x2ff4 type: 0x06=trans data
Block header dump: 0x01800032
Object id on Block? Y
seg/obj: 0xc972 csc: 0x00.23b1db itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.019.00000197 0x0080065e.0229.0e ---- 2 fsc 0x0000.00000000
0x02 0x0009.022.000001b7 0x0080008c.0250.63 C--- 0 scn 0x0000.0023916f
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 0a
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 2] c1 0a
end_of_block_dump
SQL> variable file# number;
SQL> variable blk# number;
SQL> execute :file#:=dbms_utility.data_block_address_file(to_number('80065e','xx
xxxxx'));
PL/SQL procedure successfully completed.
SQL> execute :blk#:=dbms_utility.data_block_address_block(to_number('80065e','xx
xxxxx'));
PL/SQL procedure successfully completed.
SQL> print file#
FILE#
----------
2
SQL> print blk#
BLK#
----------
1630
SQL> select file_id,block_id from dba_extents where segment_name='_SYSSMU4$';
FILE_ID BLOCK_ID
---------- ----------
2 57
2 17
2 1545
SQL> select file#,block#,class#,status from v$bh where file# in(6,2) and block#
in(1630,57,17,1545,50);
FILE# BLOCK# CLASS# STATUS
---------- ---------- ---------- -------
2 57 23 xcur
6 50 1 cr
6 50 1 xcur
2 1630 24 xcur
SQL> alter system flush buffer_cache;
System altered.
SQL> select file#,block#,class#,status ,dirty from v$bh where file# in(6,2) and
block# in(1630,57,17,1545,50);
FILE# BLOCK# CLASS# STATUS D
---------- ---------- ---------- ------- -
2 57 23 free N
6 50 1 free N
6 50 1 free N
2 1630 24 free N
SQL> commit;~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~执行updata 的 session commit
Commit complete.
QL> /
FILE# BLOCK# CLASS# STATUS D
--------- ---------- ---------- ------- -
2 57 23 free N
2 57 23 xcur Y~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~读入 undo header了 修改了 undo header tran tab
6 50 1 free N
6 50 1 free N
2 1630 24 free N
SQL> select * from test;~~~~~~~~~~~~~~~~~~~~另一个SESSION 查询,产生delayed block cleanout
A
----------
9
9
SQL> /
FILE# BLOCK# CLASS# STATUS D
---------- ---------- ---------- ------- -
2 57 23 free N
2 57 23 xcur Y~~~~~~~~~~~~~~~~~
6 50 1 free N
6 50 1 free N
6 50 1 xcur Y~~~~~~~~~~~~~~~~~~~~~~~~读入data block 产生delayed block cleanout
2 1630 24 free N
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SQL> select * from test;
A
----------
9
9
SQL> update test set a=6;
2 rows updated.
SQL> alter system dump datafile 6 block 50;
System altered.
Dump file e:\oracle\product\10.1.0\admin\orcl\udump\orcl_ora_16484.trc
Mon May 04 18:09:40 2009
ORACLE V10.1.0.2.0 - Production vsnsta=0
vsnsql=13 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows XP Version V5.1 Service Pack 2
CPU : 2 - type 586
Process Affinity: 0x00000000
Memory (A/P) : PH:787M/2047M, PG:2749M/3945M, VA:1716M/2047M
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 15
Windows thread id: 16484, image: ORACLE.EXE (SHAD)
*** SERVICE NAME:(SYS$USERS) 2009-05-04 18:09:40.265
*** SESSION ID:(136.3790) 2009-05-04 18:09:40.265
Start dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
buffer tsn: 7 rdba: 0x01800032 (6/50)
scn: 0x0000.00280758 seq: 0x01 flg: 0x00 tail: 0x07580601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800032
Object id on Block? Y
seg/obj: 0xc972 csc: 0x00.2777c5 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.019.00000197 0x0080065e.0229.0e C--- 0 scn 0x0000.0027778d
0x02 0x0004.023.0000019c 0x00800c26.022c.24 ---- 2 fsc 0x0000.00000000
data_block_dump,data header at 0x414025c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x0414025c
bdba: 0x01800032
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f94
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1~~~~~~~~~~~~~~~~~~~~~~~~~~~
col 0: [ 2] c1 07
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
*** 2009-05-04 18:10:12.171
SQL>
SQL> commit;
SQL> alter system dump datafile 6 block 50;
System altered.
Start dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
buffer tsn: 7 rdba: 0x01800032 (6/50)
scn: 0x0000.00280775 seq: 0x01 flg: 0x02 tail: 0x07750601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Block header dump: 0x01800032
Object id on Block? Y
seg/obj: 0xc972 csc: 0x00.2777c5 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.019.00000197 0x0080065e.0229.0e C--- 0 scn 0x0000.0027778d
0x02 0x0004.023.0000019c 0x00800c26.022c.24 --U- 2 fsc 0x0000.00280775
data_block_dump,data header at 0x414025c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x0414025c
bdba: 0x01800032
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f94
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col 0: [ 2] c1 07
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system dump datafile 6 block 50;
Start dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
buffer tsn: 7 rdba: 0x01800032 (6/50)
scn: 0x0000.00280775 seq: 0x01 flg: 0x06 tail: 0x07750601
frmt: 0x02 chkval: 0x26ec type: 0x06=trans data
Block header dump: 0x01800032
Object id on Block? Y
seg/obj: 0xc972 csc: 0x00.2777c5 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.019.00000197 0x0080065e.0229.0e C--- 0 scn 0x0000.0027778d
0x02 0x0004.023.0000019c 0x00800c26.022c.24 --U- 2 fsc 0x0000.00280775
data_block_dump,data header at 0x414025c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x0414025c
bdba: 0x01800032
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f94
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
System altered.
SQL> select * from test;
A
----------
6
6
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.019.00000197 0x0080065e.0229.0e C--- 0 scn 0x0000.0027778d
0x02 0x0004.023.0000019c 0x00800c26.022c.24 --U- 2 fsc 0x0000.00280775
data_block_dump,data header at 0x414025c
===============
tsiz: 0x1fa0
hsiz: 0x16
pbl: 0x0414025c
bdba: 0x01800032
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f94
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f9a
0x14:pri[1] offs=0x1f94
block_row_dump:
tab 0, row 0, @0x1f9a
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
tab 0, row 1, @0x1f94
tl: 6 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 2] c1 07
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 50 maxblk 50
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12020513/viewspace-607615/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12020513/viewspace-607615/