DB Wiki

This is an old revision of the document!

Tablespaces

Changing Undo Tablespace

Create New Undo Tablespace

create undo tablespace UNDOTBS2 datafile '/u03/undotbs02.dbf' size 500M autoextend on next 50m maxsize 8G;

Switch databases to new undo tablespace

alter system set undo_tablespace = 'UNDOTBS2';

Drop old undo tablespace

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

Potential Issues

When doing the “drop undo tablespace” command you will almost certainly hit the following Oracle error …

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Use the following SQL to see which sessions are using it …

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' );

After a while these transactions will commit/rollback (hopefully)
Then, assuming undo_retention is not set too high, you will be able to remove the old undo tablespace.

Sometimes, after dropping the undo datafiles you will find the space has not been released properly when you do “df -h”.
If this is the case then the following command will show you which processes are holding a lock on this file

/usr/sbin/lsof +L1