在线扩大数据库UNDO表空间

Oracle docs note this about ORA-30036:

ORA-30036: unable to extend segment by string in undo tablespace "string"
Cause: the specified undo tablespace has no more space available.
Action: Add more space to the undo tablespace before retrying the operation. An alternative is to wait until active transactions to commit.

故障现象：Caused by java.sql.SQLException: ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1' UNDO表空间越来越大,长此下去最终数据因为磁盘空间不足而崩溃；

问题分析：产生问题的原因主要以下两点：
1. 有较大的事务量让Oracle Undo自动扩展，产生过度占用磁盘空间的情况；
2. 有较大事务没有收缩或者没有提交所导制；
说明：本问题在ORACLE系统管理中属于比较正常的一现象，日常维护多注意对磁盘空间的监控。
备份：(如果没有在线事务，可以不做，关闭监听)

方法一:

• 查看表空间的名字及文件所在位置：

select tablespace_name, file_id, file_name,round(bytes/(1024*1024),0) total_space
from dba_data_files order by tablespace_name;

• 修改数据库datafile文件到新的大小

alter database datafile '\oracle\oradata\undotab1.dbf' resize 4000m;

方法二:

启动SQL*Plus session并执行下面命令:

oracle% sqlplus /nolog
sql> connect / as sysdba;
sql> spool $ORACLE_BASE/admin/oss/scripts/recreate_undo.log;
sql> CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS2" DATAFILE '/db/data/undotbs02.dbf' SIZE 30M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE 32767M;
sql> ALTER SYSTEM SET UNDO_TABLESPACE="UNDOTBS2";
sql> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
sql> CREATE BIGFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '/db/data/undotbs01.dbf' SIZE 35M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE 128G;
sql> ALTER SYSTEM SET UNDO_TABLESPACE="UNDOTBS1";
sql> DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;
sql> exit
oracle%

NOTE!

• 如果看到这个错误：“ORA-30013: undo tablespace 'undotas1' is currently in use”需等待10-30秒，有时会更长些，再重新执行上面命令。也可以执行“UNDOTBS1 / UNDOTBS2”来检查DROP命令是否可以执行    sql>SELECT SEGMENT_NAME, XACTS, V.STATUS FROM V$ROLLSTAT V, DBA_ROLLBACK_SEGS WHERE TABLESPACE_NAME = 'UNDOTBS1' AND SEGMENT_ID = USN;    返回结果:

“no rows selected”

• 如果返回的结果是UNDOTBS1还在用的话，那么可能要重启ORACLE服务再执行上面的步骤。