Oracle Database: How To "Shrink" the UNDO Tablespace

-- Create "temporary" undo tablespace and make it the default undo tablespace

create undo tablespace UNDO_TMP datafile '+U01' size 2G AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

alter system set undo_tablespace=UNDO_TMP;

-- Drop the original undo tablespace

drop tablespace UNDOTS including contents and datafiles;

-- Recreate the old undo tablespace (smaler) and make it the default undo tablespace again

create undo tablespace UNDOTS datafile '+U01' size 2G AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED;

alter system set undo_tablespace=UNDOTS;

-- Drop the "temporary" undo tablespace

drop tablespace UNDO_TMP including contents and datafiles;