Q

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

Dig deeper on Oracle database design and architecture

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close