Oracle 事务一致性之块清理（block cleanout）

block cleanout原理方面不做过多解释，主要记录测试过程，备忘。
对测试表格做dml操作，记录其scn值
SQL> update zhoul set name='aaa';

3 rows updated.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select current_scn scn from v$database;

SCN
------------------
[color=red]10995251665103
[/color]
SQL> update zhoul set name='bbb';

3 rows updated.

SQL> commit;

Commit complete.

SQL> alter system checkpoint;

System altered.

SQL> select current_scn scn from v$database;

SCN
------------------
10995251665322



SQL> select * from zhoul;

I NAME
---------- --------------------
1 bbb
2 bbb
3 bbb

dump datafile block可以看出其block所有行都处于提交状态。

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0023.02f.0000004e 0x0280073e.0010.2b --U- 3 fsc 0x0000.0811d995
0x02 0x0029.02d.0000004d 0x028008c5.0010.37 C--- 0 scn 0x0a00.0811d8b7

物理上修改block，修改flag表示，此block有事务存在，锁住的行数为2行。
BBED> modify 0x0200


BBED> dump offset 88
File: /oradata/mcstar/zhoul01.dbf (0)
Block: 15511 Offsets: 88 to 599 Dba:0x00000000
------------------------------------------------------------------------
b7d81108 00000000 00000000 00010300 ffff1800 001f621f 621f0000 0300141f
0a1f001f 311fff1e cb1e5c1e 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

修改此事务槽的ckpt值为[color=red]A000811D8CF[/color]。
BBED> modify 0xcf
File: /oradata/mcstar/zhoul01.dbf (0)
Block: 15511 Offsets: 88 to 599 Dba:0x00000000
------------------------------------------------------------------------
cfd81108 00000000 00000000 00010300 ffff1800 001f621f 621f0000 0300141f
0a1f001f 311fff1e cb1e5c1e 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000
00000000 00000000 00000000 00000000 00000000 00000000 00000000 00000000

<32 bytes per line>

BBED> sum apply
Check value for File 0, Block 15511:
current = 0x3274, required = 0x3274
再次执行表格查询，可看到值保持不变。
SQL> select * from zhoul;

I NAME
---------- --------------------
1 bbb
2 bbb
3 bbb

dump block可以看出Oracle已进行块清理
seg/obj: 0x21636 [color=red]csc: 0xa00.814c121[/color] itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1c03c91 ver: 0x01 opc: 0
inc: 0 exflg: 0

Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0023.02f.0000004e 0x0280073e.0010.2b C--- 0 scn 0x0a00.0811d995
0x02 0x0029.02d.0000004d 0x028008c5.0010.37 C-U- 0 scn 0x0a00.08144534
物理上也已做了修改
BBED> dump block 15511 offset 84 count 8
File: /oradata/mcstar/zhoul01.dbf (0)
Block: 15511 Offsets: 84 to 91 Dba:0x00000000
------------------------------------------------------------------------
00a0000a 34451408