DB Wiki

set linesize 140
WITH Tablespace_Data as 
(
SELECT tablespace_name, ROUND(a.bytes/1024/1024) AS size_mb, 
                    ROUND(a.maxbytes/1024/1024) AS maxsize_mb, 
                    ROUND(b.free_bytes/1024/1024) AS free_mb, 
                    ROUND((a.maxbytes-a.bytes)/1024/1024) AS growth_mb, 
                    100 - ROUND(((b.free_bytes+a.growth)/a.maxbytes) * 100) AS pct_used 
FROM (SELECT tablespace_name, file_id, bytes, 
             GREATEST(bytes,maxbytes) AS maxbytes, 
             GREATEST(bytes,maxbytes)-bytes AS growth 
        FROM dba_data_files) a, 
     (Select file_id, SUM(bytes) AS free_bytes FROM dba_free_space GROUP BY file_id) b 
WHERE a.file_id = b.file_id(+)
)
SELECT tablespace_name, 
      sum(size_mb) SIZE_MB, 
      sum(maxsize_mb) MAXSIZE_MB, 
      sum(free_mb) FREE_MB, 
      sum(growth_mb) GROWTH_MB, 
      round((sum(size_mb)-sum(free_mb)) / sum(maxsize_mb)*100,2) PERCENTAGE_USED 
  from Tablespace_Data
group by tablespace_name;

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

alter system set undo_tablespace = 'UNDOTBS2';

DROP TABLESPACE undotbs1 INCLUDING CONTENTS AND DATAFILES;

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