On Oracle version 11g:
After Googling, I can't find a simple way to reclaim free space after deleting a table.
I've found a lot of explanations, telling how the datafile becomes fragmented, the big stack of boring queries you have to run in order to move the "empty space" at end of the datafile (table by table… even when you have 200 tables !?).
Then you have to reduce the datafile size by "guessing" by how much you can reduce it, or you must know exactly what is your "block size"… And finally you should not forget to "rebuild the indexes".
See for example : http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:54178027703899
and http://www.oracle-base.com/articles/misc/ReclaimingUnusedSpace.php
Is there a simple PL/SQL procedure which, given a tablespace name or datafile name, would to that job? Or any similar Oracle tool?
Best Answer
The short answer is No. Unfortunately the way to do this in Oracle does require the "big stack of boring queries". The articles you linked to are some of the best information available on the subject. The datafile does indeed become fragmented, so that even if free space exists below the highest segment, Oracle will not automatically consolidate it when a
RESIZE
is done.To "defragment" the tablespace you need to move these segments to the start of the datafile rather than at the end. For tables this is an offline process meaning the table will be unavailable while the move is taking place. Indexes can be moved either offline or with Enterprise Edition they can be moved online. Since you have an outage window I recommend you follow these steps.
A. Shrink datafiles with free space beyond the high water mark. This can be done as follows (the query is similar to Frosty Z's procedure):
B. After shrinking things above the high water mark, find out what tablespaces would still benefit from having segments moved.
C. For each of these tablespaces determine which segments need to be moved. (Replace USERS with the name of your tablespace or join it with the previous query)
D. Move each table and rebuild the indexes and statistics.
E. Repeat step A.
I just built most of these queries, so you will want to thoroughly test them before use. I suppose you could create a procedure that would use
EXECUTE IMMEDIATE
to create the actual statements to run dynamically, but because queries will receive ORA-08103: Object no longer exists while the move is in progress, I think it is best to control that process manually even if it does mean a bit more time/effort.