Oracle Scratchpad

November 14, 2014

Shrink Tablespace

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 6:16 pm GMT 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 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.


  1. Nice little post, thanks for sharing!

    Comment by Ann — November 14, 2014 @ 7:03 pm GMT 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 GMT 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 looking at the option for dropping the old tablespace and renaming the new one to replace the original as the final stage of the process.

      Comment by Jonathan Lewis — November 17, 2014 @ 7:38 am GMT 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 GMT 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 GMT 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 GMT Nov 17,2014 | Reply

  5. […] be occasions when they extend far too much and you need to shrink them down again (and that’s not necessarily easy if it’s not the temporary tablespace). Conversely, if you think your data or temp files […]

    Pingback by Resumable | Oracle Scratchpad — October 7, 2019 @ 10:31 am BST Oct 7,2019 | Reply

  6. […] Thoughts on preparing to reduce tablespace (file) sizes […]

    Pingback by Fragmentation 1 | Oracle Scratchpad — March 2, 2021 @ 6:35 pm GMT Mar 2,2021 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

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

Facebook photo

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

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: