Shrink UNDO tablespace

출처 : http://javamix.tistory.com/entry/Shrink-UNDO-tablespace

 

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