Shrink Tablespace

If you start moving objects around to try and reclaim space in a tablespace there are all sorts of little traps that make it a little difficult to get the maximum benefit for 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
• If you’re trying to shrink the sysaux tablespace check the latest details on v$sysaux_occupants for threats.
• Before you try moving or rebuilding an object check that the total free space “below” that object is greater than  the size of the object or you may find that some extents are allocated for the rebuild further “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 to be freed as the segment vanishes (thanks to “deferred segment creation”) and you may find that the extents of the table can move further “down” the tablespace as a consequence.  (If you’ve been creating tables and indexes in different tablespaces this may be irrelevant, of course)
• When you move an object think a little carefully about whether specifying a 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 that point first. Also, there’s no point in rebuilding an index if you’re going to move its underlying table a little later.
• If you’re using system-allocated (rather than uniform) extents, moving objects with several small (64KB) extents first – even if they are below the target HWM – 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 working from the top downwards may be the most effective way to proceed.  Work towards dropping the old tablespace HWM regularly as you go through this procedure. If you can do this it’s also worth asking whether all the objects should have been in a single tablespace anyway.