切换undo表空间
(1)查看oracle版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
(2)查看当前undo表空间状态
SQL> show parameter undo_tablespace
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_tablespace string UNDOTBS2
(3)新建undo表空间
SQL> create undo tablespace UNDOTBS3 datafile '/xxxx/undotbs_11.dbf' size 5g autoextend on next 50m;
Tablespace created.
(4)切换undo表空间
SQL> alter system set undo_tablespace = undotbs3 scope=both;
System altered.
(5)查看切换后undo表空间状态
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS3
(6)查看当前undo表空间使用状态
SQL> select tablespace_name, status, count(*) from dba_rollback_segs group by tablespace_name, status;
TABLESPACE_NAME STATUS COUNT(*)
------------------------------ ---------------- ----------
UNDOTBS1 OFFLINE 168
SYSTEM ONLINE 1
UNDOTBS3 ONLINE 521
UNDOTBS2 OFFLINE 513
UNDOTBS2 ONLINE 8
由于是从UNDOTBS2切换到UNDOTBS3,发现当前UNDOTBS2还是正在使用的部分,所以暂时无法删除UNDOTBS2,
需要等待UNDOTBS2中全部执行完毕,STATUS全部变为OFFLINE才可以删除UNDOTBS2表空间。
SQL> Drop tablespace UNDOTBS2 including contents and datafiles;
SQL>SHOW parameter ROLLBACK
SQL>alter system set fast_start_parallel_rollback=false scope=both;
有关UNDO表空间的一些内容参见: