Friday, September 25, 2009

"Shrink" UNDO Tablespace

Having completely screwed up my sandbox database, I decided to do a little house cleaning. Yes, I know, I would probably break something. But I have backups now.

Specifically, I wanted to reclaim some space. More specifically, I didn't like the UNDO data file being 6GB. Completely unnecessary.



So I found this thread on how to "shrink" it.

1. Create a new temporary UNDO tablespace.
2. Point your database to the new UNDO tablespace.
3. Drop the original UNDO tablespace.
Optional
4. Create new UNDO tablespace matching the original.
5. Point your database to the old new UNDO tablespace.
6. Drop the new old UNDO tablespace.
7. Remove the old files from the filesystem
--STEP 1
CREATE UNDO TABLESPACE undotbs2 DATAFILE 'UNDOTBS_02.DBF'
SIZE 1G
AUTOEXTEND ON;

--STEP 2
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs2;

--STEP 3
DROP TABLESPACE undotbs INCLUDING CONTENTS;

--STEP 4
CREATE TABLESPACE undotbs DATAFILE 'UNDOTBS_01.DBF'
SIZE 1G
AUTOEXTEND ON;

--STEP 5
ALTER SYSTEM SET UNDO_TABLESPACE = undotbs;

--STEP 6
DROP TABLESPACE undotbs2 INCLUDING CONTENTS;
And then you can remove the old datafiles.

Just a reminder, this is a sandbox used for testing, not a production database.

3 comments:

Boneist said...

If you don't want your UNDO tablespace to reach large sizes, wouldn't it be a better idea to set a maxsize if you're going to autoextend?

oraclenerd said...

boneist,

probably...but that would require me to think a little bit. :)

i am working to understand the ins and outs of this whole "DBA" thing...right now, i'm trying to concentrate on the testing portion though.

Aman Sharma said...

The mechanisms of 10g and above always make the undo tablespace look completely filled up. Its not a bug but a documented thing. Also the shrinking may fail if the Undo tablespace would contain "active transactions"!

The best would be to keep a check over the size occupied by the datafile by setting the maximum size for it and not making it autoextensible( as mentioned by Boneist)