Undo tablespace shrink

If your Undo tablespace is overgrown and you need to shrink it. This can be done by creating a new Undo tablespace, start using it and remove the old one after that. Here is example:

----

Create new Undo tablespace with suitable size
SQL> create undo tablespace undotbsnew datafile '<path_to_the_datafiles_usually same_where current_undo_datafile_is>\UNDOTBSNEW.DBF'
size 1024m autoextend on next 5120K maxsize 32767M;

Set new tablespace as current Undo tablespace
SQL> alter system set undo_tablespace= UNDOTBSNEW scope=both;

Drop the old tablespace
SQL> drop tablespace UNDOTBS1 including contents;

-Don't use "and datafiles" clause in drop tablespace instead of that clear old Undo datafiles from OS side (Read my earlier "Cleaning Oracle logs / trace files / datafiles on the fly. " post for more information about this).

In Windows make empty text file test.txt
copy test.txt UNDOTBS1.DBF
In Linux:ssa do following:
cat /dev/null > UNDOTBS1.DBF

After you can restart database you can remove these old Undo datafiles from OS. Before database restart OS does not let you do it.


And after you have removed old Undo datafiles you can then recreate Undo tablespace with old name. But usually rhere is no need for that.

And remember that if you use auto extended Undo tablespace don't do to big DML operations without frequent commits. If you do you can get really big Undo tablespaces and other problems.

----