MSSQL编程：关于Undo Internal的研讨

提供多语言支持，常见的编码如中文的GB2312、BIG5，日文的Shift_JIS等都可以用作数据表名和数据列名。
原文链接:
http://www.eygle.com/internal/undo_internal.htm


本文就Undo的外部布局作开端切磋:

我们经由过程实行来看一下回滚段的外部布局.

测试剧本及历程以下:

起首创立一个测试表
createtableud(nnumber);
insertintoudvalues(1);
insertintoudvalues(2);
commit;

然后实行一个事物:
select*fromud;
updateudsetn=1000wheren=2;
select*fromud;

找到该事物利用的回滚段:
selectxidusn,xidslot,xidsqn,ubablk,ubafil,ubarecfromv$transaction;

取得回滚段称号并dump其内容:
selectusn,namefromv$rollnamewhereusn=7;
altersystemdumpundoheader_SYSSMU7$;

以下入手下手测试:

SQL>createtableud(nnumber);Tablecreated.SQL>insertintoudvalues(1);1rowcreated.SQL>insertintoudvalues(2);1rowcreated.SQL>commit;Commitcomplete.SQL>startupforce;ORACLEinstancestarted.TotalSystemGlobalArea47256168bytesFixedSize451176bytesVariableSize29360128bytesDatabaseBuffers16777216bytesRedoBuffers667648bytesDatabasemounted.Databaseopened.SQL>selectusn,writes,rssize,xacts,hwmsize,shrinks,wrapsfromv$rollstat;USNWRITESRSSIZEXACTSHWMSIZESHRINKSWRAPS----------------------------------------------------------------------0738038502403850240010122880012288000201228800122880003012288001228800040122880012288000501228800122880006012288001228800070122880012288000854122880012288000990401228800122880001021012288001228800011rowsselected.SQL>select*fromud;N----------12SQL>updateudsetn=1000wheren=2;1rowupdated.SQL>selectxidusn,xidslot,xidsqn,ubablk,ubafil,ubarecfromv$transaction;XIDUSNXIDSLOTXIDSQNUBABLKUBAFILUBAREC------------------------------------------------------------7352861109228SQL>selectusn,writes,rssize,xacts,hwmsize,shrinks,wrapsfromv$rollstat;USNWRITESRSSIZEXACTSHWMSIZESHRINKSWRAPS----------------------------------------------------------------------073803850240385024001012288001228800020122880012288000301228800122880004012288001228800050122880012288000601228800122880007126122880112288000854122880012288000990401228800122880001021012288001228800011rowsselected.SQL>selectusn,namefromv$rollnamewhereusn=7;USNNAME----------------------------------------7_SYSSMU7$SQL>altersystemdumpundoheader_SYSSMU7$;Systemaltered.SQL>@gettrcnameTRACE_FILE_NAME--------------------------------------------------------------------------------/opt/oracle/admin/hsjf/udump/hsjf_ora_3563.trc

找到这个trace文件:



