I’ve just seen the following question on OTN:
I had a log table which was about 30G in Production, since it was growing in size we decided to drop it. After dropping it we can’t see the space being freed. Even at OS level we can’t see any reduction in filesize.Can you please explain and/or help in this?
This prompted me to come up with the following analogy.
I’ve got a case of wine in the boot (trunk) of my car. I’ve just taken six bottles out of the case, but I don’t see any extra free space in the boot. Why not ?
The analogy works on two levels:
Level 1: Call the boot the O/S, the case an Oracle datafile, and each bottle a table (or index, or other segment), and you’ve answered the original question.
Level 2: Call the boot an Oracle datafile, the case a table, and each bottle a row, and you’ve answered the equally common question: “I’ve just deleted 10M rows from a table, but dba_free_space doesn’t show any extra free space, why not ?”
Footnote for pedants: I have heard of tablespaces and I do know that you can have multiple datafiles per tablespace – but that makes it harder to find a tidy analogy.
Footnote on how to reclaim space: If you need to reclaim space, you have to start by making sure that the “potential” space is at the end of the object.
For datafiles, you probably have to move tables and rebuild indexes to fill the empty space lower down the data file so that the end of the file is empy and the file can be resized downwards (alter database datafile … resize). It’s a tedious job and could be bad for your database.
For tables you either have to rebuild the table and all its indexes (possibly using the dbms_redefinition package) or use the newer “shrink” option which moves rows from the end of the table into space nearer the start of the table and (optionally)drops the high water mark. Either strategy could be bad for your database.