解决UNDOTBS1表空间过大导致磁盘空间不足的问题
时间:2014-12-26 23:49 来源:linux.it.net.cn 作者:IT
今日发现业务日志中有提示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)
今日发现业务日志中有提示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) |