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.
Printable View
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
sql>Select a.name,b.status
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;