I am sure you might have seen loads of information regarding how to resize/drop/recreate UNDO tablspace but point to note during dropping (ex-default/previous) UNDO tablespace is to check a view dba_undo_extents to see if any undo statments is still used by any session or needed for flashback. Following SQL should tell you if you can go ahead and drop the ex-default/previous UNDO tablespace or not (assuming undo tablespace I want to drop is UNDOTBS2),
SELECT tablespace_name, status, COUNT (*)
FROM SYS.dba_undo_extents
WHERE tablespace_name = 'UNDOTBS2'
GROUP BY tablespace_name, status
What is of our interest is status column of dba_undo_extents, 3 possible values - ACTIVE, EXPIRED, UNEXPIRED. What are the meanings ?
ACTIVE means that this undo segment contains active transactions
EXPIRED means that this segment is not rqeuired at all after considering Undo retention period
UNEXPIRED means that this segment does not contain any active transactions but it contains transactions which are still required for Flashback option (after considering Undo retention period).
So if you have all extents EXPIRED then go ahead and drop the tablespace, as usual make sure that you have done proper backup before and after.
Subscribe to:
Post Comments (Atom)
Posts
3 comments:
After reading this blog, its my sincere feeling that you own the exact idea of what are you deliberating.I seriously wish to congratulate you for zeal for the work you own that has made you stand today at this stage.Genuinely,there has not been even once that I have visited your post and went without getting some good knowledge.Get Going.And yes i have bookmarked your site dbatips4u.blogspot.com .
I like it very much!
Post a Comment