********************************************************************************UndoSegment:_SYSSMU7$(7)********************************************************************************ExtentControlHeader-----------------------------------------------------------------ExtentHeader::spare1:0spare2:0#extents:2#blocks:15lastmap0x00000000#maps:0offset:4080Highwater::0x0080006dext#:0blk#:3extsize:7#blocksinseg.hdrsfreelists:0#blocksbelow:0mapblk0x00000000offset:0UnlockedMapHeader::next0x00000000#extents:2obj#:0flag:0x40000000ExtentMap-----------------------------------------------------------------0x0080006alength:70x008000d1length:8RetentionTable-----------------------------------------------------------ExtentNumber:0CommitTime:1095324259ExtentNumber:1CommitTime:1095296201TRNCTL::seq:0x040dchd:0x0025ctl:0x0024inc:0x00000000nfb:0x0000mgc:0x8201xts:0x0068flg:0x0001opt:2147483646(0x7ffffffe)uba:0x0080006d.040d.1cscn:0x0000.017e6e59Version:0x01FREEBLOCKPOOL::uba:0x00000000.040d.1bext:0x0spc:0x994uba:0x00000000.040c.30ext:0x5spc:0xa1euba:0x00000000.040c.43ext:0x5spc:0x198uba:0x00000000.03ed.01ext:0x0spc:0x1fa0uba:0x00000000.0000.00ext:0x0spc:0x0TRNTBL::------Transactiontable,也就是这个回滚段中纪录的事件列表indexstatecflagswrap#uelscndbaparent-xidnubstmt_num------------------------------------------------------------------------------------------------0x0090x000x0b2d0x00270x0000.017ea9920x0080006c0x0000.000.000000000x000000010x000000000x0190x000x0b2d0x00020x0000.017e9dba0x0080006c0x0000.000.000000000x000000010x000000000x0290x000x0b2d0x00040x0000.017ea1ad0x0080006c0x0000.000.000000000x000000010x000000000x0390x000x0b2d0x00010x0000.017e99c70x0080006c0x0000.000.000000000x000000010x000000000x0490x000x0b2d0x00000x0000.017ea5a00x0080006c0x0000.000.000000000x000000010x000000000x0590x000x0b2d0x000b0x0000.017ebd500x0080006c0x0000.000.000000000x000000010x000000000x0690x000x0b2d0x00050x0000.017eb95d0x0080006c0x0000.000.000000000x000000010x000000000x0790x000x0b2d0x000f0x0000.017f1fa20x0080006d0x0000.000.000000000x000000010x000000000x0890x000x0b2d0x00290x0000.017eccb80x0080006d0x0000.000.000000000x000000010x000000000x0990x000x0b2d0x00080x0000.017ec8c50x0080006d0x0000.000.000000000x000000010x000000000x0a90x000x0b2d0x00090x0000.017ec4d30x0080006d0x0000.000.000000000x000000010x000000000x0b90x000x0b2d0x000a0x0000.017ec0e00x0080006d0x0000.000.000000000x000000010x000000000x0c90x000x0b2d0x00110x0000.017ed49e0x0080006d0x0000.000.000000000x000000010x000000000x0d90x000x0b2d0x00140x0000.017ee85b0x0080006d0x0000.000.000000000x000000010x000000000x0e90x000x0b2d0x001f0x0000.017f000c0x0080006d0x0000.000.000000000x000000010x000000000x0f90x000x0b2d0x00240x0000.017f23940x0080006d0x0000.000.000000000x000000010x000000000x1090x000x0b2d0x00130x0000.017edc830x0080006d0x0000.000.000000000x000000010x000000000x1190x000x0b2d0x00100x0000.017ed8900x0080006d0x0000.000.000000000x000000010x000000000x1290x000x0b2d0x000d0x0000.017ee4690x0080006d0x0000.000.000000000x000000010x000000000x1390x000x0b2d0x00120x0000.017ee0760x0080006d0x0000.000.000000000x000000010x000000000x1490x000x0b2d0x00170x0000.017eec4e0x0080006d0x0000.000.000000000x000000010x000000000x1590x000x0b2c0x00160x0000.017e763f0x0080006c0x0000.000.000000000x000000010x000000000x1690x000x0b2c0x002a0x0000.017e7a310x0080006c0x0000.000.000000000x000000010x000000000x1790x000x0b2d0x001a0x0000.017ef0410x0080006d0x0000.000.000000000x000000010x000000000x1890x000x0b2d0x001c0x0000.017f07f10x0080006d0x0000.000.000000000x000000010x000000000x1990x000x0b2d0x001e0x0000.017ef8260x0080006d0x0000.000.000000000x000000010x000000000x1a90x000x0b2d0x00190x0000.017ef4330x0080006d0x0000.000.000000000x000000010x000000000x1b90x000x0b2d0x00210x0000.017f13c90x0080006d0x0000.000.000000000x000000010x000000000x1c90x000x0b2d0x00200x0000.017f0be40x0080006d0x0000.000.000000000x000000010x000000000x1d90x000x0b2c0x00030x0000.017e95d50x0080006c0x0000.000.000000000x000000010x000000000x1e90x000x0b2d0x000e0x0000.017efc190x0080006d0x0000.000.000000000x000000010x000000000x1f90x000x0b2d0x00180x0000.017f03fe0x0080006d0x0000.000.000000000x000000010x000000000x2090x000x0b2d0x001b0x0000.017f0fd70x0080006d0x0000.000.000000000x000000010x000000000x2190x000x0b2d0x00220x0000.017f17bc0x0080006d0x0000.000.000000000x000000010x000000000x2290x000x0b2d0x00070x0000.017f1baf0x0080006d0x0000.000.000000000x000000010x00000000|<---形态9为非举动事件.0x23100x800x0b2d0x00000x0000.017f73e00x0080006d0x0000.000.000000000x000000010x00000000||<---形态10位举动事件.|<---dba,包括这个举动事件的block地点|<---index就是事件表中的slot号|dba=0x0080006d我们对之举行转换,算法参考这里.|v$transaction.xidslot=35转换为16进制,恰是23|0x0080006d=00000000100000000000000001101101|dba0x0080006d位于file2,block109上0x2490x000x0b2d0xffff0x0000.017f25190x0080006d0x0000.000.000000000x000000010x000000000x2590x000x0b2c0x00150x0000.017e724c0x0080006c0x0000.000.000000000x000000010x000000000x2690x000x0b2b0x002c0x0000.017e860a0x0080006c0x0000.000.000000000x000000010x000000000x2790x000x0b2c0x002b0x0000.017ead850x0080006c0x0000.000.000000000x000000010x000000000x2890x000x0b2c0x002e0x0000.017e8def0x0080006c0x0000.000.000000000x000000010x000000000x2990x000x0b2c0x000c0x0000.017ed0ab0x0080006d0x0000.000.000000000x000000010x000000000x2a90x000x0b2c0x002f0x0000.017e7e240x0080006c0x0000.000.000000000x000000010x000000000x2b90x000x0b2c0x002d0x0000.017eb1780x0080006c0x0000.000.000000000x000000010x000000000x2c90x000x0b2c0x00280x0000.017e89fc0x0080006c0x0000.000.000000000x000000010x000000000x2d90x000x0b2c0x00060x0000.017eb56b0x0080006c0x0000.000.000000000x000000010x000000000x2e90x000x0b2c0x001d0x0000.017e91e20x0080006c0x0000.000.000000000x000000010x000000000x2f90x000x0b2c0x00260x0000.017e82170x0080006c0x0000.000.000000000x000000010x00000000altersystemdumpdatafile2block109;********************************************************************************Startdumpdatablockstsn:1file#:2minblk109maxblk109buffertsn:1rdba:0x0080006d(2/109)scn:0x0000.017f73e0seq:0x01flg:0x04tail:0x73e00201frmt:0x02chkval:0x8fbetype:0x02=KTUUNDOBLOCK********************************************************************************UNDOBLK:xid:0x0007.023.00000b2dseq:0x40dcnt:0x1cirb:0x1cicl:0x0flg:0x0000|||<---xid=Undosegmentno.Slotno.sequenceno||<---index.rollback.begin|这是回滚蛋始的中央,我们向下找到这个纪录RecOffsetRecOffsetRecOffsetRecOffsetRecOffset---------------------------------------------------------------------------0x010x1f180x020x1e480x030x1d780x040x1ca80x050x1bd80x060x1b080x070x1a380x080x19680x090x18980x0a0x17c80x0b0x16f80x0c0x16280x0d0x15580x0e0x14880x0f0x13b80x100x12e80x110x12180x120x11480x130x10780x140x0fa80x150x0ed80x160x0e080x170x0d380x180x0c680x190x0b980x1a0x0ac80x1b0x09dc0x1c0x0960*-----------------------------*Rec#0x1slt:0x0bobjn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006c.040d.26ctlmaxscn:0x0000.017e096cprvtxscn:0x0000.017e0d5eKDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.01f.00000b4buba:0x0080005d.04fa.01flg:C---lkc:0scn:0x0000.017ec07bKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:15(0xf)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600b2d5bcol1:[7]7868090d0c2a20col2:[1]80col3:[5]c4195b6364*-----------------------------*Rec#0x2slt:0x0aobjn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.01ctlmaxscn:0x0000.017e0d5eprvtxscn:0x0000.017e0dd1KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.018.00000b4buba:0x0080005d.04fa.02flg:C---lkc:0scn:0x0000.017ec46eKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:25(0x19)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600b4c3dcol1:[7]7868090d0d212ccol2:[1]80col3:[5]c4195c0a0a*-----------------------------*Rec#0x3slt:0x09objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.02ctlmaxscn:0x0000.017e0dd1prvtxscn:0x0000.017e109bKDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.009.00000b4buba:0x0080005d.04fa.03flg:C---lkc:0scn:0x0000.017ec860KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:35(0x23)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600c0722col1:[7]7868090d0e1838col2:[1]80col3:[5]c4195c1415*-----------------------------*Rec#0x4slt:0x08objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.03ctlmaxscn:0x0000.017e109bprvtxscn:0x0000.017e148eKDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.011.00000b4buba:0x0080005d.04fa.04flg:C---lkc:0scn:0x0000.017ecc53KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:45(0x2d)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600c2607col1:[7]7868090d0f1008col2:[1]80col3:[5]c4195c1e20*-----------------------------*Rec#0x5slt:0x29objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.04ctlmaxscn:0x0000.017e148eprvtxscn:0x0000.017e1880KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.01a.00000b4buba:0x0080005d.04fa.05flg:C---lkc:0scn:0x0000.017ed046KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:55(0x37)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600c4450col1:[7]7868090d100714col2:[1]80col3:[5]c4195c282b*-----------------------------*Rec#0x6slt:0x0cobjn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.05ctlmaxscn:0x0000.017e1880prvtxscn:0x0000.017e1c73KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.029.00000b4buba:0x0080005d.04fa.06flg:C---lkc:0scn:0x0000.017ed439KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:65(0x41)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600c6332col1:[7]7868090d103a21col2:[1]80col3:[5]c4195c3236*-----------------------------*Rec#0x7slt:0x11objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.06ctlmaxscn:0x0000.017e1c73prvtxscn:0x0000.017e2066KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.027.00000b4buba:0x0080005d.04fa.07flg:C---lkc:0scn:0x0000.017ed82bKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:75(0x4b)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600d1e17col1:[7]7868090d11312dcol2:[1]80col3:[5]c4195c3c41*-----------------------------*Rec#0x8slt:0x10objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.07ctlmaxscn:0x0000.017e2066prvtxscn:0x0000.017e2459KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.01d.00000b4buba:0x0080005d.04fa.08flg:C---lkc:0scn:0x0000.017edc1eKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:85(0x55)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600d3c60col1:[7]7868090d122839col2:[1]80col3:[5]c4195c464c*-----------------------------*Rec#0x9slt:0x13objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.08ctlmaxscn:0x0000.017e2459prvtxscn:0x0000.017e284bKDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.01c.00000b4buba:0x0080005d.04fa.09flg:C---lkc:0scn:0x0000.017ee011KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:95(0x5f)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600d5b45col1:[7]7868090d132009col2:[1]80col3:[5]c4195c5057*-----------------------------*Rec#0xaslt:0x12objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.09ctlmaxscn:0x0000.017e284bprvtxscn:0x0000.017e2c3eKDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.02c.00000b4buba:0x0080005d.04fa.0aflg:C---lkc:0scn:0x0000.017ee403KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:105(0x69)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600e1627col1:[7]7868090d141715col2:[1]80col3:[5]c4195c5a61*-----------------------------*Rec#0xbslt:0x0dobjn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.0actlmaxscn:0x0000.017e2c3eprvtxscn:0x0000.017e3031KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.022.00000b4buba:0x0080005d.04fa.0bflg:C---lkc:0scn:0x0000.017ee7f6KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:115(0x73)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600e350ccol1:[7]7868090d150e22col2:[1]80col3:[5]c4195d0108*-----------------------------*Rec#0xcslt:0x14objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.0bctlmaxscn:0x0000.017e3031prvtxscn:0x0000.017e3424KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.02a.00000b4buba:0x0080005d.04fa.0cflg:C---lkc:0scn:0x0000.017eebe9KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:125(0x7d)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600e5355col1:[7]7868090d16052ecol2:[1]80col3:[5]c4195d0b13*-----------------------------*Rec#0xdslt:0x17objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.0cctlmaxscn:0x0000.017e3424prvtxscn:0x0000.017e3816KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.02e.00000b4buba:0x0080005d.04fa.0dflg:C---lkc:0scn:0x0000.017eefdcKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:135(0x87)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600f0e37col1:[7]7868090d16383acol2:[1]80col3:[5]c4195d151d*-----------------------------*Rec#0xeslt:0x1aobjn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.0dctlmaxscn:0x0000.017e3816prvtxscn:0x0000.017e3c09KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.028.00000b4buba:0x0080005d.04fa.0eflg:C---lkc:0scn:0x0000.017ef3ceKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:145(0x91)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b600f2d1ccol1:[7]7868090d17300acol2:[1]80col3:[5]c4195d1f28*-----------------------------*Rec#0xfslt:0x19objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.0ectlmaxscn:0x0000.017e3c09prvtxscn:0x0000.017e3ffcKDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.02f.00000b4buba:0x0080005d.04fa.0fflg:C---lkc:0scn:0x0000.017ef7c1KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:155(0x9b)flag:0x6clock:0ckix:0ncol:4nnew:4size:-1col0:[5]c50b600f4ccol1:[7]7868090d182716col2:[1]80col3:[5]c4195d2933*-----------------------------*Rec#0x10slt:0x1eobjn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.0fctlmaxscn:0x0000.017e3ffcprvtxscn:0x0000.017e43f4KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.02b.00000b4buba:0x0080005d.04fa.10flg:C---lkc:0scn:0x0000.017efbb4KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:165(0xa5)flag:0x6clock:0ckix:0ncol:4nnew:4size:1col0:[6]c50b6010064acol1:[7]7868090e011e23col2:[1]80col3:[5]c4195d333e*-----------------------------*Rec#0x11slt:0x0eobjn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.10ctlmaxscn:0x0000.017e43f4prvtxscn:0x0000.017e4599KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.003.00000b4cuba:0x0080005d.04fa.11flg:C---lkc:0scn:0x0000.017effa7KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:175(0xaf)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b6010252ccol1:[7]7868090e02152fcol2:[1]80col3:[5]c4195d3d48*-----------------------------*Rec#0x12slt:0x1fobjn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.11ctlmaxscn:0x0000.017e4599prvtxscn:0x0000.017e46d6KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.021.00000b4buba:0x0080005d.04fa.12flg:C---lkc:0scn:0x0000.017f0399KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:185(0xb9)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b60104411col1:[7]7868090e030c3bcol2:[1]80col3:[5]c4195d4753*-----------------------------*Rec#0x13slt:0x18objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.12ctlmaxscn:0x0000.017e46d6prvtxscn:0x0000.017e4ad0KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.000.00000b4cuba:0x0080005d.04fa.13flg:C---lkc:0scn:0x0000.017f078cKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:195(0xc3)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b6010625acol1:[7]7868090e04040bcol2:[1]80col3:[5]c4195d515e*-----------------------------*Rec#0x14slt:0x1cobjn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.13ctlmaxscn:0x0000.017e4ad0prvtxscn:0x0000.017e4ec3KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.026.00000b4buba:0x0080005d.04fa.14flg:C---lkc:0scn:0x0000.017f0b7fKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:205(0xcd)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b60111d3fcol1:[7]7868090e043717col2:[1]80col3:[5]c4195d5c06*-----------------------------*Rec#0x15slt:0x20objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.14ctlmaxscn:0x0000.017e4ec3prvtxscn:0x0000.017e52b6KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.002.00000b4cuba:0x0080005d.04fa.15flg:C---lkc:0scn:0x0000.017f0f72KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:215(0xd7)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b60113c21col1:[7]7868090e052e24col2:[1]80col3:[5]c4195e0210*-----------------------------*Rec#0x16slt:0x1bobjn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.15ctlmaxscn:0x0000.017e52b6prvtxscn:0x0000.017e56a9KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.00a.00000b4buba:0x0080005d.04fa.16flg:C---lkc:0scn:0x0000.017f1364KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:225(0xe1)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b60115b06col1:[7]7868090e062530col2:[1]80col3:[5]c4195e0c1b*-----------------------------*Rec#0x17slt:0x21objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.16ctlmaxscn:0x0000.017e56a9prvtxscn:0x0000.017e5a9bKDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.006.00000b4cuba:0x0080005d.04fa.17flg:C---lkc:0scn:0x0000.017f1757KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:235(0xeb)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b6012154fcol1:[7]7868090e071c3ccol2:[1]80col3:[5]c4195e1626*-----------------------------*Rec#0x18slt:0x22objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.17ctlmaxscn:0x0000.017e5a9bprvtxscn:0x0000.017e5e8dKDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.012.00000b4buba:0x0080005d.04fa.18flg:C---lkc:0scn:0x0000.017f1b4aKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:245(0xf5)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b60123431col1:[7]7868090e08140ccol2:[1]80col3:[5]c4195e2030*-----------------------------*Rec#0x19slt:0x07objn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.18ctlmaxscn:0x0000.017e5e8dprvtxscn:0x0000.017e6281KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.005.00000b4cuba:0x0080005d.04fa.19flg:C---lkc:0scn:0x0000.017f1f3dKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8dhdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:255(0xff)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b60125316col1:[7]7868090e090b18col2:[1]80col3:[5]c4195e2a3b*-----------------------------*Rec#0x1aslt:0x0fobjn:516(0x00000204)objd:514tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.19ctlmaxscn:0x0000.017e6281prvtxscn:0x0000.017e6673KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0006.008.00000b4cuba:0x0080005d.04fa.1aflg:C---lkc:0scn:0x0000.017f232fKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x00400e8ehdba:0x00400e89itli:2ispac:0maxfr:4863tabn:1slot:7(0x7)flag:0x6clock:0ckix:0ncol:4nnew:4size:0col0:[6]c50b60130d5fcol1:[7]7868090e0a0224col2:[1]80col3:[5]c4195e3446*-----------------------------*Rec#0x1bslt:0x24objn:18(0x00000012)objd:18tblspc:0(0x00000000)*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.1actlmaxscn:0x0000.017e6673prvtxscn:0x0000.017e6a66KDOundorecord:KTBRedoop:0x04ver:0x01op:Litl:xid:0x0007.01e.00000b14uba:0x008000dd.03f2.1bflg:C---lkc:0scn:0x0000.017cfed9KDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x0040007ahdba:0x00400079itli:1ispac:0maxfr:4863tabn:0slot:33(0x21)flag:0x2clock:0ckix:0ncol:17nnew:11size:0col0:[2]c102col1:[4]c3023e4ecol6:[1]80col7:[7]7867071004290ecol8:[7]7868090f0e391acol9:[7]7867071004290ecol10:[1]80col13:[1]80col14:*NULL*col15:[1]80col16:[4]c3073824*-----------------------------*Rec#0x1cslt:0x23objn:16177(0x00003f31)objd:16177tblspc:0(0x00000000)SQL>selectobject_namefromdba_objectswhereobject_id=16177;OBJECT_NAME--------------------------------------------------------------------------------UD*Layer:11(Row)opc:1rci0x00Undotype:RegularundoBegintransLastbuffersplit:NoTempObject:NoTablespaceUndo:Nordba:0x00000000*-----------------------------uba:0x0080006d.040d.1bctlmaxscn:0x0000.017e6a66prvtxscn:0x0000.017e6e59KDOundorecord:KTBRedoop:0x03ver:0x01op:ZKDOOpcode:URProwdependenciesDisabledxtype:XAbdba:0x0040657ahdba:0x00406579itli:2ispac:0maxfr:4863tabn:0slot:1(0x1)flag:0x2clock:0ckix:80ncol:1nnew:1size:0col0:[2]c103<------这里纪录的就是前镜像c103就是2Enddumpdatablockstsn:1file#:2minblk109maxblk109

