Monday, December 4, 2017

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

No comments:

Post a Comment

Oracle - ORACLE error 959 encountered when importing with imp

If the dump is exported with imp, you cannot use impdp and you may face issue such as IMP-00003: ORACLE error 959 encountered ORA-009...