The datafile for UNDO tablespace can’t be shrunk as we had issue where the datafile was set to unlimited and it kept on growing to fix the issue one can do the following steps.
— UNDO_RBS1 is new undo tablespace name
SQL> create undo tablespace UNDO_RBS1 datafile ‘/u03/oradata/TEST/undorbs1.dbf’ size 1000m;
— make the new tablespace to be the undo tablespace
SQL> alter system set undo_tablespace=undo_rbs1;
— get the filename of the old undo tablespace which will be dropped so you can remove the file
SQL> SELECT file_name FROM dba_data_files WHERE tablespace_name = ‘UNDO_RBS0’;
FILE_NAME
——————————————————————————–
/u03/oradata/TEST/undotbs0_rbs1.dbf
— drop the undo tablespace which has the unlimited datafile, if there is an active transaction in the undo tablespace then it will not be able to drop the tablespace so one can check and monitor for active transactions that are running
SQL> drop tablespace undo_rbs0;
— once the tablespace is dropped the file can be then be deleted
SQL> !rm /u03/oradata/TEST/undotbs0_rbs1.dbf
An Oracle Spin by Alex Lima 
Wonderfully concise and correct solution. Thank you.
I have been searching for clear steps to achieve this objective and this is by far the most straight forward.. Thanks Mate!
Works like a charm ! Thanks!
drop tablespace including contents and datafiles;
but current running session use old undo tablespace than how to switch undo tablespace data in new undo tablespace
Create the UNDO tablespace with new name and then change the parameter “UNDO_TABLESPACE” so new transactions will use the new tablespace. You can’t change the undo tablespace of sessions that are running before you made the change but over sometime the old undotablespace won’t be in use.
If don’t rechange undo tablespace in previous name than any impact or problem in DB ?
how to delete my kik account forever
How to shrink UNDO tablespace? « Oracle Spin – Flimatech Blog