我们再来看一下数据块的变更:



SQL>selectfile_id,block_idfromdba_extentswheresegment_name=UD;FILE_IDBLOCK_ID--------------------125977SQL>altersystemdumpdatafile1blockmin25977blockmax25979;Systemaltered.检察trace内容:Startdumpdatablockstsn:0file#:1minblk25977maxblk25979buffertsn:0rdba:0x00406579(1/25977)scn:0x0000.017f2520seq:0x01flg:0x04tail:0x25201001frmt:0x02chkval:0x1d41type:0x10=DATASEGMENTHEADER-UNLIMITEDExtentControlHeader-----------------------------------------------------------------ExtentHeader::spare1:0spare2:0#extents:1#blocks:7lastmap0x00000000#maps:0offset:4128Highwater::0x0040657bext#:0blk#:1extsize:7#blocksinseg.hdrsfreelists:1#blocksbelow:1mapblk0x00000000offset:0UnlockedMapHeader::next0x00000000#extents:1obj#:16177flag:0x40000000ExtentMap-----------------------------------------------------------------0x0040657alength:7nfl=1,nfb=1typ=1nxf=0ccnt=1SEGLST::flg:USEDlhd:0x0040657altl:0x0040657abuffertsn:0rdba:0x0040657a(1/25978)scn:0x0000.017f73e0seq:0x01flg:0x04tail:0x73e00601frmt:0x02chkval:0x6763type:0x06=transdataBlockheaderdump:0x0040657aObjectidonBlock?Yseg/obj:0x3f31csc:0x00.17f73dcitc:2flg:Otyp:1-DATAfsl:0fnx:0x0ver:0x01ItlXidUbaFlagLckScn/Fsc0x010x0005.018.00000b530x0080004d.04f6.1dC---0scn0x0000.017f25210x020x0007.023.00000b2d0x0080006d.040d.1c----1fsc0x0000.00000000ITL事件槽--InterestedTransactionList(ITL)ITL内容包含:xid---TransactionIDUba---UndoBlockAddressLck---LockStatusxid=Undo.Segment.Number+Transaction.Table.Slot.Number+Wrap我们看到itl2上存在举动事件.xid=0x0007.023.00000b2d指向7号回滚段.Slot号为23,Wrap#为b2d,恰是我们dump回滚段看到的谁人事件.uba=Address.Of.Last.Undo.Block.Used+Sequence+Last.Entry.in.UNDO.Record.Mapuba=0x0080006d.040d.1cdata_block_dump,dataheaderat0xadb505c===============tsiz:0x1fa0hsiz:0x16pbl:0x0adb505cbdba:0x0040657a76543210flag=--------ntab=1nrow=2frre=-1fsbo=0x16f搜索引擎优化=0x1f94avsp=0x1f78tosp=0x1f780xe:pti[0]nrow=2offs=00x12:pri[0]offs=0x1f9a0x14:pri[1]offs=0x1f94block_row_dump:tab0,row0,@0x1f9atl:6fb:--H-FL--lb:0x0cc:1col0:[2]c102tab0,row1,@0x1f94tl:6fb:--H-FL--lb:0x2cc:1col0:[2]c20bend_of_block_dumpbuffertsn:0rdba:0x0000657b(0/25979)scn:0x0000.00000000seq:0x01flg:0x05tail:0x00000001frmt:0x02chkval:0x627btype:0x00=unknownHexdumpofcorruptheader4=CORRUPTDumpofmemoryfrom0x0ADB5000to0x0ADB5014ADB5000000002000000657B0000000005010000[....{e..........]ADB50100000627B[{b..]Enddumpdatablockstsn:0file#:1minblk25977maxblk25979

