Oracle Scratchpad

January 30, 2010

Free Space

Filed under: fragmentation,Infrastructure,Oracle — Jonathan Lewis @ 10:37 am GMT Jan 30,2010

I’ve just seen the following question on OTN:

I had a log table which was about 30G in Production, since it was growing in size we decided to drop it. After dropping it we can’t see the space being freed. Even at OS level we can’t see any reduction in filesize.Can you please explain and/or help in this?

This prompted me to come up with the following analogy.

I’ve got a case of wine in the boot (trunk) of my car. I’ve just taken six bottles out of the case, but I don’t see any extra free space in the boot. Why not ?

The analogy works on two levels:

Level 1: Call the boot the O/S, the case an Oracle datafile, and each bottle a table (or index, or other segment), and you’ve answered the original question.

Level 2: Call the boot an Oracle datafile, the case a table, and each bottle a row, and you’ve answered the equally common question: “I’ve just deleted 10M rows from a table, but dba_free_space doesn’t show any extra free space, why not ?”

Footnote for pedants: I have heard of tablespaces and I do know that you can have multiple datafiles per tablespace – but that makes it harder to find a tidy analogy.

Footnote on how to reclaim space: If you need to reclaim space, you have to start by making sure that the “potential” space is at the end of the object.

For datafiles, you probably have to move tables and rebuild indexes to fill the empty space lower down the data file so that the end of the file is empy and the file can be resized downwards (alter database datafile … resize).  It’s a tedious job and could be bad for your database.

For tables you either have to rebuild the table and all its indexes (possibly using the dbms_redefinition package) or use the newer “shrink” option which moves rows from the end of the table into space nearer the start of the table and (optionally)drops the high water mark. Either strategy could be bad for your database.


  1. Jonathan

    Could you explain why the space reclaimation strategies you’ve mentioned may be bad for the database?

    Many thanks

    Comment by Simon Cole — January 30, 2010 @ 8:29 pm GMT Jan 30,2010 | Reply

  2. Hi Jonathen,

    strategy 1 alter database datafile … resize could be bad, but there is a metalink note which suggest two scripts to find lowest HWM for datafile to resize (NOTE:130866.1 – How to Resolve ORA-03297 When Resizing a Datafile by Finding the Table Highwatermark), though never came across problem with this aproach, not sure its a good idea if using flashback features.

    startegy 2 shrink option

    Can cause problem if shrink option used with row movement clause, it will move rows and application depends on rowid’s may cause problems and can invalidate triggers based on this table as well.

    Just a thought, Are you planning to come Oracle university Prague in 2010, i like to attend “Optimizing Oracle Performance by Design”.

    Comment by Vineeth Kalanthodi — January 30, 2010 @ 8:44 pm GMT Jan 30,2010 | Reply

    • Vineeth,

      Not sure for Strategy 1, but Strategy 2 will affects Clustering Factor due to row movement as said by Jonathan above “moves rows from the end of the table into space nearer the start of the table”

      Comment by Yasser — January 31, 2010 @ 6:41 am GMT Jan 31,2010 | Reply

    • Vineeth

      “and application depends on rowid’s may cause problems”. If you mean applications that have stored rowids in a table, then shrinking the target table would be a disaster !

      I don’t have Prague on my timetable for 2010, but I’ve only arranged dates as far as May with OU so there are plenty of months left. I don’t do the three-day “Performance by Design” seminar any more, though, just the one-day events.

      Comment by Jonathan Lewis — January 31, 2010 @ 8:33 pm GMT Jan 31,2010 | Reply

  3. Simon,

    Moving tables can be bad for your database though, like rebuilding indexes, it probably won’t do much harm in most cases.

    The threat comes particularly from tables where you have considered it necessary to set PCTFREE to allow rows to grow after they have been inserted. If you recreate the table you then have a problem choosing a suitable value for PCTFREE.

    If you move the table without changing PCTFREE then all the old data (which has grown to full size) is going to be inserted into blocks that leave PCTFREE which will never be used.

    On the other hand if you move the table with PCTFREE set to 0 to avoid wasting space for the old row then many of the recent rows which have not yet grown will end up having to migrate as they grow.

    There is a strategy using “minimize_records_per_block” that I described in Practical Oracle 8i for dealing with this situation, but I don’t have a note of it on-line; but I’ve found an example giving you the idea on the OTN database forum.

    Yasser has highlighted one of the problems with the shrink command. It shuffles rows around the table so that new rows are intermingled with old rows, and this could have a nasty effect on both the actual and apparent clustering of the data; making some indexes less efficient, or leading the optimizer to ignore an index it should be using.

    The other problem with the shrink command is that it actually deletes and re-inserts rows in the table – locally and temporarily disabling constraints and triggers as it does so – which means it can generate a huge amount of undo and redo. (I wrote a note on OTN a few weeks ago about the amount of undo generated by a shrink; double it and you’re in the right ballpark for redo).

    Comment by Jonathan Lewis — January 31, 2010 @ 7:53 pm GMT Jan 31,2010 | Reply

  4. How about this: “I have six bottles of whisky on my bedside table, and I’ve drunk one-third of each one but there’s still no room for an alarm clock!”


    Comment by David Aldridge — February 1, 2010 @ 12:30 pm GMT Feb 1,2010 | Reply

    • David,

      Another good analogy; but we’d have to ask whether you’re sure there’s no room, or is it just that you’re having trouble seeing it ?

      Comment by Jonathan Lewis — February 3, 2010 @ 11:53 am GMT Feb 3,2010 | Reply

  5. […] be “reclaimed” if it is at the end of the data files (a point I made quite recently in a more generic comment on free space). dba_free_space will tell you where the space is, dba_extents will tell you what’s in the […]

    Pingback by Shrink Tablespace « Oracle Scratchpad — February 6, 2010 @ 5:32 pm GMT Feb 6,2010 | 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: