Oracle Scratchpad

September 19, 2014

Shrink Tablespace

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 12:10 pm BST Sep 19,2014

A recent question on the OTN database forum raised the topic of returning free space in a tablespace to the operating system by rebuilding objects to fill the gaps near the start of files and leave the empty space at the ends of files so that the files could be resized downwards. There are a few problems with this strategy – including the fact that the movement towards the start of file is not guaranteed in the documentation and the algorithms for system-allocated (as opposed to uniform) extents introduce irritating side effects.

This isn’t a process that you’re likely to need frequently, but I have written a couple of notes about it, including a sample query to produce a map of the free and used space in a tablespace. While reading the thread, though, it crossed my mind that recent versions of Oracle introduced a feature that can reduce the amount of work needed to get the job done, so I thought I’d demonstrate the point here.

When you move a table its indexes become unusable and have to be rebuilt; but when an index becomes unusable, the more recent versions of Oracle will drop the segment. Here’s a key point – if the index becomes unusable because the table has been moved the segment is dropped only AFTER the move has completed. Pause a minute for thought and you realise that the smart thing to do before you move a table is to make its indexes unusable so that they release their space BEFORE you move the table. (This strategy is only relevant if you’ve mixed tables and indexes in the same tablespace and if you’re planning to do all your rebuilds into the same tablespace rather than moving everything into a new tablespace.)

Here are some outputs demonstrating the effect in a database. I have created (and loaded) two tables in a tablespace of 1MB uniform extents, 8KB block size; then I’ve created indexes on the two tables. Running my ts_hwm.sql script I get the following results for that tablespace:

------- ----------- ----------- ---------- --------------- ------------------            
      5         128         255 TEST_USER  T1              TABLE 
                256         383 TEST_USER  T2              TABLE
                384         511 TEST_USER  T1_I1           INDEX
                512         639 TEST_USER  T2_I1           INDEX
                640      65,535 free       free

Notice that it was a nice new tablespace, so I can see the two tables followed by the two indexes at the start of the tablespaces. If I now move table t1 and re-run the script this is what happens:

alter table t1 move;

------- ----------- ----------- ---------- --------------- ------------------
      5         128         255 free       free
                256         383 TEST_USER  T2              TABLE
                384         511 free       free
                512         639 TEST_USER  T2_I1           INDEX
                640         767 TEST_USER  T1              TABLE
                768      65,535 free       free

Table t1 is now situated past the previous tablespace highwater mark and I have two gaps in the tablespace where t1 and the index t1_i1 used to be.

Repeat the experiment from scratch (drop the tables, purge, etc. to empty the tablespace) but this time mark the index unusable before moving the table and this is what happens:

------- ----------- ----------- ---------- --------------- ------------------
      5         128         255 free       free
                256         383 TEST_USER  T2              TABLE
                384         511 TEST_USER  T1              TABLE
                512         639 TEST_USER  T2_I1           INDEX
                640      65,535 free       free

Table t1 has moved into the space vacated by index t1_i1, so the tablespace highwater mark has not moved up.

If you do feel the need to reclaim space from a tablespace by rebuilding objects, you can find that it’s quite hard to decide the order in which the objects should be moved/rebuilt to minimise the work you (or rather, Oracle) has to do; if you remember that any table you move will release its index space anyway and insert a step to mark those indexes unusable before you move the table you may find it’s much easier to work out a good order for moving the tables.


