Problem solve Get help with specific problems with your technologies, process and projects.

Decreasing the size of the UNDO tablespace

How can I decrease the size of the UNDO tablespace?

You can try to shrink the datafile of the UNDO tablespace. Query DBA_DATA_FILES to determine the name of the datafiles of the UNDO tablespace you are using. When you find the file name, you can issue a command similar to the following:

ALTER DATABASE DATAFILE '/directory/undo_ts01.dbf' RESIZE xxxM;
Supply the file name in the above command. The new size should be smaller than your older datafile size.

It would not be surprising to find that an error is raised when this operation occurs. You can only release space at the end of the datafile if it is free. It may be likely that this space has been allocated by an undo segment. If that is the case, then your alternative is to create a new UNDO tablespace and point the database to use it. The following steps should do the trick!

  1. Create a new UNDO tablespace.
    DATAFILE '/directory/undo_ts2_01.dbf' SIZE xxxM;
  2. Modify the database parameter to use the new UNDO tablespace.
    ALTER SYSTEM SET undo_tablespace=undo_ts2 SCOPE=BOTH;
New transactions will begin using the new undo tablespace. After some time passes (at least the number of seconds specified by the UNDO_RETENTION initialization parameter), you can drop the old UNDO tablespace. The downside to this approach is that for a short time period, your database will have two UNDO tablespaces.

Dig Deeper on Oracle database design and architecture

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.