Managing Undo Table Spces
Managing Undo Table Spces
Managing Undo Table Spces
How to switch the database to a new UNDO tablespace and drop the old one
SELECT a.name,b.status
FROM v$rollname a,v$rollstat b
WHERE a.usn = b.usn
AND a.name IN (
SELECT segment_name
FROM dba_segments
WHERE tablespace_name = 'UNDOTBS02'
);
NAME STATUS
---------- ---------------
_SYSSMU8$ PENDING OFFLINE
The above query shows the name of the UNDO segment in the UNDOTBS02
tablespace and its status. Now lets see which users/sessions are running this
pending transaction.
So this is KUSUMA with SID=31 and SERIAL#=27 Since we know now the user,
we can go to him/her and request to end the transaction gracefully i.e. issue a
ROLLBACK or COMMIT. However, if this is not possible (say the user initiated
the transaction and left for annual leave :) and trust me this happens) you may
go ahead and kill the session to release the undo segments in the UNDOTBS02
tablespace.
This query lists information about undo segments in the SIUE DBORCL database.
Note the two segments in the SYSTEM tablespace and the remaining segments in
the UNDO tablespace.