在线扩大数据库UNDO表空间

在线扩大数据库UNDO表空间，用Oracle账号登陆Oracle数据库服务器方法一:查看表空间的名字及文件所在位置：select tablespace_name, file_id, file_name,round（bytes/（1024*1024）,0） total_spacefrom dba_data_files order by tablespace_name;修改数据库datafile文件到新的大小alter database datafile "oracleoradataundotab1.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服务再执行上面的步骤。--------------------------------------------------------------------------------RMAN备份与恢复之undo表空间丢失 http://www.linuxidc.com/Linux/2014-01/95335.htm关于Oracle 释放过度使用的undo表空间 http://www.linuxidc.com/Linux/2013-09/90315.htmOracle undo的一些理解 http://www.linuxidc.com/Linux/2013-09/89701.htmOracle undo 镜像数据探究 http://www.linuxidc.com/Linux/2013-08/89074.htmOracle 回滚（ROLLBACK）和撤销（undo） http://www.linuxidc.com/Linux/2013-08/88792.htmLinux-6-64下安装Oracle 12C笔记 http://www.linuxidc.com/Linux/2013-07/86805.htm在CentOS 6.4下安装Oracle 11gR2（x64） http://www.linuxidc.com/Linux/2014-02/97374.htmOracle 11gR2 在VMWare虚拟机中安装步骤 http://www.linuxidc.com/Linux/2013-09/89579p2.htmDebian 下安装 Oracle 11g XE R2 http://www.linuxidc.com/Linux/2014-03/98881.htm--------------------------------------------------------------------------------更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址