Reduce size or recreate undo tablespace

[richyip]

Hi,

I notice that my UNDO datafile has grown from 200MB to 1GB. I tried to reduce the size but it doesn't allow. How can I strink the file back to 200MB or drop and recreate back? I'm not allow to offline the tablespace and thus cannot delete.

[jovery]

To recreate:

1. Create a new undo tablespace
2. Change the 'undo_tablespace' parameter to the new tablespace name.
3. Drop the old tablespace (obviously you will have to have no active transactions in this tablespace before you can do this)

[richyip]

jovery,

For no.2, how to change the 'undo_tablespace' parameter to new tablespace? Tks.

[akhadar]

Alter system set undo_tablespace = new undo_tablespace