Shrinking UNDO Tablespace in Oracle 11gR2

To shrink UNDO tablespace in Oracle first create the new undo tablespace, then alter the database to use the new undo tablespace. This will require enough available disk space to create the new tablspace. Then drop the old tablespace to free up the disk. Commands are provided below

CREATE UNDO TABLESPACE undotbs2
DATAFILE '/u02/datafile/undotbs02.dbf'
         SIZE 50M AUTOEXTEND ON NEXT 50M;

Tablespace created.



ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
System altered.

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;