Oracle Scratchpad

November 14, 2014

Shrink Tablespace

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 6:16 pm BST Nov 14,2014

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.

6 Comments »

  1. Nice little post, thanks for sharing!

    Comment by Ann — November 14, 2014 @ 7:03 pm BST Nov 14,2014 | Reply

  2. “Creating a new tablespace and moving objects” – my favourite approach, however it is important not to forget changing default segment attributes on partitioned tables (at table level for partitions and partition level for subpartitions) if “old” tablespace is dropped. Unfortunately, oracle does not warn regaridng this incosistency and developers could be quite unhappy when their code fails while trying to add/split a (sub)partition.

    Comment by Pavol Babel — November 14, 2014 @ 8:04 pm BST Nov 14,2014 | Reply

    • Pavol,

      That’s a useful reminder – the type of detail that is easy to overlook if the exercise is performed as a one-off.
      In recent versions I would suggest reviewing the option for dropping the old tablespace and rename the new one to the replace the original as the final stage of the process.

      Comment by Jonathan Lewis — November 17, 2014 @ 7:38 am BST Nov 17,2014 | Reply

  3. I’ve got a TS_REORG tablespace set aside for this very purpose.
    This is one of the DBA wishlist items that has apparently never reached the ears of RDBMS development.
    As in: a simple command variation for ALTER TABLESPACE that goes and compacts the blessed things!
    But of course: anyone asking for this must be a “bad dba”, so it never got any priority…

    Comment by Noons — November 15, 2014 @ 11:56 am BST Nov 15,2014 | Reply

    • Noons,

      Probably not as straightforward (in general) as you would expect it to be. What should the mechanism do (for example) if it can’t rebuild an index which started in a different tablespace from the one being rebuilt, but was an index on a table that tablespace ? I think very recent versions of Oracle reduce the potential for problematic side effects that such a feature might introduce – but I wouldn’t be surprised if it were seen as something that would cause more problems than it would cure.

      Comment by Jonathan Lewis — November 17, 2014 @ 7:43 am BST Nov 17,2014 | Reply

  4. Thanks a lot Jonathan for sharing it. Reading this post at the right time as i was following the approach “Creating a new tablespace and moving objects” which was much easier. Good thing i learned in this is i have to make the index unusable first.

    Comment by baskar — November 17, 2014 @ 7:59 am BST Nov 17,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.