UNDO表空间 的监控

初始化参数

undo_management=AUTO 表示实例自动化管理UNDO表空间，从Oracle 9i开始，Oracle引进了AUM（Automatic Undo Management）。
undo_retention=900 事务提交后，相应的UNDO数据保留的时间，单位：秒。
undo_tablespace=UNDOTBS1 活动的UNDO表空间。
_smu_debug_mode=33554432
_undo_autotune=TRUE


Oracle Database基于undo表空间大小和系统活动自动调整undo retention，通过设置UNDO_RETENTION初始化参数指定undo retention的最小值。

查看Oracle自动调整UNDO RETENTION的值可以通过以下查询获得：
SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,TUNED_UNDORETENTION FROM V$UNDOSTAT;

针对自动扩展的UNDO表空间，系统至少保留UNDO到参数指定的时间，自动调整UNDO RETENTION以满足查询对UNDO的要求，这可能导致UNDO急剧扩张，可以考虑不设置UNDO RETENTION值。

针对固定的UNDO表空间，系统根据最大可能的undo retention进行自动调整，参考基于UNDO表空间大小和使用历史进行调整，这将忽略UNDO_RETENTION,除非表空间启用了RETENTION GUARANTEE。

自动调整undo retention不支持LOB，因为不能在undo表空间中存储任何有关LOBs事务的UNDO信息。

可以通过设置_undo_autotune=FALSE显示的关闭Automatic UNDO Retention功能。

当使用的UNDO表空间非自动增长，tuned_undoretention是基于UNDO表空间大小的使用率计算出来的，在一些情况下，特别是较大的UNDO表空间时，这将计算出较大的值。

为了解决此行为，设置以下的实例参数：
_smu_debug_mode=33554432

设置该参数，TUNED_UNDORETENTION就不基于undo表空间大小的使用率计算，代替的是设置(MAXQUERYLEN +300)和UNDO_RETENTION的最大值。

UNDO表空间数据文件自动扩展

如果UNDO表空间是一个自动扩展的表空间，那么很有可能UNDO表空间状态为EXPIRED的EXTENT不会被使用（这是为了减少报ORA-01555错误的几率），这将导致UNDO表空间变得很大；如果将UNDO表空间设置为非自动扩展，那么状态为EXPIRED的EXTENT就能被利用，这样可以一定程度控制UNDO表空间的大小，但这样会增加ORA-01555报错和UNDO空间不足报错的风险。合理的非自动扩展的UNDO表空间大小，以及合理的UNDO_RETENTION设置可以确保稳定的UNDO空间使用。

UNDO表空间guarantee属性

如果UNDO表空间是noguarantee状态，Oracle不确保提交后的事务对应的UNDO表空间中的数据会保留UNDO_RETENTION指定的时长，如果UNDO表空间不足，其他事务将可能偷盗相应的未过期的空间；将UNDO表空间设置为guarantee能够确保提交后的事务对应UNDO表空间中的数据在任何情况下都将保留UNDO_RETENTION指定的时长。

SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1';

SQL> alter tablespace undotbs1 retention guarantee;

SQL> SELECT tablespace_name, retention FROM dba_tablespaces where tablespace_name='UNDOTBS1';

UNDO表空间大小

              针对不同类型的业务系统，需要有充足的UNDO表空间，确保系统能够正常的运行。UNDO空间的大小跟业务系统有关系，也跟UNDO_RETENTION和UNDO表空间的GUARANTEE属性有关系，通常我们可以通过V$UNDOSTAT的统计信息估算出需要的UNDO表空间大小。

UNDO表空间总大小。

      UNDO表空间下也以段的形式存储数据，每个事务对应一个段，这种类型的段通常被称为回滚段，或者UNDO段。默认情况下，数据库实例会初始化10个UNDO段，这主要是为了避免新生成的事务对UNDO段的争用。
UNDO表空间的总大小就是UNDO表空间下的所有数据文件大小的总和：
SQL> select tablespace_name,contents from dba_tablespaces where tablespace_name='UNDOTBS1';

TABLESPACE_NAME CONTENTS
------------------------------------------------------------ ------------------
UNDOTBS1 UNDO

SQL> select tablespace_name,sum(bytes)/1024/1024 mb from dba_data_files where tablespace_name='UNDOTBS1' group by tablespace_name;

TABLESPACE_NAME MB
------------------------------------------------------------ ----------
UNDOTBS1 90

查看UNDO表空间的使用情况。

SQL> select owner,segment_name,bytes/1024/1024 mb from dba_segments where tablespace_name='UNDOTBS1';
SQL> select segment_name, v.rssize/1024/1024 mb From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ;

查询V$ROLLSTAT数据更加准确。

查询事务使用的UNDO段及大小。

 是哪些会话的事务占用了这些空间：
SQL> select s.sid,s.serial#,s.sql_id,v.usn,segment_name,r.status, v.rssize/1024/1024 mb From dba_rollback_segs r, v$rollstat v,v$transaction t,v$session s Where r.segment_id = v.usn and v.usn=t.xidusn and t.addr=s.taddr order by segment_name ;


通过这个SQL语句可以查询到会话对应的活动事务使用的UNDO段名称，以及该段占用的UNDO空间大小，对于非活动事务占用了UNDO空间是由Oracle实例根据参数配置自动化管理的。

查询V$UNDOSTAT的例子：

SELECT TO_CHAR(BEGIN_TIME, 'MM/DD/YYYY HH24:MI:SS') BEGIN_TIME,TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,UNDOTSN, UNDOBLKS, TXNCOUNT, MAXCONCURRENCY AS "MAXCON",MAXQUERYLEN, TUNED_UNDORETENTION FROM v$UNDOSTAT;

当字段UNXPSTEALCNT和EXPBLKREUCNT是非零值，表示有空间压力。
如果字段SSOLDERRCNT是非零值，表示UNDO_RETENTION设置不合理。
如果字段NOSPACEERRCNT是非零值，表示有一系列空间问题。
在10g DBA_HIST_UNDOSTAT视图包括了V$UNDOSTAT快照统计信息。
注意：如果参数_undo_autotune=FALSE,X$KTUSMST2将没有数据生成，该表是DBA_HIST_UNDOSTATS视图的源表。

释放UNDO表空间。

UNDO表空间被撑得过大，需要释放这些空间，通常的做法是新建一个UNDO，然后设置使用新建的UNDO表空间，最后DROP原有UNDO表空间。下面通过一个例子来演示这个过程：
SQL> col segment_name format a30
SQL> col tablespace_name format a30
SQL>
SQL> select segment_name, tablespace_name, r.status,(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v where r.segment_id = v.usn(+) order by segment_name ;

当前所有的回滚段在属于UNDOTBS1表空间。

SQL> create undo tablespace undotbs2 datafile 'F:\APP\ORADATA\ORCL\undotbs02.dbf' size 20m autoextend on next 100m;

表空间已创建。

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL> alter system set undo_tablespace='UNDOTBS2';

系统已更改。

SQL> show parameter undo

NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

SQL> select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id = v.usn(+) order by segment_name ;


将数据库实例使用的UNDO表空间指向了新表空间，但是依然有过去的事务在使用UNDOTBS1表空间下面的段，这个时候不能直接DROP UNDOTBS1（执行DROP命令也会报错），必须等待UNDOTBS1表空间下的所有段状态变成OFFLINE才能DROP。

SQL> select segment_name, tablespace_name, r.status,(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v  Where r.segment_id = v.usn(+)
 order by segment_name


UNDOTBS1表空间下的所有段状态都变成了OFFLINE，这个时候可以DROP UNDOTBS1来释放空间。

SQL> drop tablespace undotbs1 including contents and datafiles;

表空间已删除。

虽然能DROP，只是说明没有事务在使用旧的UNDO表空间，这并不表示所有的UNDO EXTENT已经过期（DBA_UNDO_EXTENTS.STATUS），如果有某些查询需要用到这些存储在旧UNDO表空间上过期或未过期的EXTENT时，将收到ORA-01555的报错。

SQL> select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent  From dba_rollback_segs r, v$rollstat v  Where r.segment_id = v.usn(+)  order by segment_name ;