How to delete data and free up disk space in Oracle DBMS 9i

oracleoracle9i

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.

Scenario:

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

Best Answer

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.

Related Topic