DB Wiki

SET PAGESIZE 140 LINESIZE 200
 
COLUMN used_pct FORMAT A11
COLUMN size_mb FORMAT 999,999
COLUMN free_mb FORMAT 999,999
COLUMN max_size_mb FORMAT 999,999
COLUMN max_free_mb FORMAT 999,999
 
SELECT tablespace_name,
       size_mb,
       free_mb,
       max_size_mb,
       max_free_mb,
       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct
FROM   (
        SELECT a.tablespace_name,
               b.size_mb,
               a.free_mb,
               b.max_size_mb,
               a.free_mb + (b.max_size_mb - b.size_mb) AS max_free_mb
        FROM   (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS free_mb
                FROM   dba_free_space
                GROUP BY tablespace_name) a,
               (SELECT tablespace_name,
                       TRUNC(SUM(bytes)/1024/1024) AS size_mb,
                       TRUNC(SUM(GREATEST(bytes,maxbytes))/1024/1024) AS max_size_mb
                FROM   dba_data_files
                GROUP BY tablespace_name) b
        WHERE  a.tablespace_name(+) = b.tablespace_name
       )
ORDER 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

 CREATE TEMPORARY TABLESPACE TEMP2 TEMPFILE '/u01/oradata/DB1/temp99.dbf' SIZE 100M; 

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP2; 

 DROP TABLESPACE TEMP INCLUDING CONTENTS AND DATAFILES; 

If this command hangs then there are probably sessions still using the old temp tablespace.
Use the following SQL to find the sessions …

 SELECT USERNAME, SESSION_NUM, SESSION_ADDR FROM V$SORT_USAGE;
 SELECT SID, SERIAL#, STATUS FROM V$SESSION WHERE SERIAL#="SESSION_NUM" AND SADDR="SESSION_ADDR";

 CREATE TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/oradata/DB1/temp01.dbf' SIZE 100M; 

 ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP; 

 DROP TABLESPACE TEMP2 INCLUDING CONTENTS AND DATAFILES; 

To discover what the default temporary tablespace is …

 SELECT PROPERTY_VALUE FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE'; 

ALTER TABLESPACE USERS ADD DATAFILE '/oradata/user02.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE 1G;
ALTER DATABASE DATAFILE '/oradata/user02.dbf' RESIZE 500M;

SELECT * FROM database_properties WHERE property_name LIKE '%TABLESPACE';

-- Report on which data files can be shrunk
SET verify off pagesize 50 linesize 120
COLUMN file_name format a60 word_wrapped
COLUMN smallest format 999,990 heading "Smallest|Size|Poss."
COLUMN currsize format 999,990 heading "Current|Size"
COLUMN savings  format 999,990 heading "Poss.|Savings"
break ON report
compute SUM OF savings ON report
COLUMN VALUE new_val blksize
SELECT file_name,
       CEIL( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,
       CEIL( blocks*&&blksize/1024/1024) currsize,
       CEIL( blocks*&&blksize/1024/1024) -
       CEIL( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings
FROM dba_data_files a,
     ( SELECT file_id, MAX(block_id+blocks-1) hwm
         FROM dba_extents
        GROUP BY file_id ) b
WHERE a.file_id = b.file_id(+) 
ORDER BY savings DESC;