郭一军，网名guoyJoe，尖峰在线培训创始人、Oracle OCM、ITPUB论坛Oracle认证版版主、2013最佳精华贴、Senior DBA、Oracle OCT、 ACOUG MEMBER、CSDN 认证专家、RHCE、在线技术分享的倡导者和实践者。 http://www.jianfengedu.com http://jianfengedu.taobao.com
全部博文（72）
2014年（72）
分类： Oracle
2014-06-19 10:34:18
一丶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 0x20 9 0x00 0x0ff2 0x0003 0x0000.002719d6 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/Fsc 0x01 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 76543210 flag=-------- 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为了提高提交速度．而不去清除事务留在块中的行锁之类的事务信息，就是延迟提交。当你下次访问到被延迟提交的块时，才会去真正的清除事务信息．这叫块清除。
１丶当oracle 修改的块超过一定数量时，事务提交依次清楚每个块的事务信息将减缓提交速度，这时就会做延迟提交。
２丶事务所修改的数据块已经被写进了数据文件，这时事务提交如果把数据块从数据文件再重新读出，清除事务信息，将又是一个物理 I/O，为了提高提交速度 此时也会做延迟提交。
四丶读一致性
１丶什么是读一致性
会话一：
gyj@OCM> execdbms_stats.gather_table_stats(user,\'T1\');
PL/SQL procedure successfully completed.
gyj@OCM> alter session set isolation_level=serializable;
Session altered.
gyj@OCM> select * from t1 where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 9 | 18 (0)| 00:00:01 |
|* 1| TABLE ACCESS FULL| T1 | 1 | 9 | 18 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter("ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
22 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net toclient
416 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
取第二次的执行计划！！！！！
再开一个会话，会话二：
gyj@OCM> begin
2 for i in 1 .. 1000
3 loop
4 update t1 set name=\'GGGGG\'where id=1;
5 commit;
6 end loop;
7 end;
8 /
PL/SQL procedure successfully completed.
再回到会话一，看一致性读信息：
gyj@OCM> select * from t1 where id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 1 | 9 | 18 (0)| 00:00:01 |
|* 1| TABLE ACCESS FULL| T1 | 1 | 9 | 18 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified byoperation id):
---------------------------------------------------
1- filter("ID"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1017 consistent gets
0 physical reads
0 redo size
481 bytes sent via SQL*Net toclient
416 bytes received via SQL*Netfrom client
2 SQL*Net roundtrips to/fromclient
0 sorts (memory)
0 sorts (disk)
1 rows processed
2
一致性读从22变成了1017
２丶构造CR块
步1:在会话一中发布如下声明:
gyj@OCM> create table t4 (id int,namevarchar2(10));
gyj@OCM> insert into t4values(1,\'AAAAA\');
1 row created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> var x refcursor
gyj@OCM> exec open :x for select * fromt4;
PL/SQL procedure successfully completed.
步2:在会话二删除e表的所有记录且提交
gyj@OCM> delete t4;
1 row deleted.
gyj@OCM> commit;
Commit complete.
步3:回到会话一中输出游标X的所有行:
gyj@OCM> print x;
ID NAME
---------- ----------
1 AAAAA
结果还在！！！！
３丶ORA-01555错误
gyj@OCM> create undo tablespace undotbs2 datafile\'/u01/app/oracle/oradata/ocm/undotbs2.dbf\' size 10M;
Tablespace created.
gyj@OCM> alter system set undo_tablespace=undotbs2;
System altered.
gyj@OCM> alter system set undo_retention=2 scope=both;
System altered.
第1步丶session1: 目标是让b表报快照过旧的报错
gyj@OCM> conn gyj/gyj
Connected.
gyj@OCM> create table a (id int,cc varchar2(10));
Table created.
gyj@OCM> insert into a values(1,\'hello\');
1 row created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> create table b(id int,cc varchar2(10));
Table created.
gyj@OCM> insert into b values(10,\'AAAAAA\');
1 row created.
gyj@OCM> commit;
Commit complete.
gyj@OCM> select * from a;
ID CC
---------- ----------
1 hello
gyj@OCM> select * from b;
ID CC
---------- ----------
10 AAAAAA
gyj@OCM> var x refcursor;
gyj@OCM> exec open :x for select * fromb;
PL/SQL procedure successfully completed.
第2步丶session2:修改b表,字段cc前镜像"OK"保存在UDNO段中
gyj@OCM> update b set cc=\'BBBBBB\' where id= 10;
1 row updated.
gyj@OCM> commit;
Commit complete.
第3步丶session 3:该条语句就是刷新缓存
sys@OCM> alter system flush buffer_cache;
System altered.
第4步丶再开3－5个会话:在A表上行大的事务,多运行几次以确保，回滚段被覆盖
gyj@OCM> begin
2 for i in 1..20000 loop
3 update a set cc=\'HELLOWWWW\';
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
第5步丶session 1:在B表上执行查询(第一步的查询)
gyj@OCM> print :x
ERROR:
ORA-01555: snapshot too old: rollbacksegment number 21 with name "_SYSSMU21$" too small
五丶UNDO表空间的大小估算以及创建
１丶估算UNDO的大小
gyj@OCM> show parameter undo
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 2
undo_tablespace string UNDOTBS2
gyj@OCM> show parameter db_block_size
NAME TYPE VALUE
----------------------------------------------- ------------------------------
db_block_size integer 8192
gyj@OCM> show parameter undo_retention
NAME TYPE VALUE
----------------------------------------------- ------------------------------
undo_retention integer 2
gyj@OCM> select max(undoblks/((end_time - begin_time)*24*3600)) from v$undostat;
MAX(UNDOBLKS/((END_TIME-BEGIN_TIME)*24*3600))
---------------------------------------------
2.41833333
２丶创建UNDO表空间
gyj@OCM> create undo tablespace undotbs3datafile \'/u01/app/oracle/oradata/ocm/undotbs3.dbf\' size 100M;
Tablespace created.
３丶切换UNDO表空间
gyj@OCM> alter system setundo_tablespace=undotbs3;
System altered.
gyj@OCM> alter tablespace undotbs3retention guarantee;
Tablespace altered.
gyj@OCM> alter tablespace undotbs3retention noguarantee;
Tablespace altered.
gyj@OCM> select * from v$rollstat;
USN LATCH EXTENTS RSSIZE WRITES XACTS GETS WAITS OPTSIZE HWMSIZE SHRINKS WRAPS EXTENDS AVESHRINK AVEACTIVE STATUS CUREXT CURBLK
---------- ---------- ---------- -------------------- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ---------- ---------- --------------- ---------- ----------
0 0 6 385024 54380 0 449 0 385024 0 1 0 0 6553 ONLINE 0 5
21 0 2 122880 876372 0 1223 0 122880 0 0 0 0 0 ONLINE 0 0
22 1 2 122880 2685414 0 1839 0 122880 0 0 0 0 0 ONLINE 0 0
23 2 2 122880 2006864 0 1979 0 122880 0 0 0 0 0 ONLINE 0 1
24 0 2 122880 1242336 0 1317 0 122880 0 0 0 0 0 ONLINE 0 0
25 1 2 122880 1171432 0 1743 0 122880 0 0 0 0 0 ONLINE 0 0
26 2 2 122880 955404 0 1353 0 122880 0 0 0 0 0 ONLINE 0 1
27 0 2 122880 1176496 0 1318 0 122880 0 0 0 0 0 ONLINE 0 0
28 1 2 122880 1788000 0 1884 0 122880 0 0 0 0 0 ONLINE 0 0
29 2 2 122880 1854010 0 1867 0 122880 0 0 0 0 0 ONLINE 0 1
30 0 2 122880 1762272 0 1409 0 122880 0 0 0 0 0 ONLINE 0 1
11 rows selected.
**********本博客所有内容均为原创，如有转载请注明作者和出处!!!**********
Name: guoyJoe
QQ: 252803295
Email: oracledba_cn@hotmail.com
Blog: http://blog.csdn.net/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM:
_____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!
答案在：http://blog.csdn.net/guoyjoe/article/details/8624392
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036
