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

Question

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

Solution 1

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.

Solution 2

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

Comments

[Jeffrey Kemp]

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]

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)

[Oh Chin Boon]

Hi Jeffrey, the delete DML does delete the data, however not the disk space that one would suppose it freed, hence "supposedly", my bad.

[Jeffrey Kemp]

I don't think DUMP TRAN is a valid command in Oracle.

[Oh Chin Boon]

Hi ik_zelf, thank you for your thoughts. One question, does the whole operation you described introduce any downtime?

[ik_zelf]

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.