在线扩大数据库UNDO表空间

时间：2022-03-10 18:03

用oracle账号登陆ORACLE数据库服务器

方法一:

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; 返回结果: