Many a time situation comes, when undo tablespace becomes very big in size and due to space crunch , we need to recreate undo tablespace with appropriate size & drop old one.
This post provides u simple steps to perform this activity.
SQL> SHOW PARAMETER UNDO NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS1
Create new undo tablespace with appropriate size:
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/U01/ORACLE/DB1/ORADATA/UNDOTBS2_01.DBF' SIZE 50OM AUTOEXTEND ON MAXSIZE 30G;
Change undo_tablespace initialization parameter with new undo tablespace value
SQL>ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
SQL> SHOW PARAMETER UNDO NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ undo_management string AUTO undo_retention integer 900 undo_tablespace string UNDOTBS2
At this point all new transaction will start using new undo tablespace – (in our case UNDOTBS2) for holding undo values & we are ready to drop old undo tablespace – (in our case UNDOTBS1)
SQL> DROP TABLESPACE UNDOTBS1 INCLUDING CONTENTS AND DATAFILES;
This command can be successful, if your all transactions are either committed or roll backed else this command will error out giving following error.
ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use
Now u can use following command to find out session which are still using old undo tablespace. Either u can ask end users to commit or rollback their sessions or kill them gracefully.
SELECT A.NAME,B.STATUS , D.USERNAME , D.SID , D.SERIAL# FROM V$ROLLNAME A,V$ROLLSTAT B, V$TRANSACTION C , V$SESSION D WHERE A.USN = B.USN AND A.USN = C.XIDUSN AND C.SES_ADDR = D.SADDR AND A.NAME IN ( SELECT SEGMENT_NAME FROM DBA_SEGMENTS WHERE TABLESPACE_NAME = 'UNDOTBS1' );
once all sessions are cleared, u have to wait till time of undo_retention passes (in our case 15 minutes), then u can drop old tablespace.
Hope so this post will be useful for u 🙂
Cheers
Regards,
Adityanath
Categories: Administration
Adityanath's Oracle Blog 
Great and simple steps to follow, thanks for your great work.
Hello There,
Thanks for visiting and appreciate your feedback.:-)
Regards,
Adi
why do we need to drop and recreate please explain that first
Hello There,
Many a times situation comes, when undo tablespace becomes very big in size and due to space crunch on diskgroup or mount point , we need to recreate undo tablespace with appropriate size & drop old one.
Regards,
Adi
Hi Adi,
Is there any way to reclaim space manually from unexpired undo and how to check who is using unexpired undo.
Regards,
Eknath
Hello Eknath,
Following query can be used to check undo usage details:
set lines 300
SELECT u.tablespace_name tablespace,
s.username,s.sid||’,’||s.serial# sess,
u.status,
SUM (u.bytes) / 1024 / 1024 sum_in_mb,
COUNT (u.segment_name) seg_cnts
FROM dba_undo_extents u
LEFT JOIN v$transaction t
ON u.segment_name = ‘_SYSSMU’ || t.xidusn || ‘$’
LEFT JOIN v$session s
ON t.addr = s.taddr
GROUP BY u.tablespace_name, s.username, u.status,s.sid||’,’||s.serial#
ORDER BY 1, 2, 3;
Regards,
Adi
some truly prime blog posts on this web site, saved to fav.
very interesting points you have noted, thankyou for posting.
I’m not that much of a internet reader to be honest but your blogs really nice, keep it up! I’ll go ahead and bookmark your website to come back later on. Many thanks
Like!! Thank you for publishing this awesome article.
I got this website from my friend who shared with me concerning this website
and now this time I am visiting this site and reading very informative articles or reviews at this place.
Do you mind if I quote a few of your articles as long as I provide credit and sources back to your blog? My blog is in the very same area of interest as yours and my users would really benefit from a lot of the information you present here. Please let me know if this ok with you. Appreciate it!