[20150130]块清理(block cleanout).txt

1.建立测试环境:

create table t2 as select * from dept ;
SCOTT@test> select rowid,t2.* from t2;
ROWID                    DEPTNO DNAME          LOC
------------------ ------------ -------------- -------------
AAAOQdAAEAAAAGGAAA           10 ACCOUNTING     NEW YORK
AAAOQdAAEAAAAGGAAB           20 RESEARCH       DALLAS
AAAOQdAAEAAAAGGAAC           30 SALES          CHICAGO
AAAOQdAAEAAAAGGAAD           40 OPERATIONS     BOSTON

SCOTT@test> @ &r/lookup_rowid AAAOQdAAEAAAAGGAAA
OBJECT         FILE        BLOCK          ROW DBA    TEXT
------- ------------ ------------ ------------ ------ ----------------------------------------
  58397            4          390            0 4,390  alter system dump datafile 4 block 390 ;

SCOTT@test> update t2 set loc=upper(loc) where deptno=10;
1 row updated.

SCOTT@test> alter system checkpoint ;
System altered.

SCOTT@test> alter system flush BUFFER_CACHE;
System altered.

SCOTT@test> alter system dump datafile 4 block 390;
System altered.
---注意没有提交.

Block header dump:  0x01000186
Object id on Block? Y
seg/obj: 0xe41d  csc: 0x02.cb061ba3  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000181 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.01e.00001452  0x00801638.09ca.20  C---    0  scn 0x0002.cb061a46
0x02   0x000a.026.00001450  0x0080163b.09ca.03  ----    1  fsc 0x0000.00000000

2.执行提交以后:

--提交以后:
SCOTT@test> commit ;
Commit complete.

SCOTT@test> alter system checkpoint ;
System altered.

SCOTT@test> alter system flush BUFFER_CACHE;
System altered.

SCOTT@test> alter system dump datafile 4 block 390;
System altered.

Block header dump:  0x01000186
Object id on Block? Y
seg/obj: 0xe41d  csc: 0x02.cb061ba3  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000181 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.01e.00001452  0x00801638.09ca.20  C---    0  scn 0x0002.cb061a46
0x02   0x000a.026.00001450  0x0080163b.09ca.03  ----    1  fsc 0x0000.00000000

--因为数据已经在磁盘,提交并没有改写块的内容.对比没有变化.

3.执行select看看:

SCOTT@test> select * from t2 where deptno=10 ;
      DEPTNO DNAME          LOC
------------ -------------- -------------
          10 ACCOUNTING     NEW YORK

SCOTT@test> alter system flush BUFFER_CACHE;
System altered.

SCOTT@test> alter system checkpoint ;
System altered.

SCOTT@test> alter system dump datafile 4 block 390;
System altered.

Block header dump:  0x01000186
Object id on Block? Y
seg/obj: 0xe41d  csc: 0x02.cb061c51  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000181 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.01e.00001452  0x00801638.09ca.20  C---    0  scn 0x0002.cb061a46
0x02   0x000a.026.00001450  0x0080163b.09ca.03  C---    0  scn 0x0002.cb061be7

--使用diff比较:

$  diff -Nur /tmp/a1.txt /tmp/a3.txt
--- /tmp/a1.txt 2015-01-30 10:07:23.340509813 +0800
+++ /tmp/a3.txt 2015-01-30 10:14:48.975510120 +0800
@@ -1,12 +1,12 @@
Block header dump:  0x01000186
  Object id on Block? Y
- seg/obj: 0xe41d  csc: 0x02.cb061ba3  itc: 2  flg: E  typ: 1 - DATA
+ seg/obj: 0xe41d  csc: 0x02.cb061c51  itc: 2  flg: E  typ: 1 - DATA
      brn: 0  bdba: 0x1000181 ver: 0x01 opc: 0
      inc: 0  exflg: 0

  Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.01e.00001452  0x00801638.09ca.20  C---    0  scn 0x0002.cb061a46
-0x02   0x000a.026.00001450  0x0080163b.09ca.03  ----    1  fsc 0x0000.00000000
+0x02   0x000a.026.00001450  0x0080163b.09ca.03  C---    0  scn 0x0002.cb061be7

data_block_dump,data header at 0x6976c64
===============
@@ -30,7 +30,7 @@
0x18:pri[3] offs=0x1f80
block_row_dump:
tab 0, row 0, @0x1f3c
-tl: 26 fb: --H-FL-- lb: 0x2  cc: 3
+tl: 26 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 2]  c1 0b
col  1: [10]  41 43 43 4f 55 4e 54 49 4e 47
col  2: [ 8]  4e 45 57 20 59 4f 52 4b

--对比很容易知道两者的变化.