Recently I have faced a problem with our undo tablespace on production database. The database size is 2TB and the undotbs was growing very large also there were space constraints at the mountpoint level. So I came with the below idea and first implemented in the test environment and its worked out as I wished. Then I have implemented the same in production.
How to switch to a new UNDO tablespace and drop the old one on Oracle database (Version greater than 10g & 11g since the undo_management must be auto).
STEP -1
=======
$ sqlplus / as sysdba
SQL> show parameter undo
NAME TYPE VALUE
———————————— ———– ——————————
undo_management string AUTO
undo_retention integer 900
undo_tablespace string UNDOTBS1
SQL>
The current undo tablespace as suggested by the initialization parameter undo_tablespace is UNDOTBS1.
STEP -2
=======
— Create a new undo tablespace
CREATE UNDO TABLESPACE undotbs2
DATAFILE ‘/d01/apps/oradata/oraxpo/undotbs201.dbf’
SIZE 50M AUTOEXTEND ON NEXT 5M;
Tablespace created.
STEP -3
=======
— Switch the database to the new UNDO tablespace.
ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2 SCOPE=BOTH;
System altered.
STEP -4
=======
— Try to drop the tablespace but failed.
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use
With the alter system set undo_tablespace=UNDOTBS2, the database UNDO tablespace is changed and any new transaction’s undo data will go to the new tablespace i.e. UNDOTBS2. But the undo data for already pending transaction (e.g. someone initiated a transaction before the UNDO tablespace switch) is still in the old tablespace with a status of PENDING OFFLINE. As far as it is there you cannot drop the old tablespace.
STEP -5
=======
— The query shows the name of the UNDO segment in the UNDOTBS1 tablespace and its status.
Now lets see which users/sessions are running this pending transaction.
set lines 10000
column name format a10
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 = ‘UNDOTBS1’
);
NAME STATUS
———- —————
_SYSSMU8$ PENDING OFFLINE
column username format a6
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’
);
NAME STATUS USERNA SID SERIAL#
———- ————— —— ———————— ———- ———-
_SYSSMU8$ PENDING OFFLINE SCOTT 147 4
So this is SCOTT with SID=147 and SERIAL#=4. 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 UNDOTBS1 tablespace. (Don’t Kill the session in a production environment wait for the undo retention period)
SQL> alter system kill session ‘147,4’;
System altered.
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’
);
no rows selected
As we can see once the session is kills we don’t see anymore segments occupied in the UNDOTBS1 tablespace.
Lets drop UNDOTBS1.
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use
If you are retaining undo data then you still won’t be able to drop the tablespace because it is still in use by undo_retention. Let the UNDO_RETENTION time pass and then try to drop the tablespace. In my case it is 900 seconds i.e. 15 minutes.
— After 15 minutes.
SQL> DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.
Finny's Oracle Blog 