Decreasing the size of the UNDO tablespace

Decreasing the size of the UNDO tablespace

How can I decrease the size of the UNDO tablespace?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

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. 
    CREATE UNDO TABLESPACE undo_ts2
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

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top