Recreating undo tablespace in oracle database

Published on

May 30, 2014

3,305 hits

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

12 responses to “Recreating undo tablespace in oracle database”

Thomas

August 15, 2018

Great and simple steps to follow, thanks for your great work.

Reply
1. Adityanath Dewoolkar
  
  August 21, 2018
  
  Hello There,
  
  Thanks for visiting and appreciate your feedback.:-)
  
  Regards,
  Adi
  
  Reply
Roger

November 3, 2018

why do we need to drop and recreate please explain that first

Reply
1. Adityanath Dewoolkar
  
  November 7, 2018
  
  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
  
  Reply
Eknath

December 7, 2018

Hi Adi,

Is there any way to reclaim space manually from unexpired undo and how to check who is using unexpired undo.

Regards,
Eknath

Reply
1. Adityanath Dewoolkar
  
  December 7, 2018
  
  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
  
  Reply
Kelsey Kazeck

March 2, 2019

some truly prime blog posts on this web site, saved to fav.

Reply
Ashleigh Cribari

March 6, 2019

very interesting points you have noted, thankyou for posting.

Reply
Paulene Salsbury

November 8, 2019

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

Reply
ปั้มไลค์

July 11, 2020

Like!! Thank you for publishing this awesome article.

Reply
Emma

March 9, 2021

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.

Reply
Norman Sellner

August 23, 2021

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!

Reply