Recovering disk space from Oracle

Where has the disk space gone
To find out which tables are the biggest you can run this query:

SELECT
owner, segment_name, segment_type, tablespace_name,
SUM (BYTES / 1024 / 1024) sizemb
FROM dba_segments
GROUP BY owner, segment_name, segment_type, tablespace_name
ORDER BY sizemb DESC

Recovering disk space
Most of the following was learned from this forum post. As tables grow, it can be necessary to compact them to free up disk space. Deleting unnecessary rows won't necessarily free disk space. The space occupied by those rows just becomes available for new rows to consume.

To free up space, you need to truncate (or drop and re-create) the table. If there are constraints referencing some of the rows in the table you want to shrink then you'll have to disable those first.

So, assuming:

• you want shrink table FOO


• you've deleted the rows you don't need from FOO


• table BAR has constraints referencing FOO

-- copy those rows you want to keep into a new table
CREATE TABLE temp_storage_table AS SELECT * FROM foo;
-- disable constaints so you can truncate
ALTER TABLE bar DISABLE CONSTRAINT foo_fk01;
-- truncate which will release the disk space
TRUNCATE TABLE foo;
-- copy the rows you want to keep back into the original table
INSERT INTO foo SELECT * FROM temp_storage_table;
-- re enable constraints
ALTER TABLE bar ENABLE CONSTRAINT foo_fk01;
-- remove temp data
DROP TABLE temp_storage_table;

Try this out on a test table first to make sure everything is working as expected.