oracle undo的定义,关于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_EXTENTSDBA_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视图包括了VKTUSMST2将没有数据生成，该表是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的描述AUM 常用分析/诊断脚本 (文档 ID 1526122.1)

参考资料[1]

监控和管理Oracle UNDO表空间的使用: http://blog.itpub.net/23135684/viewspace-1065601/[2]

UNDO表空间监控说明: http://www.askmaclean.com/archives/undo%E8%A1%A8%E7%A9%BA%E9%97%B4%E7%9B%91%E6%8E%A7%E8%AF%B4%E6%98%8E.html