|
今日发现业务日志中有提示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) |