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?

score 4 · Answer 1 · answered Jun 11 '15 at 11:21

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;

Thanks for your answer - so Oracle won't allow you to resize a tablespace too small? Are there any other risks associated with resizing tablespaces? — user1578653, Jun 11 '15 at 11:50
Go through the two links I gave, it explains the pros and cons. — Lalit Kumar B, Jun 11 '15 at 12:01

score 2 · Accepted Answer · answered Jun 11 '15 at 10:57

2

I would do a full database export, drop the tablespace, create a new tablespace with a smaller size and import back into it

answered Jun 11 '15 at 10:57

davegreen100

2,055
3
13
24

Thanks for your answer - this is the approach I used in the end. It was a lot simpler than having to defragment and resize the tablespace. – user1578653 Jun 12 '15 at 13:43

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

2 Answers2