Oracle XE data limit reached - how to reduce tablespace size?

Question

I have an Oracle XE database with several tablespaces. One of these is used to store image data, and this tablespace has grown to a huge size. We decided that we didn't need to the images any more so deleted them all.

This has freed up a lot of space but the tablespace size is still huge. How can I reduce it back to the size of the data that's actually in it?

Answer 1

This is a bit tricky, you would find DATAFILE RESIZE as the most common answer. However, you need to take care of few things as mentioned by Jonathan Lewis to avoid ORA-03297: file contains used data beyond requested RESIZE value while resizing the datafile.

You need to first find the free space of the tablespace to make sure how much you could reclaim.

The following script by Tim Hall is handy:

SET PAGESIZE 140
COLUMN used_pct FORMAT A11

SELECT tablespace_name,
       size_mb,
       free_mb,
       max_size_mb,
       max_free_mb,
       TRUNC((max_free_mb/max_size_mb) * 100) AS free_pct,
       RPAD(' '|| RPAD('X',ROUND((max_size_mb-max_free_mb)/max_size_mb*10,0), 'X'),11,'-') AS used_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;

All that remains now is to resize the data files.

ALTER DATABASE DATAFILE '/directory/datafile.dbf' RESIZE value;