undo tablespace switch

sometimes,the devs will meet the situation that delete lots of objects and I was much sure the undo may eat up the disk space,so you usually meet the situation to switch the undo ,when the del operation was over,following as:

1.create a new undo tablespace

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE ‘F:ORADATA2AAAUNDOTBS02.DBF’ SIZE 200M;

2.switch the undo to new created tablespace

SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2  SCOPE=BOTH

3.make the original undo tbs offline and drop all the related files

SQL> ALTER TABLESPACE UNDOTBS1 OFFLINE;

表空间已更改。

SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;

表空间已删除。

4.check the undo parameter

SQL> SHOW PARAMETER UNDO

NAME TYPE VALUE
———————————— ———————- ———-
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS2

 

–EOF–