Oracle undo表空间管理

当在做DML操作的时候，在修改buffer cache里面的数据块的时候，会对当前块的旧镜像信息做一个保存，保存的数据块就是undo块。undo块上面保存着数据修改前的信息。

 

生成undo块的几个作用：

1. 当事务没有提交，undo用来回滚事务
2. 读一致性
3. 在做实例恢复的时候对事务进行回滚
4. Flashback query 闪回查询

在9I之前undo块存储的方式是手工的方式进行存储，这个需要DBA根据事务的情况建立相应的回滚段然后手工改的方式为每一个事务分配回滚段。

现在基本对undo块的存储通过自动，这个就使用了undo表空间。Oracle 9I以后对undo管理就不使用手工方式，因为随着事务增加，手动管理的方式容易在回滚段上面产生冲突。自动管理在undo空间online的时候可以自动的建立undo段，这一切都是oracle自动管理的，对dba很简单，只需要建立相应的undo表空间即可，让oracle自动管理。

 

SQL> show parameter undo;



NAME      TYPE  VALUE

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

undo_management      string  AUTO

undo_retention      integer  900

undo_tablespace      string  UNDOTBS2

 

undo_management采用的是自动管理AUTO，在9I之前都是manual手动管理。通过undo_tablespace指定undo表空间UNDOTBS2。对于undo的自动管理还有一个参数undo_retention（当在undo块上面所对应的事务在完成提交以后，这个undo块空间是可以再被循环使用的，为了使得undo的信息可以保存更长一点，因为这个对flashback query等都比较重要，尽量保留时间长一点，所以可以设置undo_retention这个参数。当提交事务以后，undo块可以保留多长时间不被覆盖），这个时间到底设置多长呢？一般建议超过数据库里面最长事务查询的时间，比如一个查询大概需要半小时，那么undo_rentention设置为半小时1800s，这样保证在做查询的时候不会出现ORA-0155快照太旧。

 

在oracle数据库里面一般可以有多个undo表空间，但是只有一个undo表空间是active状态。

SQL> select TABLESPACE_NAME,STATUS,CONTENTS from dba_tablespaces;



TABLESPACE_NAME        STATUS  CONTENTS

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

SYSTEM        ONLINE  PERMANENT

SYSAUX        ONLINE  PERMANENT

TDS           ONLINE  PERMANENT

UNDOTBS2      ONLINE  UNDO

USER_TEMP     ONLINE  TEMPORARY



SQL> create undo tablespace undotbs3

  2  datafile '/u01/app/oracle/oradata/oradb/undotbs03.dbf' size 100m

  3  autoextend on;



Tablespace created.



SQL> select TABLESPACE_NAME,STATUS,CONTENTS from dba_tablespaces;  --可以看到有两个undo表空间



TABLESPACE_NAME        STATUS  CONTENTS

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

SYSTEM        ONLINE  PERMANENT

SYSAUX        ONLINE  PERMANENT

TDS           ONLINE  PERMANENT

UNDOTBS2      ONLINE  UNDO

USER_TEMP     ONLINE  TEMPORARY

UNDOTBS3      ONLINE  UNDO



6 rows selected.



SQL> col SEGMENT_NAME for a30;

SQL> set linesize 1600;

SQL> select SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME from user_segments

  2  where tablespace_name='UNDOTBS3';



SEGMENT_NAME        SEGMENT_TYPE   TABLESPACE_NAME

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

_SYSSMU1_2546521612$        TYPE2 UNDO   UNDOTBS3

_SYSSMU2_347642666$            TYPE2 UNDO   UNDOTBS3

_SYSSMU3_3866846032$        TYPE2 UNDO   UNDOTBS3

_SYSSMU4_970427828$            TYPE2 UNDO   UNDOTBS3

_SYSSMU5_2070521138$        TYPE2 UNDO   UNDOTBS3

_SYSSMU6_312287002$            TYPE2 UNDO   UNDOTBS3

_SYSSMU7_3177245340$        TYPE2 UNDO   UNDOTBS3

_SYSSMU8_3841002300$        TYPE2 UNDO   UNDOTBS3

_SYSSMU9_1958399569$        TYPE2 UNDO   UNDOTBS3

_SYSSMU10_994732307$        TYPE2 UNDO   UNDOTBS3



10 rows selected.

当undo表空间online的时候，oracle会自动的分配undo段，比如上面就分配了10个undo段。

 

下面语句是查看undo段的状态，比如数据库里面有两个undo表空间，只有一个undo表空间是active状态，另外一个undo表空间不被使用，可以通过下面语句查看段的状态，通过dba_rollback_segs这个视图。

SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS2';

SEGMENT_NAME		       TABLESPACE_NAME	    STATUS
------------------------------ -------------------- ----------------
_SYSSMU11_4202450068$	       UNDOTBS2 	    OFFLINE
_SYSSMU12_2784461198$	       UNDOTBS2 	    OFFLINE
_SYSSMU13_306909427$	       UNDOTBS2 	    OFFLINE
_SYSSMU14_1288844830$	       UNDOTBS2 	    OFFLINE
_SYSSMU15_1412425860$	       UNDOTBS2 	    OFFLINE
_SYSSMU16_2466010722$	       UNDOTBS2 	    OFFLINE
_SYSSMU17_2893287284$	       UNDOTBS2 	    OFFLINE
_SYSSMU18_2611002218$	       UNDOTBS2 	    OFFLINE
_SYSSMU19_1129654697$	       UNDOTBS2 	    OFFLINE
_SYSSMU20_3826278671$	       UNDOTBS2 	    OFFLINE

10 rows selected.

SQL> select SEGMENT_NAME,TABLESPACE_NAME,STATUS from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS3';

SEGMENT_NAME		       TABLESPACE_NAME	    STATUS
------------------------------ -------------------- ----------------
_SYSSMU1_2546521612$	       UNDOTBS3 	    ONLINE
_SYSSMU2_347642666$	           UNDOTBS3 	    ONLINE
_SYSSMU3_3866846032$	       UNDOTBS3 	    ONLINE
_SYSSMU4_970427828$	           UNDOTBS3 	    ONLINE
_SYSSMU5_2070521138$	       UNDOTBS3 	    ONLINE
_SYSSMU6_312287002$	           UNDOTBS3 	    ONLINE
_SYSSMU7_3177245340$	       UNDOTBS3 	    ONLINE
_SYSSMU8_3841002300$	       UNDOTBS3 	    ONLINE
_SYSSMU9_1958399569$	       UNDOTBS3 	    ONLINE
_SYSSMU10_994732307$	       UNDOTBS3 	    ONLINE

10 rows selected.

 

也可以通过两个动态视图v$rollstat，v$rollname去了解undo段的使用情况。

SQL> select a.usn,a.name,b.XACTS,b.status from v$rollname a,v$rollstat b

  2  where a.usn=b.usn;



       USN NAME        XACTS STATUS

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

 0 SYSTEM    0 ONLINE

11 _SYSSMU11_4202450068$    0 ONLINE

12 _SYSSMU12_2784461198$    0 ONLINE

13 _SYSSMU13_306909427$    0 ONLINE

14 _SYSSMU14_1288844830$    0 ONLINE

15 _SYSSMU15_1412425860$    0 ONLINE

16 _SYSSMU16_2466010722$    0 ONLINE

17 _SYSSMU17_2893287284$    0 ONLINE

18 _SYSSMU18_2611002218$    0 ONLINE

19 _SYSSMU19_1129654697$    0 ONLINE

20 _SYSSMU20_3826278671$    0 ONLINE



11 rows selected.

System回滚段是建立正在system表空间上面的，只能用于sys用户的事务操作，对于普通用户只能使用11-20回滚段。

这里使用Scott用户去产生一个事务

SQL> insert into t values(1);



1 row created.

未提交然后再去查询

SQL> /



       USN NAME        XACTS STATUS

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

 0 SYSTEM    0 ONLINE

11 _SYSSMU11_4202450068$    0 ONLINE

12 _SYSSMU12_2784461198$    0 ONLINE

13 _SYSSMU13_306909427$    0 ONLINE

14 _SYSSMU14_1288844830$    0 ONLINE

15 _SYSSMU15_1412425860$    0 ONLINE

16 _SYSSMU16_2466010722$    0 ONLINE

17 _SYSSMU17_2893287284$    0 ONLINE

18 _SYSSMU18_2611002218$    0 ONLINE

19 _SYSSMU19_1129654697$    0 ONLINE

20 _SYSSMU20_3826278671$    1 ONLINE



11 rows selected.

可以看到红色部分的undo段，XACTS，X代表事务，ACTS代表活跃的事务，20回滚段有未提交的事务，对应的undo信息保存在这个回滚段上面，一旦提交就为0了，表示undo对应的信息就没了，空间就可以再次被使用。

 

对于undo表空间一般会打开自动扩展，随着事务增多undo表空间的数据文件会越来越大，这样会占用大量磁盘空间，这个空间只会不断扩大，不会回收。

为了解决undo表空间过大，可以考虑新建立一个undo表空间，对undo表空间进行一个切换，然后将之前大的undo表空间进行删除释放空间，注意不能立刻将之前的undo表空间删除，因为切换以后可能在原有的undo表空间上面记录的一些事务还没有完成，如果将这个undo表空间给删除了，库意外宕机重启了，在做实例恢复回滚的时候就找不到undo数据了，库就可能打不开了。

在数据库正常关闭再启动或者保证原有undo表空间事务都完成了提交了再去删除原来的undo表空间。

SQL> show parameter undo;



NAME      TYPE  VALUE

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

undo_management      string  AUTO

undo_retention      integer  900

undo_tablespace      string  UNDOTBS2

SQL> alter system set undo_tablespace='UNDOTBS3';



System altered.



SQL> show parameter undo;  



NAME      TYPE  VALUE

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

undo_management      string  AUTO

undo_retention      integer  900

undo_tablespace      string  UNDOTBS3