一、Undo表空间和回滚段
１、Undo段的主要作用
（１）事务回滚
（２）事务恢复(实例恢复，利用回滚来恢复未提交的数据)
（３）读一致性(构造CR)
（４）闪回查询
２、查与undo相关的参数：
sys@OCM> show parameter undo
NAME TYPE VALUE
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
从上面可以看出，UNDOTBS1就是当前使用的undo表空间。
３、Undo段:段头、回滚块
gyj@OCM> select * from v$rollname;
USN NAME 0 SYSTEM
1 _SYSSMU1_592353410$
2 _SYSSMU2_967517682$
3 _SYSSMU3_1204390606$
4 _SYSSMU4_1003442803$
5 _SYSSMU5_538557934$
6 _SYSSMU6_2897970769$
7 _SYSSMU7_3517345427$
8 _SYSSMU8_3901294357$
9 _SYSSMU9_1735643689$
10 _SYSSMU10_4131489474$
gyj@OCM>select blocks,extents from dba_segments wheresegment_name='_SYSSMU10_4131489474$';
BLOCKS EXTENTS
400 5
gyj@OCM>select tablespace_name,extent_id,file_id,block_id,blocks from dba_extents whereSEGMENT_NAME='_SYSSMU10_4131489474$';
TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
UNDOTBS1 0 3 272 8
UNDOTBS1 1 3 384 8
UNDOTBS1 2 3 2816 128
UNDOTBS1 3 3 512 128
UNDOTBS1 4 3 2304 128
gyj@OCM> select owner, segment_name, tablespace_name from dba_rollback_segs;
OWNER SEGMENT_NAME TABLESPACE_NAME
SYS SYSTEM SYSTEM
PUBLIC _SYSSMU10_4131489474$ UNDOTBS1
PUBLIC _SYSSMU9_1735643689$ UNDOTBS1
PUBLIC _SYSSMU8_3901294357$ UNDOTBS1
PUBLIC _SYSSMU7_3517345427$ UNDOTBS1
PUBLIC _SYSSMU6_2897970769$ UNDOTBS1
PUBLIC _SYSSMU5_538557934$ UNDOTBS1
PUBLIC _SYSSMU4_1003442803$ UNDOTBS1
PUBLIC _SYSSMU3_1204390606$ UNDOTBS1
PUBLIC _SYSSMU2_967517682$ UNDOTBS1
PUBLIC _SYSSMU1_592353410$ UNDOTBS1
４、Undo段中区的状态
（１）active
（２）unexpired
（３）expired
（４）free
gyj@OCM> select * from t1;
ID NAME 1 AAAAA
2 BBBBB
3 CCCCC
4 DDDDD
5 EEEEE
gyj@OCM> update t1 set name='eeeee'where id=5;
1 row updated.
这时先不要提交，然后通过活动的事务查到正在使用的回滚段。
sys@OCM> selectXIDUSN,XIDSLOT,XIDSQN,UBAFIL,UBABLK,UBASQN,UBAREC,STATUS from v$transaction;
XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS
---------- ---------- ---------- ---------- ---------- ---------- -------- ----------
8 18 4308 3 2086 316 2 ACTIVE
sys@OCM> select * from v$rollname whereusn=8;
USN NAME 8 _SYSSMU8_3901294357$
sys@OCM>select extent_id, bytes, status from dba_undo_extents wheresegment_name='_SYSSMU8_3901294357$';
EXTENT_ID BYTES STATUS
0 65536 EXPIRED
1 65536 EXPIRED
2 1048576 ACTIVE
gyj@OCM> update t1 set name='eeeee'where id=5;
1 row updated.
gyj@OCM> commit;
Commit complete.
提交操作，使2号区状态由ACTIVE变为UNEXPIRED
sys@OCM> select extent_id, bytes, statusfrom dba_undo_extents where segment_name='_SYSSMU8_3901294357$';
EXTENT_ID BYTES STATUS
0 65536 EXPIRED
1 65536 EXPIRED
2 1048576 UNEXPIRED
二、undo与事务
１、事务ID
XID的组成：XIDUSN 、XIDSLOT 、XIDSQN
gyj@OCM> select * from t1;
ID NAME
1 AAAAA
2 BBBBB
3 CCCCC
4 DDDDD
5 eeeee
gyj@OCM> update t1 set name='FFFFF'where id=5;
1 row updated.
sys@OCM>select xid,xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,status fromv$transaction;
XID XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS
06001500F20F0000 6 21 4082 3 1901 291 6 ACTIVE
２、事务表
undo段的第一个数据块，回滚段头块存放事务信息
sys@OCM>select * from v$rollname where usn=6;
USN NAME 6 _SYSSMU6_2897970769$
dump回滚段头:
sys@OCM> alter system dump undo header'_SYSSMU6_2897970769$';
System altered.
Dump出的内容，显示事务表信息，如下：
index state cflags wrap# uel scn dba parent-xid nub stmt_num cmt
0x00 9 0x00 0x0ff4 0x0016 0x0000.00271cd6 0x00c0076d 0x0000.000.00000000 0x00000001 0x00000000 1 362666670
0x01 9 0x00 0x0ff4 0x000c 0x0000.00271a9e 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x02 9 0x00 0x0ff3 0x0020 0x0000.002719cf 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x03 9 0x00 0x0ff3 0x000b 0x0000.002719f0 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x04 9 0x00 0x0ff3 0x0014 0x0000.00271a09 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x05 9 0x00 0x0ff4 0x0010 0x0000.00271c08 0x00c0076c 0x0000.000.00000000 0x00000001 0x00000000 1 362666292
0x06 9 0x00 0x0ff3 0x001c 0x0000.00271b10 0x00c0076c 0x0000.000.00000000 0x00000001 0x00000000 1 362665726
0x07 9 0x00 0x0ff3 0x000f 0x0000.002719bb 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x08 9 0x00 0x0ff4 0x001d 0x0000.00271a4b 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x09 9 0x00 0x0ff3 0x0007 0x0000.002719b0 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x0a 9 0x00 0x0ff3 0x001f 0x0000.00271a6d 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x0b 9 0x00 0x0ff3 0x0011 0x0000.002719f5 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x0c 9 0x00 0x0ff3 0x001a 0x0000.00271aac 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x0d 9 0x00 0x0ff3 0x0000 0x0000.00271c49 0x00c0076c 0x0000.000.00000000 0x00000001 0x00000000 1 362666328
0x0e 9 0x00 0x0ff3 0x0009 0x0000.00271997 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665601
0x0f 9 0x00 0x0ff3 0x0002 0x0000.002719c4 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x10 9 0x00 0x0ff4 0x000d 0x0000.00271c32 0x00c0076c 0x0000.000.00000000 0x00000001 0x00000000 1 362666292
0x11 9 0x00 0x0ff3 0x0004 0x0000.00271a02 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x12 9 0x00 0x0ff3 0x001e 0x0000.00271a26 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x13 9 0x00 0x0ff2 0x0005 0x0000.00271be2 0x00c0076c 0x0000.000.00000000 0x00000001 0x00000000 1 362666229
0x14 9 0x00 0x0ff2 0x0012 0x0000.00271a11 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x15 10 0x80 0x0ff2 0x0002 0x0000.0026e970 0x00c0076d 0x0000.000.00000000 0x00000001 0x00000000 0
0x16 9 0x00 0x0ff4 0x001b 0x0000.00271ce4 0x00c0076d 0x0000.000.00000000 0x00000001 0x00000000 1 362666674
0x17 9 0x00 0x0ff3 0x0021 0x0000.00271a7e 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x18 9 0x00 0x0ff3 0x000a 0x0000.00271a59 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x19 9 0x00 0x0ff2 0x0008 0x0000.00271a3f 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x1a 9 0x00 0x0ff3 0x0006 0x0000.00271af5 0x00c0076c 0x0000.000.00000000 0x00000002 0x00000000 1 362665663
0x1b 9 0x00 0x0ff2 0xffff 0x0000.00271d1a 0x00c0076d 0x0000.000.00000000 0x00000001 0x00000000 1 362666795
0x1c 9 0x00 0x0ff4 0x0013 0x0000.00271b90 0x00c0076c 0x0000.000.00000000 0x00000001 0x00000000 1 362666040
0x1d 9 0x00 0x0ff2 0x0018 0x0000.00271a51 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x1e 9 0x00 0x0ff3 0x0019 0x0000.00271a2f 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x1f 9 0x00 0x0ff3 0x0017 0x0000.00271a72 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
0x21 9 0x00 0x0ff3 0x0001 0x0000.00271a8e 0x00c0076b 0x0000.000.00000000 0x00000001 0x00000000 1 362665649
分析以上红色字体的部分。。。。
３、Undo回滚块
sys@OCM> select extent_id,file_id,block_id,blocks from dba_undo_extents wheresegment_name='_SYSSMU6_2897970769$';
EXTENT_ID FILE_ID BLOCK_ID BLOCKS
0 3 208 8
1 3 368 8
2 3 1792 128
sys@OCM> selectxid,xidusn,xidslot,xidsqn,ubafil,ubablk,ubasqn,ubarec,status fromv$transaction;
XID XIDUSN XIDSLOT XIDSQN UBAFIL UBABLK UBASQN UBAREC STATUS
06001500F20F0000 6 21 4082 3 1901 291 6 ACTIVE
dump回滚块:
alter system dump datafile 3 block 1901;
dump内容的信息如下：
UNDO BLK:
xid: 0x0006.015.00000ff2 seq: 0x123 cnt: 0x6 irb: 0x6 icl: 0x0 flg: 0x0000
Rec Offset Rec Offset Rec Offset Rec Offset Rec Offset
0x01 0x1f00 0x02 0x1e98 0x03 0x1e40 0x04 0x1d74 0x05 0x1cd8
0x06 0x1c1c
*-----------------------------
- Rec #0x1 slt: 0x00 objn: 287(0x0000011f) objd: 287 tblspc: 0(0x00000000)
- Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c0076c.0123.3b ctl max scn: 0x0000.0027186d prv tx scn: 0x0000.0027188a
txn start scn: scn: 0x0000.00271cd5 logon user: 0
prev brb: 12584810 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0007.00e.00000c11 uba: 0x00c008e2.012d.30
flg: C--- lkc: 0 scn: 0x0000.00271cbb
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x004007d9 hdba: 0x004007d8
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0
ncol: 19 nnew: 6 size: 7
col 4: [ 7] 78 71 03 07 17 1f 09
col 5: [ 7] 78 71 03 07 17 20 0b
col 6: [ 7] 78 71 03 07 17 20 09
col 7: [21]
bf 31 32 36 47 26 04 47 26 04 47 26 04 47 26 04 47 26 04 47 11
col 9: [ 1] 80
col 10: [ 1] 80
*-----------------------------
- Rec #0x2 slt: 0x00 objn: 289(0x00000121) objd: 289 tblspc: 0(0x00000000)
- Layer: 10 (Index) opc: 22 rci 0x01
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0007.00e.00000c11 uba: 0x00c008e2.012d.32
flg: C--- lkc: 0 scn: 0x0000.00271cbb
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=0 indexid=0x4007e8 block=0x004007e9
(kdxlre): restore leaf row (clear leaf delete flags)
key :(15): 07 78 71 03 07 17 20 09 06 00 40 07 d9 00 02
*-----------------------------
- Rec #0x3 slt: 0x00 objn: 289(0x00000121) objd: 289 tblspc: 0(0x00000000)
- Layer: 10 (Index) opc: 22 rci 0x02
Undo type: Regular undo Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000
*-----------------------------
index undo for leaf key operations
KTB Redo
op: 0x02 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: C uba: 0x00c0076d.0123.02
Dump kdilk : itl=2, kdxlkflg=0x1 sdc=1353654820 indexid=0x4007e8 block=0x004007e9
(kdxlpu): purge leaf row
key :(15): 07 78 71 03 07 17 21 0b 06 00 40 07 d9 00 02
*-----------------------------
- Rec #0x4 slt: 0x16 objn: 458(0x000001ca) objd: 458 tblspc: 0(0x00000000)
- Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c0076d.0123.01 ctl max scn: 0x0000.0027188a prv tx scn: 0x0000.002718b1
txn start scn: scn: 0x0000.00271ce3 logon user: 0
prev brb: 12584810 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0001.00e.00000bf7 uba: 0x00c02aff.00cb.17
flg: C--- lkc: 0 scn: 0x0000.00271ce2
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x00400c3b hdba: 0x00400c38
itli: 1 ispac: 0 maxfr: 4863
tabn: 0 slot: 81(0x51) flag: 0x2c lock: 0 ckix: 69
ncol: 7 nnew: 6 size: -1
col 1: [ 1] 80
col 2: [ 2] c1 28
col 3: [ 1] 80
col 4: [ 7] 78 71 03 07 17 0f 0a
col 5: [ 1] 80
col 6: [ 1] 80
*-----------------------------
- Rec #0x5 slt: 0x1b objn: 287(0x0000011f) objd: 287 tblspc: 0(0x00000000)
- Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c0076d.0123.04 ctl max scn: 0x0000.002718b1 prv tx scn: 0x0000.00271912
txn start scn: scn: 0x0000.00271d17 logon user: 0
prev brb: 12584811 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0004.00d.00000c08 uba: 0x00c0015c.00b1.01
flg: C--- lkc: 0 scn: 0x0000.00271d13
KDO Op code: URP row dependencies Disabled
xtype: XA flags: 0x00000000 bdba: 0x004007d9 hdba: 0x004007d8
itli: 2 ispac: 0 maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 191
ncol: 19 nnew: 1 size: -7
col 5: NULL
*-----------------------------
- Rec #0x6 slt: 0x15 objn: 74949(0x000124c5) objd: 74949 tblspc: 7(0x00000007)
- Layer: 11 (Row) opc: 1 rci 0x00
Undo type: Regular undo Begin trans Last buffer split: No
Temp Object: No
Tablespace Undo: No
rdba: 0x00000000Ext idx: 0
flg2: 0
*-----------------------------
uba: 0x00c0076d.0123.05 ctl max scn: 0x0000.00271912 prv tx scn: 0x0000.00271946
txn start scn: scn: 0x0000.0026e8e7 logon user: 91
prev brb: 12584811 prev bcl: 0
KDO undo record:
KTB Redo
op: 0x04 ver: 0x01
compat bit: 4 (post-11) padding: 1
op: L itl: xid: 0x0007.000.00000c09 uba: 0x00c00361.012a.18
flg: C--- lkc: 0 scn: 0x0000.0026e89a
Array Update of 1 rows:
tabn: 0 slot: 4(0x4) flag: 0x2c lock: 0 ckix: 191
ncol: 2 nnew: 1 size: 0
KDO Op code: 21 row dependencies Disabled
xtype: XAxtype KDO_KDOM2 flags: 0x00000080 bdba: 0x01802ba7 hdba: 0x01802b83
itli: 1 ispac: 0 maxfr: 4858
vect = 3
col 1: [ 5] 65 65 65 65 65
End dump data blocks tsn: 2 file#: 3 minblk 1901 maxblk 1901
对上面的红色字体进行分析。。。
４、事务槽
sys@OCM>selectdbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),id,namefrom gyj.t1;
DBMS_ROWID.ROWID_RELATIVE_FNO(ROWID)DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID) ID NAME
6 11175 1 AAAAA
6 11175 2 BBBBB
6 11175 3 CCCCC
6 11175 4 DDDDD
6 11175 5 eeeee
dump数据块（id=5这行记录所在的数据块）
sys@OCM> alter system dump datafile6 block 11175;
System altered.
Dump的主要内容如下：
seg/obj: 0x124c5 csc: 0x00.271d3b itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1802b80 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc0x01 0x0006.015.00000ff2 0x00c0076d.0123.06 ---- 1 fsc 0x0000.00000000
0x02 0x0008.012.000010d4 0x00c00826.013c.02 C--- 0 scn 0x0000.0026e970
bdba: 0x01802ba7
data_block_dump,data header at 0xa64664
tsiz: 0x1f98
hsiz: 0x1c
pbl: 0x00a64664
76543210flag=--------
ntab=1
nrow=5
frre=-1
fsbo=0x1c
fseo=0x1f5c
avsp=0x1f40
tosp=0x1f40
0xe:pti[0] nrow=5 offs=0
0x12:pri[0] offs=0x1f8c 0x14:pri[1] offs=0x1f80
0x16:pri[2] offs=0x1f74 0x18:pri[3] offs=0x1f68
0x1a:pri[4] offs=0x1f5c
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 02 col 1: [ 5] 41 41 41 41 41
tab 0, row 1, @0x1f80
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 03 col 1: [ 5] 42 42 42 42 42
tab 0, row 2, @0x1f74
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 04 col 1: [ 5] 43 43 43 43 43
tab 0, row 3, @0x1f68
tl: 12 fb: --H-FL-- lb: 0x0 cc: 2
col 0: [ 2] c1 05 col 1: [ 5] 44 44 44 44 44
tab 0, row 4, @0x1f5c
tl: 12 fb: --H-FL-- lb: 0x1 cc: 2
col 0: [ 2] c1 06 col 1: [ 5] 46 46 46 46 46
end_of_block_dump End dump data blocks tsn: 7 file#: 6 minblk 11175 maxblk 11175
分析以下红色字体部分。。。。
三、延迟提交
oracle为了提高提交速度．而不去清除事务留在块中的行锁之类的事务信息，就是延迟提交。当你下次访问到被延迟提交的块时，才会去真正的清除事务信息．这叫块清除。
(1)当oracle 修改的块超过一定数量时，事务提交依次清楚每个块的事务信息将减缓提交速度，这时就会做延迟提交。
(2)事务所修改的数据块已经被写进了数据文件，这时事务提交如果把数据块从数据文件再重新读出，清除事务信息，将又是一个物理 I/O，为了提高提交速度 此时也会做延迟提交。