Ask the Expert

Decreasing the size of the UNDO tablespace

How can I decrease the size of the UNDO tablespace?

    Requires Free Membership to View

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.

This was first published in June 2005

There are Comments. Add yours.

TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: