[20151218]dml未提交事务最新内容到底存储在什么地方.txt
--刚刚看了http://www.itpub.net/thread-1940809-1-1.html上的讨论。
--我自己的想法应该在buffer cache中，自己也做1些测试，说明问题：
1.环境：
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t (id number, name varchar2(20)) ;
Table created.
SCOTT@book> insert into t values (1,'AAAA');
1 row created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> select rowid,t.* from t;
ROWID ID NAME
------------------ ---------- --------------------
AAAVr7AAEAAAAIPAAA 1 AAAA
SCOTT@book> @ &r/rowid AAAVr7AAEAAAAIPAAA
OBJECT FILE BLOCK ROW DBA TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
88827 4 527 0 4,527 alter system dump datafile 4 block 527 ;
--实际上如果你不写盘，通过bbed观察看到的内容是不对的。一般这个时候我要通过bbed观察要执行alter system checkpoint,或者刷新脏块到磁盘。
SCOTT@book> alter system checkpoint;
System altered.
--这个时候通过bbed观察可以发现数据已经写盘。
BBED> set dba 4,527
DBA 0x0100020f (16777743 4,527)
BBED> p *kdbr[0]
rowdata[0]
----------
ub1 rowdata[0] @8177 0x2c
BBED> x /rnc
rowdata[0] @8177
flag@8177: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8178: 0x00
cols@8179: 2
col 0[2] @8180: 1
col 1[4] @8183: AAAA
2.开始测试：
SYS@book> @ &r/bh 4 527
HLADDR DBARFIL DBABLK CLASS CLASS_TYPE STATE TCH CR_SCN_BAS CR_SCN_WRP CR_UBA_FIL CR_UBA_BLK CR_UBA_SEQ BA OBJECT_NAME
---------------- ---------- ---------- ---------- ------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------------- --------------------
0000000083EED060 4 527 1 data block xcur 1 0 0 0 0 0 000000007469A000 T
SCOTT@book> update t set name='BBBB' where id=1;
1 row updated.
--不提交！
0000000083EED060 4 527 1 data block xcur 1 0 0 0 0 0 000000007B07E000 T
0000000083EED060 4 527 1 data block cr 1 1475062 0 0 0 0 000000007469A000 T
--可以发现两个STATE不一样。原来变成了BA=000000007469A000,xucr=>cr. XCUR表示当前块的状态。
-- BA 表示就是块地址，如果检查里面的内容就知道问题。数据块大小8192. 8192 = 0x2000.而且我仅仅插入一条应该靠近块的底板。
SYS@book> oradebug setmypid
Statement processed.
--0x000000007B07E000+0x2000-0x10 = 0x7B07FFF0
--0x000000007469A000+0x2000-0x10 = 0x7469BFF0
SYS@book> oradebug peek 0x7B07FFF0 16
[07B07FFF0, 07B080000) = 02022C00 0402C102 42424242 81FA0601
SYS@book> oradebug peek 0x7469BFF0 16
[07469BFF0, 07469C000) = 02002C00 0402C102 41414141 7FBF0602
--ASCII=0x41=>对应的就是A ， ASCII=0x42=>对应的就是B.从这里可以证明DML未提交事务最新内容到底存储在buffer cache中。
--实际上你这样执行一个alter system checkpoint;或者alter system flush buffer_cache;再转储就可以看到。
3.转储问题：
--实际上这个问题一直困扰我以前的学习，执行dml未提交如果这个时候参考转储的块，因为没有写盘，往往看到错误的信息。
--换1句话讲alter system dump datafile 4 block 527 ;看到的磁盘文件的信息，如何看内存的信息呢？可以参考我写另外一篇blog。
<a href="http://blog.itpub.net/267265/viewspace-1659981/">http://blog.itpub.net/267265/viewspace-1659981/</a>
--继续测试：
SCOTT@book> update t set name='CCCC' where id=1;
0000000083EED060 4 527 1 data block xcur 1 0 0 0 0 0 0000000077F3A000 T
0000000083EED060 4 527 1 data block cr 1 1476290 0 3 3014 498 000000007839A000 T
0000000083EED060 4 527 1 data block cr 1 1476289 0 3 3014 498 0000000078372000 T
0000000083EED060 4 527 1 data block cr 1 1476338 0 0 0 0 00000000786CC000 T
0000000083EED060 4 527 1 data block free 0 0 0 0 0 0 000000007B07E000 T
0000000083EED060 4 527 1 data block free 0 0 0 0 0 0 000000007469A000 T
6 rows selected.
--注意看STATE=xcur,BA=0x0000000077F3A000,0x0000000077F3A000+0x2000-0x10=0x77F3BFF0.
SYS@book> oradebug peek 0x77F3BFF0 16
[077F3BFF0, 077F3C000) = 02012C00 0402C102 43434343 86EF0601
SCOTT@book> select dump('CCCC',16) from dual ;
DUMP('CCCC',16)
-------------------------
Typ=96 Len=4: 43,43,43,43
--正好对上。
--DBA地址是16777743。
lock@8178: 0x02
col 1[4] @8183: BBBB
--可以发现依旧是旧的信息。name='BBBB'.
SCOTT@book> alter session set events 'immediate trace name set_tsn_p1 level 5';
Session altered.
SCOTT@book> ALTER SESSION SET EVENTS 'immediate trace name buffer level 16777743';
--也可以执行ALTER SESSION SET EVENTS 'immediate trace name buffer level 0x0100020f';
Dump of buffer cache at level 10 for tsn=4 rdba=16777743
BH (0x77ff5638) file#: 4 rdba: 0x0100020f (4/527) class: 1 ba: 0x77f3a000
set: 56 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 0,15
dbwrid: 1 obj: 88827 objn: 88827 tsn: 4 afn: 4 hint: f
hash: [0x783f9170,0x83eee1e0] lru: [0x77ff5860,0x77ff55f0]
ckptq: [NULL] fileq: [NULL] objq: [0x7c43a348,0x7c43a348] objaq: [0x7c43a338,0x7c43a338]
st: XCURRENT md: NULL fpin: 'kdswh11: kdst_fetch' tch: 1
flags: block_written_once redo_since_read
LRBA: [0x0.0.0] LSCN: [0x0.0] HSCN: [0xffff.ffffffff] HSUB: [1]
buffer tsn: 4 rdba: 0x0100020f (4/527)
scn: 0x0000.0016875c seq: 0x01 flg: 0x04 tail: 0x875c0601
frmt: 0x02 chkval: 0x6d86 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000000077F3A000 to 0x0000000077F3C000
077F3A000 0000A206 0100020F 0016875C 04010000 [........\.......]
077F3A010 00006D86 00000001 00015AFB 001686F3 [.m.......Z......]
077F3A020 00000000 00320002 01000208 00120004 [......2.........]
077F3A030 000002FD 00C0014B 0020011C 00000001 [....K..... .....]
077F3A040 00000000 000D000A 00000A1A 00C00BC6 [................]
077F3A050 001801F2 00008000 001686EF 00000000 [................]
077F3A060 00000000 00010100 0014FFFF 1F791F8D [..............y.]
077F3A070 00001F79 1F8D0001 00000000 00000000 [y...............]
077F3A080 00000000 00000000 00000000 00000000 [................]
Repeat 502 times
077F3BFF0 02012C00 0402C102 43434343 875C0601 [.,......CCCC..\.]
Block header dump: 0x0100020f
Object id on Block? Y
seg/obj: 0x15afb csc: 0x00.1686f3 itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000208 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0004.012.000002fd 0x00c0014b.011c.20 ---- 1 fsc 0x0000.00000000
0x02 0x000a.00d.00000a1a 0x00c00bc6.01f2.18 C--- 0 scn 0x0000.001686ef
bdba: 0x0100020f
data_block_dump,data header at 0x77f3a064
===============
tsiz: 0x1f98
hsiz: 0x14
pbl: 0x77f3a064
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8d
avsp=0x1f79
tosp=0x1f79
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f8d
block_row_dump:
tab 0, row 0, @0x1f8d
tl: 11 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 02
col 1: [ 4] 43 43 43 43
end_of_block_dump
BH (0x783f90b8) file#: 4 rdba: 0x0100020f (4/527) class: 1 ba: 0x7839a000
set: 59 pool: 3 bsz: 8192 bsi: 0 sflg: 2 pwc: 1,15
hash: [0x783f7910,0x77ff56f0] lru: [0x7b3e5c18,0x783f9070]
ckptq: [NULL] fileq: [NULL] objq: [NULL] objaq: [NULL]
st: CR md: NULL tch: 1
cr: [scn: 0x0.1686c2],[xid: 0xa.d.a1a],[uba: 0xc00bc6.1f2.18],[cls: 0x0.1686c2],[sfl: 0x0],[lc: 0x0.1686c2]
flags: only_sequential_access
scn: 0x0000.001686c3 seq: 0x00 flg: 0x00 tail: 0x86c30600
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data
Dump of memory from 0x000000007839A000 to 0x000000007839C000
07839A000 0000A206 0100020F 001686C3 00000000 [................]
07839A010 00000000 00000001 00015AFB 001686C2 [.........Z......]
07839A020 00000000 00320002 01000208 0009000A [......2.........]
07839A030 00000A19 00C00BC2 001E01F2 00008000 [................]
07839A040 00167F29 00000000 00000000 00000000 [)...............]
07839A050 00000000 00000000 00000000 00000000 [................]
07839A060 00000000 00010100 0014FFFF 1F791F8D [..............y.]
07839A070 00001F79 1F8D0001 00000000 00000000 [y...............]
07839A080 00000000 00000000 00000000 00000000 [................]
07839BFF0 02002C00 0402C102 41414141 86C30600 [.,......AAAA....]
seg/obj: 0x15afb csc: 0x00.1686c2 itc: 2 flg: E typ: 1 - DATA
0x01 0x000a.009.00000a19 0x00c00bc2.01f2.1e C--- 0 scn 0x0000.00167f29
0x02 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
data_block_dump,data header at 0x7839a064
pbl: 0x7839a064
tl: 11 fb: --H-FL-- lb: 0x0 cc: 2
col 1: [ 4] 41 41 41 41
--我这里仅仅截取其中1段，ba: 0x77f3a000，ba: 0x7839a000。这样就可以看到内存中该数据块的信息了。
![oracle中的start with connect by用法[图]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)