Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I would like to know how usually one would delete data from an Oracle 9i DBMS which actually frees up disc space. Past scenario, we have had cases where clearing up 1-2 million rows of data does not translate to a decrement in disc space usage.


sqlplus > delete from audit_log where date_created between today and the day before;

sqlplus > 2 million records deleted.

bash$: du -sh (after issuing the delete above)

Results in no change to the disc space usage.

Which also brings me to the question, will one need to flush anymore tables in order for oracle to completely delete all the data that is supposedly deleted by the delete DML

share|improve this question

migrated from stackoverflow.com Apr 29 '11 at 6:37

This question came from our site for professional and enthusiast programmers.

DELETE only removes records from the table; it never deallocates the segments from the data files, and never coalesces empty space within the data files. –  Jeffrey Kemp Apr 29 '11 at 3:49
What do you mean "supposedly"? In what way do you think that DELETE doesn't delete the data? (i.e. DELETE+COMMIT removes the records from the table so that subsequent (non-flashbacked) queries will not return those records) –  Jeffrey Kemp Apr 29 '11 at 3:52
Hi Jeffrey, the delete DML does delete the data, however not the disk space that one would suppose it freed, hence "supposedly", my bad. –  Oh Chin Boon Apr 29 '11 at 8:07

3 Answers 3

up vote 6 down vote accepted

In Oracle deleting rows from a table won't automatically release any disk space. It is possible to release disk space but to accomplish this you have to find out how the tables are physically placed in the datafiles. As soon as a datafile has empty blocks on the end, you can resize the datafile to a smaller size. Only after this - successful - operation you get real disk space back. If you have got a lot of empty blocks in a datafile but not on the end, it might be easiest to move the tables from the tablespace to which the datafile belongs into a new tablespace and drop the old tablespace. This won't work for the SYSTEM tablespace, you are not allowed to move SYS objects to an other tablespace.

Sometimes you are lucky when you can move just one - small - table that happened to block releasing space because it was placed at the end of a datafile. In that case a simple alter table thesmalltable move; will relocate that table and make reclaimable space at the end of the datafile[s]. After that, alter database datafile '/your/df/name.dbf' resize the_new_size; releases disk space.

A table is logically placed in a tablespace. A tablespace consists of minimal 1 datafile, in many cases multiple datafiles.

share|improve this answer
Hi ik_zelf, thank you for your thoughts. One question, does the whole operation you described introduce any downtime? –  Oh Chin Boon Apr 29 '11 at 8:08
There will be downtime[s] mainly caused by the rebuild of indexes that became unusable during table move[s]. A lot of downtime can be prevented using dbms_redefinition but not all, there will at least be short outages. –  ik_zelf May 1 '11 at 9:04

To complete other answers, purging diag logs can help you free some significant disk space (up to several GBs).

Check this : http://www.databasejournal.com/features/oracle/article.php/3875896/Purging-Oracle-Databases-Alert-Log-with-ADRCI---Usage-and-Warning.htm

Basically, you will run the adrci Oracle command line utility, then do:

adrci> set homepath diag/rdbms/yourinstance/yourinstance
adrci> purge -age 10080

The example above will remove diag traces older than 1 week.

As well, check if you have locally managed tablespaces (LMT) or directory managed tablespaces (DMT). The former is supposed to handle fragmentation better (however far from perfect). Check: http://www.orafaq.com/node/3

share|improve this answer

Try this command to get some space back:

DUMP TRAN <database name> WITH NO_LOG

I think the best way to get hard disk space back is by removing any other no longer used files from your hard disk.

The usual way to delete data from the database is executing the delete command, however that will not necessarily free up disk space.

share|improve this answer
I don't think DUMP TRAN is a valid command in Oracle. –  Jeffrey Kemp Apr 29 '11 at 3:42

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.