Recreating undo tablespace in oracle database

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s