Friday, April 15, 2011

Shrink UNDO tablespace


Oracle 11g 10g 9i  tablespace database undo 


How to shrink the undo tablespace in Oracle? 

We need to create a tablespace temporary so that we can redirect undo usage to this temperorary tablespace. 


Steps:

1. Create a temporary tablespace for swapping

CREATE undo TABLESPACE undotbs2 DATAFILE
     '/dbora01/oradata/ORCL/ora02/undotbs2_01.dbf' size 100M;

2. Use it as default UNDO tablespace

ALTER SYSTEM SET undo_tablespace=undotbs2;

3. Drop the old UNDO tablespace

DROP TABLESPACE undo including contents;

4. Create back the original UNDO tablespace

CREATE undo TABLESPACE undo DATAFILE
     '/dbora01/oradata/ORCL/ora02/undo01.dbf' size 5000M REUSE,
     '/dbora01/oradata/ORCL/ora03/undo02.dbf' size 5000M REUSE;

5. Set the default UNDO tablespace back to the original one

ALTER SYSTEM SET undo_tablespace=undo;     

6. Drop the temporary one

DROP TABLESPACE undotbs2 including contents and datafiles;

7. End

No comments:

Post a Comment