> 数据库 > Oracle >

解决UNDOTBS1表空间过大导致磁盘空间不足的问题

今日发现业务日志中有提示ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'

查看数据库所在服务器的硬盘空间发现UNDOTBS1表空间所在的磁盘空间不足,分析应该是此问题导致异常。

迁移UNDOTBS1表空间到其他磁盘后数据库正常。

现将过程和在网上找到的相关资料汇总如下:希望对自己和大家有帮助。


undo表空间不断扩大问题的原因:1有较大的事务量让oracle undo 自动扩展,产生过度占有磁盘空间的情况。2有较大事务没有收缩或者没有提交所导致。
 

1.查看还原表空间所在磁盘是否使用率过高,及linux 系统哪个磁盘处于比较空闲的状态

:$df -lh

2.在oracle 数据库中查看所有表空间的占用率:

SQL> SELECT Total.name "Tablespace Name",nvl(Free_space, 0) Free_space,nvl(total_space-Free_space, 0) Used_space,total_space FROM (select tablespace_name, sum(bytes/1024/1024) Free_Space from sys.dba_free_space group by tablespace_name) Free,(select b.name,  sum(bytes/1024/1024) TOTAL_SPACE from sys.v_$datafile a, sys.v_$tablespace B where a.ts = b.ts group by b.name) Total WHERE Free.Tablespace_name(+) = Total.name ORDER BY Total.name;

星期二 5月  29      第    1
                        Space Usage for Database in Meg

EXAMPLE                             21.25      78.75         100
STAGING                            1.9375     1.0625           3
SYSAUX                            35.4375   524.5625         560
SYSTEM                              3.125    686.875         690
UNDOTBS1                          194.375     85.625         280
USERS                               .9375     4.0625           5
SQL>
SQL> tti off
SQL>

查询undo表空间的路径

sql>select file_name ,bytes/1024/1024
    from dba_data_files
    where tablespace_name like 'UNDOTBS1';


3.检查还原表空间的segment的状态的信息:
    
  sql>select usn,xacts,rssize/1024/1024/1024,
      hwmsize/1024/10244/1024, shrinks
      from v$rollstat order by rssize;




  0     0            .000358582              .000035844    0
  1     0            .002067566              .000206676    0
  5     0            .002067566              .000206676    0
  6     0            .002067566              .000206676    0
 10     0            .002067566              .000206676    0
  4     0            .002067566              .000206676    0
  9     0            .003105164              .000310395    0
  8     0            .004020691              .000401912    0
  7     0            .005973816              .000597148    0
  3     0            .022575378              .002256656    0
  2     0            .036552429              .003653816    0


这表示该还原表中还存在11个回滚对象,及数据库的历史数据。


4. 创建新的还原表空间。
  sql>create undo tablespace undotbs2
      datafile 'F:\ORACLE11GR2\ORADATA\ORCL\UNDOTBS02.DBF'
      size 10m autoextend on maxsize 1GB;
    
5.在oracle数据库中把默认的还原表空间切换成新建的还原表空间undotbs2
sql>alter system set undo_tablespace=undotbs2 scope=both ;


6.验证当前数据库使用的还原表空间是否为新建的还原表空间:
sql>show parameter undo


7.等待旧的还原表空间所有的数据变成脱机状态(undo segment offline):
  sql>select t.segment_name , t.tablespace_name, t.segment_id, t.status
      from dba_rollback_segs t;


SYSTEM                         SYSTEM                                  0 ONLINE
_SYSSMU10_3176102001$          UNDOTBS1                               10 ONLINE
_SYSSMU9_1126410412$           UNDOTBS1                                9 ONLINE
_SYSSMU8_1557854099$           UNDOTBS1                                8 ONLINE
_SYSSMU7_137577888$            UNDOTBS1                                7 ONLINE
_SYSSMU6_1834113595$           UNDOTBS1                                6 ONLINE
_SYSSMU5_1018230376$           UNDOTBS1                                5 ONLINE
_SYSSMU4_2369290268$           UNDOTBS1                                4 ONLINE
_SYSSMU3_991555123$            UNDOTBS1                                3 ONLINE
_SYSSMU2_2082490410$           UNDOTBS1                                2 ONLINE
_SYSSMU1_1518548437$           UNDOTBS1                                1 ONLINE




上面对应的回滚段均为 offline 时即可干掉 之前的undo表空间了
 sql>drop tablespace undotbs1 including contents and datafiles;


(责任编辑:IT)