Oracle - Reclaim disk space

Oracle does not release disk space even if you had delete the data or tablespace. If you have enterprise manager, you should use it to reclaim waste space.

You could do it manually but it will be troublesome. Below is a very simple steps to reclaim disk space used by Oracle if

1. You had added a continuous chunk of data

2. You had deleted the same continuous chunk of data added in point 1 and you had not added any data in between.

The reason being is you cannot shink and release space if free space are in between data.

If you are sure that you had met the above condition, do the following with sqlplus

1. Set column format so that the print is nice

COLUMN name FORMAT A50

2. Find the datafile and it used space

SELECT name, bytes/1024/1024 AS size_mb FROM v$datafile;

NAME                                                  SIZE_MB
-------------------------------------------------- ----------
/u01/app/oracle/oradata/users.dbf        10000

3. Shink the data file.

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/XE/users.dbf' RESIZE 1000M;

The final command is the trick and is pretty safe. If you try to shink a size which contain used data, it will throw an error

ORA-03297: file contains used data beyond requested RESIZE value

Reference:

1. https://oracle-base.com/articles/misc/reclaiming-unused-space#manual_tablespace_reorganization