ORACLE 如何消除undo表空间爆满

检查数据库表空间占用空间情况:
SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB
2 from dba_data_files group by tablespace_name
3 union all
4 select tablespace_name,sum(bytes)/1024/1024/1024 GB
5 from dba_temp_files group by tablespace_name order by GB;

TABLESPACE_NAME GB

USERS .004882813
TEMP 20.5498047
UNDOTBS1 27.1582031

15 rows selected.

不幸的发现，UNDO表空间已经扩展至27G，而TEMP表空间也扩展至20G，这2个表空间加起来占用了47G的磁盘空间,导致了空间不足。
显然曾经有大事务占用了大量的UNDO表空间和Temp表空间，Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展,不收缩(shrink)的情况(通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性).
现在我们可以采用如下步骤回收UNDO空间:
1.确认文件
SQL> select file_name,bytes/1024/1024 from dba_data_files
2 where tablespace_name like ‘UNDOTBS1’;

FILE_NAME

BYTES/1024/1024

+ORADG/danaly/datafile/undotbs1.265.600173875
27810
2.检查UNDO Segment状态
SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;

   USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
复制

     0          0            .000358582             .000358582          0
     2          0            .071517944             .071517944          0
复制

3.创建新的UNDO表空间
SQL> create undo tablespace undotbs2;

Tablespace created.
4.切换UNDO表空间为新的UNDO表空间
SQL> alter system set undo_tablespace=undotbs2 scope=both;

System altered.
5.等待原UNDO表空间所有UNDO SEGMENT OFFLINE 当status从pending offline变成 online就可以了
SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;

   USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
复制

    14          0 ONLINE                     .000114441             .000114441          0
   USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
复制

     6          0 PENDING OFFLINE             2.9671936              2.9671936          0
复制

6.删除原UNDO表空间
11:34:00 SQL> drop tablespace undotbs1 including contents;

1. 查看表空间占用量
   select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_data_files group by tablespace_name
   union all
   select tablespace_name,sum(bytes)/1024/1024/1024 GB from dba_temp_files group by tablespace_name order by GB;
   通常情况下，undo表空间占用量比较高，例（UNDOTBS1）
2. 确认表空间UNDOTBS占用量
   select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like ‘UNDOTBS1’;
3. 检查UNDO Segment状态
   select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
4. 创建新的UNDO表空间
   create undo tablespace undotbs2;
5. 切换UNDO表空间为新的UNDO表空间
   alter system set undo_tablespace=undotbs2 scope=both;
6. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE
   select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;
7. 删除原UNDO表空间
   drop tablespace undotbs1 including contents;

如果要级联删除系统文件，可使用
drop tablespace undotbs1 including CONTENTS and datafiles;
UNDO表空间越来越大,产生问题的原因主要以下两点：

1. 有较大的事务量曾经让Oracle Undo自动扩展
2. 有较大事务长时间没有提交曾经让Oracle Undo自动扩展
   UNDO表空间变大后，把空间回收比较麻烦，Shrink命令经常不成功，但是可以通过切换UNDO表空间的方法，回收磁盘空间

解决步骤：

1. 启动SQLPLUS，并用sys登陆到数据库
2. 查找数据库的UNDO表空间名
   show parameter undo
   NAME TYPE VALUE

undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2
3. 检查数据库UNDO表空间占用空间情况以及数据文件存放位置
select tablespace_name, sum(bytes/1024/1024) “SIZE(M)”
from dba_data_files
where tablespace_name = ‘UNDOTBS2’
group by tablespace_name;

4. 查看回滚段的使用情况，哪个用户正在使用回滚段的资源，一般停掉业务系统，重新启动数据库后操作比较好，中国自学编程网，www.zxbc.cn。
   select s.username, u.name
   from v$transaction t,v$rollstat r, v$rollname u,v$session s
   where s.taddr=t.addr
   and t.xidusn=r.usn
   and r.usn=u.usn
   order by s.username;
5. 创建新的UNDO表空间，并设置自动扩展；
   create undo tablespace undotbs1
   datafile ‘/oradata/oradata/ddptest/UNDOTBS1.dbf’ size 1000m autoextend on next 800m maxsize unlimited;
6. 更改spfile配置；
   alter system set undo_tablespace=undotbs1 scope=both;
7. 重新启动数据库, 删除原有的UNDO表空间；
   drop tablespace undotbs2 including contents;
8. 册除原UNDO表空间的数据文件，回收磁盘空间
   #rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs02.dbf

在大家dba的职业生涯中,相信undo表空间暴满的情况基本上大家都会遇到,那又如何处理这种情况呢,最常用的方法就是创建新的undo表空间,然后修改undo_tablespace参数切换undo表空间到新创建的undo表空间…
还有一种方法是将undo_retention调小,一般大家的undo_retention参数都会采用默认的值10800,即事务的前映象在undo表空间中保存3个小时;如果你的db事务非常频繁,undo_retention就需要适当地调少,否则就得准备较大的undo表空间.
以上介绍的两种方法是治标不治本的方法,毕竟进行表空间切换还是比较麻烦的,也增加了系统的风险;将undo_retention调小,有可能对大的事务或长时间运行的查询有影响…
在10g,大家可以启用automatic undo_retention tuning,想启动automatic undo_retention tuning,需要把undo_retention设置为0,如果检测automatic undo_retention tuning起作用呢?可以查看v$undostat动态性能视图中tuned_undoretention字段,需要注意的是,自动的undo retention调优对lob的retention不受影响…
那如何才能彻底消除undo表空间爆满呢?通常我们最好的选择是调优查询,降低查询的执行时间,尽量减少长时间运行的查询的运行时间;还有就是减少大的事务,将大的事务分解成小事务,比如用pl/sql分批处理并提交等…