undo表空间爆满的原因排查以及解决办法

–查看undo表空间的实时以及历史的 使用情况
如果一个表未做索引，那么他在进行dml时会产生效率很低，也有可能导致UNDOTBS表空间异常增大。一般来说INSERT生成的UNDO最少，因为对于INSERT而言，
Oracle 只要记录其对应的一个“删除“行的rowid即可。其次就是UPDATE 操作，对于该操作而言，只记录修改的字节；通常，在多数情况下，我们只修改行的一小部分，
那么，UNDO会将该部分记录。相对上述两种操作，DELETE操作会生成最多的UNDO，因为Oracle必须把整行的前影像都记录到了undo段中

–查看回滚段的使用情况，哪个用户正在使用回滚段的资源:
SELECT r.NAME 回滚段名,s.sid SID,s.serial# Serial,s.sql_id,s.osuser,
s.username 用户名,s.machine 机器名,
t.start_time 开始时间,t.status 状态,
t.used_ublk 撤消块,USED_UREC 撤消记录,used_urec,
t.cr_get 一致性取,t.cr_change 一致性变化,
t.log_io “逻辑I/O”,t.phy_io “物理I/O”,
t.noundo NoUndo,g.extents Extents,substr(s.program, 1, 50) 操作程序
FROM v$sessions,v$transaction t, v$rollnamer,v$rollstat g
WHERE t.addr = s.taddr
AND t.xidusn = r.usn
AND r.usn = g.usn
ORDER BY t.start_time desc

v$undostat视图包含了数据库如何来使用撤销空间的统计信息，以及运行时间最长的查询等信息。可以使用这些信息计算当前数据库所处理的工作负载的撤销空间大小。v$undostat视图中每一行显示十分钟时间间隔的撤销统计信息。表中最多包含576行数据，每一行记录十分钟。因此，最多可以查看四天内的撤销空间使用情况。
BEGIN_TIME：时间段的起始时间。
END_TIME：时间段的截止时间。
UNDOBLKS：在十分钟的间隔内数据库所消耗的撤销数据块数量。
TXNCOUNT：在十分钟时间间隔内所执行的事务数。
MAXQUERYLEN：显示了在十分钟间隔内该实例上执行的，耗时最长的查询所花的时间（单位为秒）。
MAXQUERYID：时间间隔内运行时间最长的SQL语句的标识符。
NOSPACEERRCNT:数据库在撤销表空间中，因为整个空间都被活动事务所占用，因而没有足够空间来存放新撤销数据的次数。
TUNED_UNDORETENTION:数据库在提交撤销所属事务后，将会保留撤销数据的时长，单位为秒。

select to_char(begin_time, ‘hh24:mi:ss’) BEGIN_TIME,
to_char(end_time, ‘hh24:mi:ss’) END_TIME,
maxquerylen,
nospaceerrcnt,
tuned_undoretention
from v$undostat;

–2. 查看历史的Undo使用情况
select BEGIN_TIME,
end_time,
round(maxquerylen / 60, 0) maxq, --最长sql执行时间
maxquerysqlid, --最长sql执行时间的sqlid
undotsn, --最后活动的undo tablespace 编号
undoblks, --消耗的undo block size
txncount, --时段内的食物数量
unexpiredblks, --未过期的
expiredblks, --时段内未过期的undp block总数
round(tuned_undoretention / 60, 0) Tuned --auto undoundoretention tuned之后的undo_retention
from dba_hist_undostat a
where end_time > sysdate - 2
order by 1

–通过undo历史表中查看到的sql_id,查看sql_text
select * from dba_hist_sqltext where sql_id=’&sqlid’;
或者
select sql_fulltext from v$sql where sql_id=’&sqlid’;

–3. 如何释放应经扩展的undo表空间。

1. . 查找数据库的UNDO表空间名,确定当前例程正在使用的UNDO表空间：
   Show parameter undo_tablespace

2. 确认UNDO表空间；
   select name from v$tablespace;

3. 检查数据库UNDO表空间占用空间情况以及数据文件存放位置；
   SQL>select file_name,bytes/1024/1024 fromdba_data_files where tablespace_name like ‘UNDO%’;

4. 查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。
   SELECT r.NAME 回滚段名,s.sid SID,s.serial# Serial,s.sql_id,s.osuser,
   s.username 用户名,s.machine 机器名,
   t.start_time 开始时间,t.status 状态,
   t.used_ublk 撤消块,USED_UREC 撤消记录,used_urec,
   t.cr_get 一致性取,t.cr_change 一致性变化,
   t.log_io “逻辑I/O”,t.phy_io “物理I/O”,
   t.noundo NoUndo,g.extents Extents,substr(s.program, 1, 50) 操作程序
   FROM v$sessions,v$transaction t, v$rollnamer,v$rollstat g
   WHERE t.addr = s.taddr
   AND t.xidusn = r.usn
   AND r.usn = g.usn
   ORDER BY t.start_time desc

5. 检查UNDO Segment状态；
   select s.username, s.SID, u.name, r.RSSIZE,r.WRITES, r.HWMSIZE
   fromv$transactiont,v$rollstat r, v$rollnameu,v$session s
   wheres.taddr = t.addr
   andt.xidusn = r.usn
   andr.usn = u.usn
   orderby r.rssize desc;

6. 创建新的UNDO表空间，并设置自动扩展参数；
   create undo tablespace undotbs2 datafile ‘/opt/oracle/oradata/ge01/UNDOTBS2.dbf’ size 100m reuse autoextend on 50m;

7. 切换UNDO表空间为新的UNDO表空间 ， 动态更改spfile配置文件；
   alter system set undo_tablespace=undotbs2 scope=both;

8. 验证当前数据库的 UNDO表空间
   show parameter undo

9. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE；
   select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from v$rollstatorder by rssize;

select t.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segst;

11. 删除原有的UNDO表空间；
    drop tablespace undotbs1including contents and datafiles;

–4.回滚段维护：
1.查询回滚段的信息。所用数据字典：DBA_ROLLBACK_SEGS，可以查询的信息：回滚段的标识(SEGMENT_ID)、名称(SEGMENT_NAME)、所在表空间(TABLESPACE_NAME)、类型(OWNER)、状态(STATUS)。
select * from DBA_ROLLBACK_SEGS

2.查看回滚段的统计信息：
SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status
FROM v$rollnamen,v$rollstat s
WHERE n.usn = s.usn;