在《Oracle性能优化与诊断案例精选》一书中有一个测试案例，读者提出异议，我做了一个更详细的测试供读者参考。书中内容并无问题，但是过程省略了一些细节，导致读者的困惑，作为主编，我要表示歉意。
测试表明的结果是：DUMP Datafile不会引起脏数据块写入数据文件。
但是如何验证呢？
我们首先执行一个数据行插入提交，执行检查点，这条数据被写入磁盘；
再插入一条数据，提交，此时这条数据不会写入磁盘。
通过DUMP BLOCK，从数据文件转储，只能看到一行记录，通过DUMP Buffer Cache Block，能够看到两条记录。
这里的知识点是，如何DUMP Buffer Cache中的数据块，书中未提及这个知识点，但是隐含的用到了。
1.以下步骤执行了数据写入和DUMP，我们看到DUMP Datafile时只有一条记录存在于数据文件之上
[eygle@enmoteam1 ~]$ sqlplus eygle/eygle
SQL*Plus: Release 11.2.0.3.0 Production on Fri Apr 7 14:24:00 2017
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> CREATE TABLE eygle (n varchar2(20));
Table created.
SQL> INSERT INTO eygle VALUES('ENMOTECH');
1 row created.
SQL> commit;
Commit complete.
SQL> ALTER system checkpoint;
System altered.
SQL> INSERT INTO eygle VALUES('YHEM');
1 row created.
SQL> commit;
Commit complete.
SQL> select dbms_rowid.rowid_relative_fno(rowid)fno,
2 dbms_rowid.rowid_block_number (rowid) block# from eygle;
FNO BLOCK#
---------- ----------
20 2362757
20 2362757
SQL> alter system dump datafile 20 block 2362757;
System altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9165.trc
SQL> ! cat /u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9165.trc
Trace file /u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9165.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: enmoteam1
Release: 2.6.39-200.24.1.el6uek.x86_64
Version: #1 SMP Sat Jun 23 02:39:07 EDT 2012
Machine: x86_64
Instance name: enmot1
Redo thread mounted by this instance: 1
Oracle process number: 82
Unix process pid: 9165, image: oracle@enmoteam1 (TNS V1-V3)
*** 2017-04-07 14:25:11.875
*** SESSION ID:(256.5763) 2017-04-07 14:25:11.875
*** CLIENT ID:() 2017-04-07 14:25:11.875
*** SERVICE NAME:(SYS$USERS) 2017-04-07 14:25:11.875
*** MODULE NAME:(SQL*Plus) 2017-04-07 14:25:11.875
*** ACTION NAME:() 2017-04-07 14:25:11.875
Start dump data blocks tsn: 4 file#:20 minblk 2362757 maxblk 2362757
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4 rdba=86248837
BH (0x643e75e8) file#: 20 rdba: 0x05240d85 (20/2362757) class: 1 ba: 0x641a0000
set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: 107958 objn: 107958 tsn: 4 afn: 20 hint: f
hash: [0x647f09d8,0x8fc75b48] lru: [0x62fefbd0,0x63fee080]
obj-flags: object_ckpt_list
ckptq: [0x8ff1f8f0,0x647de048] fileq: [0x8ff1fb70,0x8ff1fb70] objq: [0x87841758,0x87841758] objaq: [0x64feb008,0x87841738]
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 1
flags: buffer_dirty block_written_once redo_since_read
LRBA: [0x346.21360.0] LSCN: [0x0.2af3ac2e] HSCN: [0x0.2af3ac2e] HSUB: [1]
BH (0x647f0928) file#: 20 rdba: 0x05240d85 (20/2362757) class: 1 ba: 0x64698000
set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: 107957 objn: 107957 tsn: 4 afn: 20 hint: f
hash: [0x8fc75b48,0x643e7698] lru: [0x637ecb20,0x653f7750]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: FREE md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 0 lfb: 33
flags:
Block dump from disk:
buffer tsn: 4 rdba: 0x05240d85 (20/2362757)
scn: 0x0000.2af3ac29 seq: 0x01 flg: 0x06 tail: 0xac290601
frmt: 0x02 chkval: 0xe737 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00007F35FD4B9200 to 0x00007F35FD4BB200
7F35FD4B9200 0000A206 05240D85 2AF3AC29 06010000 [......$.)..*....]
7F35FD4B9210 0000E737 00000001 0001A5B6 2AF3AC27 [7...........'..*]
7F35FD4B9220 00000000 00320002 05240D80 00110005 [......2...$.....]
7F35FD4B9230 000E5D0F 054000C9 001256BA 00002001 [.]....@..V... ..]
7F35FD4B9240 2AF3AC29 00000000 00000000 00000000 [)..*............]
7F35FD4B9250 00000000 00000000 00000000 00000000 [................]
7F35FD4B9260 00000000 00010100 0014FFFF 1F781F8C [..............x.]
7F35FD4B9270 00001F78 1F8C0001 5029F80F 5029F80F [x.........)P..)P]
7F35FD4B9280 00000000 00000000 00000000 00000000 [................]
7F35FD4B9290 008012A1 000000F4 00000000 00000000 [................]
7F35FD4B92A0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7F35FD4B92C0 000018C3 000D873C 00000000 0084A200 [....<...........]
7F35FD4B92D0 00000100 00000000 00000000 00000000 [................]
7F35FD4B92E0 00000000 00000000 00000000 00000000 [................]
Repeat 9 times
7F35FD4B9380 00000000 00000000 00000000 21112211 [.............".!]
7F35FD4B9390 22111111 11121121 12112112 21111121 [..."!....!..!..!]
7F35FD4B93A0 11111111 11111111 11111112 21111111 [...............!]
7F35FD4B93B0 11111111 21121111 11212111 11111111 [.......!.!!.....]
7F35FD4B93C0 11211112 21221111 12111111 11111111 [..!..."!........]
7F35FD4B93D0 11112111 11111111 12112122 12111111 [.!......"!......]
7F35FD4B93E0 11111111 11111311 22111111 11222111 [...........".!".]
7F35FD4B93F0 11111111 11111131 11111112 11221111 [....1.........".]
7F35FD4B9400 11111211 11111231 11111111 00000000 [....1...........]
7F35FD4B9410 00000000 00000000 00000000 00000000 [................]
Repeat 477 times
7F35FD4BB1F0 0801012C 4F4D4E45 48434554 AC290601 [,...ENMOTECH..).]
Block header dump: 0x05240d85
Object id on Block? Y
seg/obj: 0x1a5b6 csc: 0x00.2af3ac27 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x5240d80 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.011.000e5d0f 0x054000c9.56ba.12 --U- 1 fsc 0x0000.2af3ac29
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
bdba: 0x05240d85
data_block_dump,data header at 0x7f35fd4b9264
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x7f35fd4b9264
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8c
avsp=0x1f78
tosp=0x1f78
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f8c
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 8] 45 4e 4d 4f 54 45 43 48
end_of_block_dump
End dump data blocks tsn: 4 file#: 20 minblk 2362757 maxblk 2362757
2.进一步的DUMP Buffer Cache Block，这里有两个命令：
ALTER SESSION SET EVENTS 'immediate trace name set_tsn_p1 level level'; -- 这里的 level 是 表空间号 +1；
ALTER SESSION SET EVENTS 'immediate trace name buffer level level'; -- 这里的 level 是 相对文件号 * 4194304 + BlockNumber
经过转储可以看到Cache中的两条记录：
SQL> connect eygle/eygle
Connected.
SQL> select dbms_rowid.rowid_relative_fno(rowid)fno,
2 dbms_rowid.rowid_block_number (rowid) block# from eygle;
FNO BLOCK#
---------- ----------
20 2362757
20 2362757
SQL> select 20*4194304 +2362757 from dual;
20*4194304+2362757
------------------
86248837
SQL> select default_tablespace from dba_users where username='EYGLE';
DEFAULT_TABLESPACE
------------------------------
USERS
SQL> SELECT ts# FROM sys.ts$ WHERE name = 'USERS';
TS#
----------
4
SQL> ALTER SESSION SET EVENTS 'immediate trace name set_tsn_p1 level 5';
Session altered.
SQL> ALTER SESSION SET EVENTS 'immediate trace name buffer level 86248837';
Session altered.
SQL> select value from v$diag_info where name='Default Trace File';
VALUE
-----------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9214.trc
SQL> ! cat /u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9214.trc
Trace file /u01/app/oracle/diag/rdbms/enmot1/enmot1/trace/enmot1_ora_9214.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: enmoteam1
Release: 2.6.39-200.24.1.el6uek.x86_64
Version: #1 SMP Sat Jun 23 02:39:07 EDT 2012
Machine: x86_64
Instance name: enmot1
Redo thread mounted by this instance: 1
Oracle process number: 83
Unix process pid: 9214, image: oracle@enmoteam1 (TNS V1-V3)
*** 2017-04-07 14:26:33.824
*** SESSION ID:(377.2369) 2017-04-07 14:26:33.824
*** CLIENT ID:() 2017-04-07 14:26:33.824
*** SERVICE NAME:(SYS$USERS) 2017-04-07 14:26:33.824
*** MODULE NAME:(SQL*Plus) 2017-04-07 14:26:33.824
*** ACTION NAME:() 2017-04-07 14:26:33.824
Dump of buffer cache at level 10 for tsn=4 rdba=86248837
BH (0x643e75e8) file#: 20 rdba: 0x05240d85 (20/2362757) class: 1 ba: 0x641a0000
set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: 107958 objn: 107958 tsn: 4 afn: 20 hint: f
hash: [0x647f09d8,0x8fc75b48] lru: [0x62fefbd0,0x63fee080]
obj-flags: object_ckpt_list
ckptq: [0x64fecc98,0x647de048] fileq: [0x8ff1fb70,0x8ff1fb70] objq: [0x87841758,0x87841758] objaq: [0x64feb008,0x87841738]
st: XCURRENT md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 1
flags: buffer_dirty block_written_once redo_since_read
LRBA: [0x346.21360.0] LSCN: [0x0.2af3ac2e] HSCN: [0x0.2af3ac2e] HSUB: [1]
buffer tsn: 4 rdba: 0x05240d85 (20/2362757)
scn: 0x0000.2af3ac2e seq: 0x02 flg: 0x02 tail: 0xac2e0602
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x00000000641A0000 to 0x00000000641A2000
0641A0000 0000A206 05240D85 2AF3AC2E 02020000 [......$....*....]
0641A0010 00000000 00000001 0001A5B6 2AF3AC27 [............'..*]
0641A0020 00000000 00320002 05240D80 00110005 [......2...$.....]
0641A0030 000E5D0F 054000C9 001256BA 00002001 [.]....@..V... ..]
0641A0040 2AF3AC29 000C0007 000E6208 05400148 [)..*.....b..H.@.]
0641A0050 002D5CA1 00002001 2AF3AC2E 00000000 [.\\-.. .....*....]
0641A0060 00000000 00020100 0016FFFF 1F6D1F84 [..............m.]
0641A0070 00001F6D 1F8C0002 50291F84 5029F80F [m.........)P..)P]
0641A0080 00000000 00000000 00000000 00000000 [................]
0641A0090 008012A1 000000F4 00000000 00000000 [................]
0641A00A0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
0641A00C0 000018C3 000D873C 00000000 0084A200 [....<...........]
0641A00D0 00000100 00000000 00000000 00000000 [................]
0641A00E0 00000000 00000000 00000000 00000000 [................]
Repeat 9 times
0641A0180 00000000 00000000 00000000 21112211 [.............".!]
0641A0190 22111111 11121121 12112112 21111121 [..."!....!..!..!]
0641A01A0 11111111 11111111 11111112 21111111 [...............!]
0641A01B0 11111111 21121111 11212111 11111111 [.......!.!!.....]
0641A01C0 11211112 21221111 12111111 11111111 [..!..."!........]
0641A01D0 11112111 11111111 12112122 12111111 [.!......"!......]
0641A01E0 11111111 11111311 22111111 11222111 [...........".!".]
0641A01F0 11111111 11111131 11111112 11221111 [....1.........".]
0641A0200 11111211 11111231 11111111 00000000 [....1...........]
0641A0210 00000000 00000000 00000000 00000000 [................]
Repeat 476 times
0641A1FE0 00000000 00000000 0401022C 4D454859 [........,...YHEM]
0641A1FF0 0801012C 4F4D4E45 48434554 AC2E0602 [,...ENMOTECH....]
Block header dump: 0x05240d85
Object id on Block? Y
seg/obj: 0x1a5b6 csc: 0x00.2af3ac27 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x5240d80 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.011.000e5d0f 0x054000c9.56ba.12 --U- 1 fsc 0x0000.2af3ac29
0x02 0x0007.00c.000e6208 0x05400148.5ca1.2d --U- 1 fsc 0x0000.2af3ac2e
bdba: 0x05240d85
data_block_dump,data header at 0x641a0064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x641a0064
76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f84
avsp=0x1f6d
tosp=0x1f6d
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f8c
0x14:pri[1] offs=0x1f84
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 8] 45 4e 4d 4f 54 45 43 48
tab 0, row 1, @0x1f84
tl: 8 fb: --H-FL-- lb: 0x2 cc: 1
col 0: [ 4] 59 48 45 4d
end_of_block_dump
BH (0x647f0928) file#: 20 rdba: 0x05240d85 (20/2362757) class: 1 ba: 0x64698000
set: 10 pool: 3 bsz: 8192 bsi: 0 sflg: 1 pwc: 0,0
dbwrid: 0 obj: 107957 objn: 107957 tsn: 4 afn: 20 hint: f
hash: [0x8fc75b48,0x643e7698] lru: [0x637ecb20,0x653f7750]
lru-flags: on_auxiliary_list
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: FREE md: NULL fpin: 'ktspbwh2: ktspfmdb' tch: 0 lfb: 33
flags:
Buffer contents not dumped
补充测试，供读者参考，学习。
