How to shrink UNDO tablespace?
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