如今我们来总结一下事件的流程:
1.分派一个回滚段
2.在回滚段事件表平分配一个事件槽
3.分派undoblock
4.更新数据块上的ITL事件槽
5.把前镜像纪录在undoblock内
6.变动数据块的内容

因为DelayedBlockCleanout的存在,Oracle在读一个block时,假如ITL事件槽存在举动事件,那末Oracle必需依据响应的xid找到
响应的回滚段以判别事件形态.
这能找出所有错误的99.99%。它不能找出的是仅仅涉及数据文件的损坏(这很不常见)。如果你想要检查一张表，你通常应该没有选项地运行myisamchk或用-s或--silent选项的任何一个。

总感觉自己还是不会SQL

我是新手，正在学习数据库和操作系统，深感理论的泛广，唯有一步一步来，但是又感觉时间不够，收集了很多资料却总是没能认真的看完，希望有一个讨论板块，大家共同解决，共同分享，共同努力

发几份SQL课件，以飨阅者

是要和操作系统进行Socket通讯的场景。否则建议慎重！

理解了存储结构，再阅读下性能优化的章节基本上会对sqlserver有个清晰地认识

但是随着数据量的增大，这种成本差距会逐渐减小，趋于相等。（500万数量级只相差10%左右）

我们学到了什么？思考问题的时候从表的角度来思考问

SQLServer的异构移植功能个人感觉最好了。（如果对比过SQLServer的链接服务器和Oracle的透明网关的朋友会发现SQLServer的sp_addlinkedserver(openquery)异构数据库系列比Oracle真是强太多了。）