UNDO管理
一、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_IDBLOCK_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;
XIDUSNXIDSLOT 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出的内容，显示事务表信息，如下：
indexstate cflagswrap# uel scn dba parent-xid nub stmt_num cmt
------------------------------------------------------------------------------------------------
0x00 9 0x000x0ff40x00160x0000.00271cd60x00c0076d0x0000.000.000000000x00000001 0x000000001
362666670
0x01 9 0x000x0ff40x000c0x0000.00271a9e0x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x02 9 0x000x0ff30x00200x0000.002719cf0x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x03 9 0x000x0ff30x000b0x0000.002719f00x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x04 9 0x000x0ff30x00140x0000.00271a090x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x05 9 0x000x0ff40x00100x0000.00271c080x00c0076c0x0000.000.000000000x00000001 0x000000001
362666292
0x06 9 0x000x0ff30x001c0x0000.00271b100x00c0076c0x0000.000.000000000x00000001 0x000000001
362665726
0x07 9 0x000x0ff30x000f0x0000.002719bb0x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x08 9 0x000x0ff40x001d0x0000.00271a4b0x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x09 9 0x000x0ff30x00070x0000.002719b00x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x0a 9 0x000x0ff30x001f0x0000.00271a6d0x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x0b 9 0x000x0ff30x00110x0000.002719f50x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x0c 9 0x000x0ff30x001a0x0000.00271aac0x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x0d 9 0x000x0ff30x00000x0000.00271c490x00c0076c0x0000.000.000000000x00000001 0x000000001
362666328
0x0e 9 0x000x0ff30x00090x0000.002719970x00c0076b0x0000.000.000000000x00000001 0x000000001
362665601
0x0f 9 0x000x0ff30x00020x0000.002719c40x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x10 9 0x000x0ff40x000d0x0000.00271c320x00c0076c0x0000.000.000000000x00000001 0x000000001
362666292
0x11 9 0x000x0ff30x00040x0000.00271a020x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x12 9 0x000x0ff30x001e0x0000.00271a260x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x13 9 0x000x0ff20x00050x0000.00271be20x00c0076c0x0000.000.000000000x00000001 0x000000001
362666229
0x14 9 0x000x0ff20x00120x0000.00271a110x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x15 10 0x800x0ff20x00020x0000.0026e9700x00c0076d0x0000.000.000000000x00000001 0x000000000
0x16 9 0x000x0ff40x001b0x0000.00271ce40x00c0076d0x0000.000.000000000x00000001 0x000000001
362666674
0x17 9 0x000x0ff30x00210x0000.00271a7e0x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x18 9 0x000x0ff30x000a0x0000.00271a590x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x19 9 0x000x0ff20x00080x0000.00271a3f0x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x1a 9 0x000x0ff30x00060x0000.00271af50x00c0076c0x0000.000.000000000x00000002 0x000000001
362665663
0x1b 9 0x000x0ff20xffff0x0000.00271d1a0x00c0076d0x0000.000.000000000x00000001 0x000000001
362666795
0x1c 9 0x000x0ff40x00130x0000.00271b900x00c0076c0x0000.000.000000000x00000001 0x000000001
362666040
0x1d 9 0x000x0ff20x00180x0000.00271a510x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x1e 9 0x000x0ff30x00190x0000.00271a2f0x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x1f 9 0x000x0ff30x00170x0000.00271a720x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x20 9 0x000x0ff20x00030x0000.002719d60x00c0076b0x0000.000.000000000x00000001 0x000000001
362665649
0x21 9 0x000x0ff30x00010x0000.00271a8e0x00c0076b0x0000.000.000000000x00000001 0x000000001
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.00000ff2seq: 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 #0x1slt: 0x00objn: 287(0x0000011f)objd: 287tblspc: 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: 0x04ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Litl: 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: 0x00000000bdba: 0x004007d9hdba: 0x004007d8
itli: 1ispac: 0maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 0
ncol: 19 nnew: 6 size: 7
col4: [ 7]78 71 03 07 17 1f 09
col5: [ 7]78 71 03 07 17 20 0b
col6: [ 7]78 71 03 07 17 20 09
col7:
bf 31 32 36 47 26 04 47 26 04 47 26 04 47 26 04 47 26 04 47 11
col9: [ 1]80
col 10: [ 1]80
*-----------------------------
* Rec #0x2slt: 0x00objn: 289(0x00000121)objd: 289tblspc: 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: 0x04ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Litl: 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 #0x3slt: 0x00objn: 289(0x00000121)objd: 289tblspc: 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: 0x02ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Cuba: 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 #0x4slt: 0x16objn: 458(0x000001ca)objd: 458tblspc: 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: 0x04ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Litl: 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: 0x00000000bdba: 0x00400c3bhdba: 0x00400c38
itli: 1ispac: 0maxfr: 4863
tabn: 0 slot: 81(0x51) flag: 0x2c lock: 0 ckix: 69
ncol: 7 nnew: 6 size: -1
col1: [ 1]80
col2: [ 2]c1 28
col3: [ 1]80
col4: [ 7]78 71 03 07 17 0f 0a
col5: [ 1]80
col6: [ 1]80
*-----------------------------
* Rec #0x5slt: 0x1bobjn: 287(0x0000011f)objd: 287tblspc: 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: 0x04ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Litl: 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: 0x00000000bdba: 0x004007d9hdba: 0x004007d8
itli: 2ispac: 0maxfr: 4863
tabn: 0 slot: 2(0x2) flag: 0x2c lock: 0 ckix: 191
ncol: 19 nnew: 1 size: -7
col5: *NULL*
*-----------------------------
* Rec #0x6slt: 0x15objn: 74949(0x000124c5)objd: 74949tblspc: 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: 0x04ver: 0x01
compat bit: 4 (post-11) padding: 1
op: Litl: 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: 0x00000080bdba: 0x01802ba7hdba: 0x01802b83
itli: 1ispac: 0maxfr: 4858
vect = 3
col1: [ 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 datafile6block 11175;
System altered.
Dump的主要内容如下：
seg/obj: 0x124c5csc: 0x00.271d3bitc: 2flg: Etyp: 1 - DATA
brn: 0bdba: 0x1802b80 ver: 0x01 opc: 0
inc: 0exflg: 0
Itl Xid Uba FlagLck Scn/Fsc
0x01 0x0006.015.00000ff20x00c0076d.0123.06---- 1fsc 0x0000.00000000
0x02 0x0008.012.000010d40x00c00826.013c.02C--- 0scn 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 nrow=5offs=0
0x12:pri offs=0x1f8c
0x14:pri offs=0x1f80
0x16:pri offs=0x1f74
0x18:pri offs=0x1f68
0x1a:pri offs=0x1f5c
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 12 fb: --H-FL-- lb: 0x0cc: 2
col0: [ 2]c1 02
col1: [ 5]41 41 41 41 41
tab 0, row 1, @0x1f80
tl: 12 fb: --H-FL-- lb: 0x0cc: 2
col0: [ 2]c1 03
col1: [ 5]42 42 42 42 42
tab 0, row 2, @0x1f74
tl: 12 fb: --H-FL-- lb: 0x0cc: 2
col0: [ 2]c1 04
col1: [ 5]43 43 43 43 43
tab 0, row 3, @0x1f68
tl: 12 fb: --H-FL-- lb: 0x0cc: 2
col0: [ 2]c1 05
col1: [ 5]44 44 44 44 44
tab 0, row 4, @0x1f5c
tl: 12 fb: --H-FL-- lb: 0x1cc: 2
col0: [ 2]c1 06
col1: [ 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
----------------------------------------------------------
0recursive calls
0db block gets
22consistent gets
0physical reads
0redo size
481bytes sent via SQL*Net toclient
416bytes received via SQL*Netfrom client
2SQL*Net roundtrips to/fromclient
0sorts (memory)
0sorts (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
----------------------------------------------------------
0recursive calls
0db block gets
1017consistent gets
0 physical reads
0redo size
481bytes sent via SQL*Net toclient
416bytes received via SQL*Netfrom client
2SQL*Net roundtrips to/fromclient
0sorts (memory)
0sorts (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 AVESHRINKAVEACTIVE 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.iyunv.com/guoyJoe
ITPUB: http://www.itpub.net/space-uid-28460966.html
OCM: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题：哪些SGA结构是必需的，哪些是可选的？否则拒绝申请!!!
答案在：http://blog.iyunv.com/guoyjoe/article/details/8624392
Oracle@Paradise 总群:127149411
Oracle@Paradise No.1群:177089463(已满)
Oracle@Paradise No.2群:121341761
Oracle@Paradise No.3群:140856036
版权声明：本文为博主原创文章，未经博主允许不得转载。
页:
[1]