delete、truncate 测试
测试用意：确认 delete 不会写 缓冲区中的脏块，truncate 会写 缓冲区中的脏块
测试步骤：创建测试表共三个字段，每字段类型是 char(2000)，基本上一行占据一个数据块 共两个数据块（作对比）。
首先
对表做update操作不提交， 那么缓冲区中会有该表的脏块。
对表做delete操作不提交 ， 检查对应数据块的状态；
对表做truncate操作不提交，检查对应数据块的状态
创建测试表 test1
SQL> set time on
15:13:21 SQL> create table test1(n1 char(2000),n2 char(2000),n3 char(2000));
Table created.
15:13:34 SQL> insert into test1 values('a','b','c');
1 row created.
15:13:42 SQL> commit;
Commit complete.
09:57:26 SQL> set linesize 1000
09:57:34 SQL> set pagesize 20
09:58:02 SQL> col n1 for a10
09:58:24 SQL> col n2 for a10
09:58:29 SQL> col n3 for a10
15:13:49 SQL> select rowid,a.* from test1 a ;
ROWID N1 N2 N3
------------------ ----- ----- -----
AAASRmAAEAAAAI0AAA a b c
15:15:25 SQL> insert into test1 values('d','e','f');
1 row created.
15:15:41 SQL> commit;
Commit complete.
15:15:44 SQL> select rowid,a.* from test1 a ;
ROWID N1 N2 N3
------------------ ----- ----- -----
AAASRmAAEAAAAI0AAA a b c
AAASRmAAEAAAAI1AAA d e f
15:19:31 SQL> select get_rowid('AAASRmAAEAAAAI0AAA') from dual;
GET_ROWID('AAASRMAAEAAAAI0AAA')
--------------------------------------------------------------------------------
Object# is :74854
Relative_fno is :4
Block number is :564
Row number is :0
15:19:33 SQL> select get_rowid('AAASRmAAEAAAAI1AAA') from dual;
GET_ROWID('AAASRMAAEAAAAI1AAA')
--------------------------------------------------------------------------------
Object# is :74854
Relative_fno is :4
Block number is :565
Row number is :0
对数据块做更新操作
15:22:39 SQL> update test1 set n1='aa' where n1='a';
1 row updated.
检查缓冲区中 数据块的状态
15:23:48 SQL> select file#,block#,dirty,status from v$bh where file#=4 and block# in(564,565);
FILE# BLOCK# D STATUS
---------- ---------- - ----------
4 564 N free
4 564 N cr
4 564 N xcur
4 565 N free
4 565 N xcur
对 564数据块做删除操作
15:22:41 SQL> delete from test1 where n2='b';
1 row deleted.
再次检查缓冲区中 数据块的状态
15:28:46 SQL> select file#,block#,dirty,status from v$bh where file#=4 and block# in(564,565);
FILE# BLOCK# D STATUS
---------- ---------- - ----------
4 564 N free
4 564 N free
4 564 N free
4 564 N cr
4 564 Y xcur
4 565 N free
4 565 N free
4 565 N xcur
-----------------------------------------------------------------------------------------------------
15:25:38 SQL> alter system flush buffer_cache;
System altered.
15:32:08 SQL> select file#,block#,dirty,status from v$bh where file#=4 and block# in(564,565);
FILE# BLOCK# D STATUS
---------- ---------- - ----------
4 564 N free
4 564 N free
4 564 N free
4 564 N free
4 564 N free
4 565 N free
4 565 N free
4 565 N free
15:32:54 SQL> update test1 set n1='aa' where n1='a';
1 row updated.
15:34:42 SQL> truncate table test1;
Table truncated.
15:35:15 SQL> select file#,block#,dirty,status from v$bh where file#=4 and block# in(564,565);
FILE# BLOCK# D STATUS
---------- ---------- - ----------
4 564 N free
4 564 N free
4 564 N free
4 564 N free
4 564 N free
4 564 N cr
4 565 N free
4 565 N free
4 565 N free
4 565 N free
