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

Labels: dba, testing

  ¶ 9/25/2009 03:54:00 PM