UNDO管理

最新推荐文章于 2024-07-30 10:00:00 发布

郭一军_guoyJoe

最新推荐文章于 2024-07-30 10:00:00 发布

阅读量4.3k

点赞数 2

分类专栏： Oracle Training

本文链接：https://blog.csdn.net/guoyJoe/article/details/8648773

版权

Oracle Training 专栏收录该内容

41 篇文章 2 订阅

订阅专栏

一、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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

| 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

一致性读从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: http://education.oracle.com/education/otn/YGuo.HTM
_____________________________________________________________
加群验证问题：哪些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