关于Oracle 释放过度使用的Undo表空间

关于Oracle 释放过度使用的Undo表空间，有较大的事务量让Oracle Undo自动扩展，产生过度占用磁盘空间的情况；

产生问题的原因主要以下两点：
1. 有较大的事务量让oracle undo自动扩展，产生过度占用磁盘空间的情况；
2. 有较大事务没有收缩或者没有提交所导制；
 
$>exp vas/vas file=/opt/oracle/data_1.dmp,/opt/oracle/data_2.dmp log=/opt/oracle/date.log owner=vas rows=y indexes=y compress=n buffer=65536 feedback=100000 volsize=0 filesize=1000m
 
解决步骤：
查找数据库的undo表空间名
 
sql> select name from v$tablespace;
 
检查数据库undo表空间占用空间情况以及数据文件存放位置；
sql>select file_name, bytes/1024/1024 from dba_data_files;
 
查看回滚段的使用情况，哪个用户正在使用回滚段的资源，如果有用户最好更换时间（特别是生产环境）。
select s.username, u.name, d.tablespace_name
from v$transaction t,v$rollstat r, v$rollname u, v$session s, dba_rollback_segs d
where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn and r.usn=d.segment_id order by s.username;
 
检查undo segment状态
select usn, xacts, rssize/1024/1024/1024, hwmsize/1024/1024/1024, shrinks
 from v$rollstat order by rssize;
 
创建新的undo表空间，并设置自动扩展参数；
create undo tablespace undotbs2 datafile '/oradata/oradata/ddptest/undotbs2.dbf' size 1000m reuse autoextend on next 800m maxsize unlimited;
 
动态更改spfile配置文件；
alter system set undo_tablespace=undotbs2 scope=both;
 
等待原undo表空间所有undo segment offline；
select segment_name, tablespace_name, status from dba_rollback_segs;
 
再执行看undo表空间所有undo segment online；
select segment_name, tablespace_name, status from dba_rollback_segs;
 
删除原有的undo表空间；
drop tablespace undotbs2 including contents;
 
确认删除是否成功；
select name from v$tablespace;
 
册除原undo表空间的数据文件，，其文件名为步骤中执行的结果。
#rm $oracle_base/oradata/$oracle_sid/undotbs02.dbf
 
-- 查看事务对应的回滚段
select a.name, b.xacts, c.sid, c.serial# ,d.sql_text
from v$rollname a,v$rollstat b,v$session c,v$sqltext d,v$transaction e
where a.usn=b.usn and b.usn=e.xidusn and c.taddr=e.addr
and c.sql_address=d.address and c.sql_hash_value=d.hash_value order by a.name,c.sid,d.piece;
 
-- 查看事务占用undo 空间
col username for a16
col osuser for a16
select t.used_ublk,
      s.username,
      s.sid,
      s.serial#,
      pr.pid,
      s.osuser,
      s.machine,
      s.program,
      rs.segment_id,
      r.usn,
      rs.segment_name,     
      r.rssize/1024/1024,
      sq.sql_text
  from v$transaction t, v$session s, v$rollstat r, dba_rollback_segs rs, v$sqltext sq, v$process pr
 where s.saddr = t.ses_addr
  and t.xidusn = r.usn
  and rs.segment_id = t.xidusn
  and s.sql_address=sq.address
  and s.sql_hash_value = sq.hash_value
  and s.paddr=pr.addr
order by t.used_ublk desc, sq.piece;

相关阅读：

Oracle Undo 镜像数据探究

Oracle 回滚(ROLLBACK)和撤销(Undo)

Undo 表空间损坏导致无法open

百度虚拟主播

百度智能云平台的一站式、灵活化的虚拟主播直播解决方案

126

查看详情

Undo表空间失败的处理方法

Oracle Undo表空间重建与恢复