If you start moving objects around to try and reclaim space in a tablespace there are all sorts of little traps that make it harder than you might hope to get the maximum benefit with the minimum effort. I’ve written a couple of notes in the past about how to proceed and, more recently, one of the difficulties involved. This is just a brief note about a couple of ideas to make life a little easier.
- Check that you’ve emptied the recyclebin before you start
- Before you try moving/rebuilding an object check that the total free space “below” that object is greater than the size of the object or you’ll find that parts of the object move “up” the tablespace.
- Before moving a table, mark its indexes as unusable. If you do this then (in recent versions of Oracle) the default behaviour is for the index space be freed as the segment vanishes and you may find that the extents of the table can move further “down” the tablespace. (If you’ve kept tables and indexes in different tablespaces this is irrelevant, of course).
- When you move an object think a little carefully about whether specifying an minimum initial extent size would help or hinder the move.
- Don’t assume that moving the “highest” object first is the best strategy – work out where you expect the final tablespace HWM to be and you may find that it makes more sense to move other objects that are above the point first.
- Moving objects with several small (64KB) extents first may allow you to free up larger (1MB, 8MB) gaps that can be used by other larger objects
- Creating a new tablespace and moving objects to it from the old tablespace “top down” may be the quickest way to proceed. Work towards dropping the old tablespace HWM regularly.