I appreciate that some readers may already take advantage of the necessity of rebuilding indexes by dropping indexes before moving tables – but I think it’s a nice feature that we can now make them unusable and get the same benefit without introducing a risk of error when using a script to recreate an index we’ve dropped.


  1. I’ve found when trying to do reorgs like these is that it’s impossible to convince Oracle not to use space that exists in the tablespace already. This tip might be useful to prevent a tablespace from growing further during some table moves that were needed anyway (for whatever reason), but if you’re doing it for the purpose of shrinking the datafiles afterward, it’s too easy to Oracle to choose to use the space you were hoping to reclaim.

    I’m specifically talking in the context of “removing” data files, meaning shrinking them down to where they’re too small for Oracle to use (unless Oracle is allowing us to drop unused files from a tablespace now?). Maybe it’s different if you’re trying to shrink all available data files equally, though I don’t remember noticing any tendency of Oracle to pick locations early in the files for its new extents.

    The last time I worked at this, I came up with the idea of creating an empty table and manually allocating extents in particular data files, then as I moved tables or indexes out of the way of the targeted file, I’d add more extents from those files. This actually worked for uniform-sized extents, but with system-managed extent sizes I stumbled over some very interesting Oracle bugs around ALTER TABLE ALLOCATE EXTENT…

    Maybe you have the ability to Vulcan mind-meld with the database to get it to locate its extents where you want (after all, you are an alien of extraordinary ability ;) )… but for all I can see, moving the tables with index segments gone makes it a bit more likely that the table would reuse that particular space, but how can you be sure it won’t just go towards the top of the file and hinder your shrinking?

    Comment by Jason B. — September 19, 2014 @ 3:44 pm BST Sep 19,2014 | Reply

    • Jason,

      I am slightly surprised – I’ve found that moved objects always take up the free space at the start of the files if you’re using locally managed tablespaces. If they’re large objects you ending up with a few oddities when Oracle decides that it won’t use the small spaces (64KB, 1MB) that may be available and starts at the 8MB boundary only – which is why you might start by moving some of the very small objects first if there are a lot of tiny fragments in the tablespace.

      Trying to imagine why it doesn’t work with you – I’ve come up with one thought: if you have a file that is created relatively small and becomes very large, its space management bitmap could be allocated in sections. Perhaps in that case move objects would preferentially end up being moved into space identied by the most recently used space management blocks rather than the initial space management blocks.

      Historically, to deal with dictionary managed tablespaces, I used to use a pl/sql block report every contigous space in the tablespace in descending order of size, then create a table with each of those sizes so that every space was filled; then I’d drop a few of the empty objects to allow me to move the thing I wanted to move, shrink the datafiles down to the new HWM then “rinse and repeat”. Tedious – but only a very rare occurrence, usually in response to re-engineering around a design or maintenance error. You could apply the last bit (i.e. lower the HWM to the end of the last object every time you move an object).

      P.S. I’m no longer an alien of extraordinary ability, as from 9th Sept I became an ordinary conference attendee and tourist for the purposes of going to the USA (My O-1 expired and I decided that I wasn’t going to renew it since all my US work seems to be running over VPN from home at last.)

      Comment by Jonathan Lewis — September 19, 2014 @ 4:06 pm BST Sep 19,2014 | Reply

      • Since we create our files small and let them autoextend however big they need to get, the idea about the space management bitmaps almost certainly applies to us. Hmm.

        I also seem to recall that, when it would create the initial run of small (128K??) extents, it would prefer an empty space of exactly that size late in the file over carving up a larger space early in the file.

        Comment by Jason Bucata — September 19, 2014 @ 6:02 pm BST Sep 19,2014 | Reply

        • Jason,

          That last point is a good one. The smallest extent size is 64KB, but you’re right that Oracle will preferentially soak up existing small gaps that can’t otherwise be used in its waste-avoidance algorithms rather than taking a slice out of a 1MB gap (the next size up). I had overlooked the consequences this could have if you try to move a small object and the only small gaps are near the end of the file.

          Comment by Jonathan Lewis — September 19, 2014 @ 10:00 pm BST Sep 19,2014

  2. I have seen the same behaviour as Jason, i.e. often the new extents are placed near the end of the data file. My strategy was to always trim the data file before moving the object. I posted this earlier in

    Comment by Karsten Spang — September 30, 2014 @ 3:38 pm BST Sep 30,2014 | Reply

    • Yes, looking at your blog post, your experience reflects mine, pretty much. The big difference is that you have a higher tolerance for moving the same segment multiple times. If we can move a segment twice then it’s typically feasible for us to to move/rebuild it once to another tablespace, then shrink and move/rebuild back (if I had some particular reason to want this particular tablespace to be preserved).

      I’ve been in plenty of situations where we *could* borrow some disk space temporarily for a reorg, if it meant shrinking the net disk footprint by the end–but where it would be *preferable* to do it in place where we didn’t have to. But I’ve never had it happen where we had absolutely no choice but to shrink within the existing footprint. (If it came to that, we could have dropped all the indexes then shrunk the index tablespace to give us some breathing room. If we were that desperate, then we could usually tolerate an outage for that part of the system.)

      Comment by Jason Bucata — September 30, 2014 @ 3:58 pm BST Sep 30,2014 | Reply

  3. […] a comment on my previous post on shrinking tablespaces Jason Bucata and Karsten Spang both reported problems with small objects […]

    Pingback by Shrink Tablespace | Oracle Scratchpad — October 1, 2014 @ 2:55 pm BST Oct 1,2014 | Reply

  4. […] 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 […]

    Pingback by Shrink Tablespace | Oracle Scratchpad — November 14, 2014 @ 6:17 pm GMT Nov 14,2014 | Reply

  5. […] Moving objects to release space from a tablespace needs a little planning […]

    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 )

Twitter picture

You are commenting using your Twitter 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: