关于oracle中的undo

一，undo介绍

二，undo视图说明

三，常用脚本说明

四.释放UNDO表空间

五.参考

一，undo介绍
Oracle中 undo的作用主要有两个：第一是回滚事务，第二是产生一致性读。同时也衍生出了一些新的 功能，比如Flashback query。传统的 undo是通过undo segment 来管理的，我们看下面的示例：

详见第二篇文章《oracle undo原理》

二，undo视图说明
DBA_ROLLBACK_SEGS
V$ROLLSTAT
V$TRANSACTION
V$UNDOSTAT
DBA_UNDO_EXTENTS
DBA_UNDO_EXTENTS describes the extents comprising the segments in all undo tablespaces in the database. This view shows the status and size of each extent in the undo tablespace.

DBA_UNDO_EXTENTS.STATUS有三个值：

ACTIVE 表示未提交事务还在使用的UNDO EXTENT，该值对应的UNDO SEGMENT的DBA_ROLL_SEGMENTS.STATUS一定是ONLINE或PENDING OFFLINE状态，一旦没有活动的事务在使用UNDO SEGMENT，那么对应的UNDO SEGMENT就变成OFFLINE状态。
EXPIRED 表示已经提交且超过了UNDO_RETENTION指定时间的UNDO EXTENT。
UNEXPIRED 表示已经提交但是还没有超过UNDO_RETENTION指定时间的UNDO EXTENT。
Oracle重复使用UNDO EXTENT的原则如下：
ACTIVE状态的EXTENT在任何情况下都不会被占用。
如果是自动扩展的UNDO表空间，Oracle会保证EXTENT至少保留UNDO_RETENTION指定的时间。
如果自动扩展空间不足或者UNDO表空间是非自动扩展，Oracle会尝试重复使用同一个段下面EXPIRED状态的EXTENT，如果本段中没有这样的EXTENT，就会去偷别的段下面EXPIRED状态的EXTENT，如果依然没有这样的EXTENT，就会使用本段UNEXPIRED的EXTENT，如果还是没有，那么会去偷别的段的UNEXPIRED的EXTENT，这个都没有，就会报错。
三，常用脚本说明
1.undo表空间总大小

select tablespace_name,contents from dba_tablespaces where tablespace_name='UNDOTBS1';

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

2.查看undo表空间的使用情况 有两个视图可查

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 ;

select owner,segment_name,bytes/1024/1024 mb from dba_segments where tablespace_name='UNDOTBS1'

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

-- 我的UNDO表空间超过了90%，是哪些会话的事务占用了这些空间：

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

4.查询每秒使用的undo表空间大小

select ur undo_retention,
       dbs db_block_size,
       ((ur * (ups * dbs)) + (dbs * 24))  1024  1024 as "M_bytes"
  from (select value as ur from v$parameter where name = 'undo_retention'),
       (select (sum(undoblks)  sum(((end_time - begin_time) * 86400))) ups
          from v$undostat),
       (select value as dbs from v$parameter where name = 'db_block_size');

5.根据Oracle对UNDO表空间的统计信息调整UNDO参数及大小

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

KTUSMST2将没有数据生成，该表是DBA_HIST_UNDOSTATS视图的源表。

V$UNDOSTAT视图，该视图的作用是用于指导管理员调整UNDO表空间的参数及表空间大小，每行表示的是10分钟的数据，最多可保留576行，4天一个周期，如果该视图没有数据，那么UNDO可能是手动管理方式。下面对该视图字段的含义进行说明：

6.查询undo表空间extent的使用情况

select sum(bytes)/1024/1024 MB, status, tablespace_name
  from dba_undo_extents
  group by status, tablespace_name order by 3, 2;

四.释放UNDO表空间
详见：http://blog.itpub.net/23135684/viewspace-1065601/

新建一个undo表空间，然后修改undo_tablespace指向新的表空间，然后查询下面的视图，待原来的表空间上所有的段都为offline状态时才能删除。

查询

set line 200 pages 999
col segment_name format a30
col tablespace_name format a30
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 ;

五.参考
监控和管理Oracle UNDO表空间的使用[1]

UNDO表空间监控说明[2]

里面有MOS中对AUM和SUM的描述