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

8 thoughts on “How to shrink UNDO tablespace?

  1. I have been searching for clear steps to achieve this objective and this is by far the most straight forward.. Thanks Mate!

    • 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.

  2. Pingback: how to delete my kik account forever

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s