How To free up Disk Space used by Oracle

The time comes when your Oracle database server has gobbled up a lot of space on the hard disk and you wonder why it still uses so much disk space after having deleted a lot of data from your tables. There is an easy way to shrink the size of Oracle’s tablespaces.

How much disk space can I save?

Simply execute the following statement to have a look at how much space your tablespaces currently occupy and how much space you could save. The statement provides you with the current size of each tablespace as well as the size you could shrink it to and the space you would save when shrinking it to that size:

select file_name,
ceil( (nvl(hwm,1)*8192)/1024/1024 ) SHRINK_TO,
ceil( blocks*8192/1024/1024) CURRENT_SIZE,
ceil( blocks*8192/1024/1024) -
ceil( (nvl(hwm,1)*8192)/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(+)

Save the space!

Then run the following statement to shrink a tablespace to a specified size.

alter database datafile '/path/to/data/file.dbf' resize 100m;


I found this very helpful hint at stackexchange:


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s