Remove UNDO tablespace
How can I delete the existing UNDO tablespace and create a new one? I have tried creating a new one, and then offline the default UNDO, but it says its current in use. I'm using OEM. Thanks.
After creating the new undo tablespace
Alter system set undo_tablespace = xxx;
If there are any transactions using the old undospace let it continue and once these transactions are completed u can drop the old one.
To determine any pending transaction use the following query
from v$rollname a,v$rollstat b
where a.name IN ( select segment_name
from dba_segments where tablespace_name = 'undotbs')
and a.usn = b.usn;
Click Here to Expand Forum to Full Width