Oracle Scratchpad

May 20, 2011


Filed under: ASSM,Infrastructure,Oracle — Jonathan Lewis @ 5:16 pm BST May 20,2011

Here’s an interesting little detail (obvious AFTER the event) about space management with ASSM (automatic segment space management). It starts with this question on OTN:

When I alter table deallocate unused and keep 1K the object ends up with 24 blocks, even after I’ve truncated the table. Why?
This is in a tablespace using ASSM, with locally managed extents set to use automatic (system) allocation.

Ultimately the answer is – the first extent in this table started life at 8MB, and an extent that large needs to have 16 level 1 bitmap (space management) blocks, one level 2 bitmap block, and the segment header block before you get to data blocks. When you truncate and deallocate Oracle doesn’t recreate the map, so the extent has to start with 18 blocks – round that up to the multiple of 8 blocks (the 64KB that Oracle normally uses for starting extents for small objects) and you get the 24 blocks from the question.

It took us a bit of time to get to the right answer on the thread – and that’s why I’m giving you the quick answer.


  1. […] Jonathan Lewis has found another gem as an answer to a question posted on OTN forums regarding the ASSM. […]

    Pingback by Log Buffer #221, A Carnival of the Vanities for DBAs | The Pythian Blog — May 21, 2011 @ 3:04 pm BST May 21,2011 | Reply

  2. A bit off topic, but in you have a new clause



    Comment by laurentschneider — May 21, 2011 @ 7:20 pm BST May 21,2011 | Reply

    • Laurent,

      Thanks for the comment.

      It may look a little off-topic at first glance, but I think it’s most appropriate – it addresses the original poster’s requirement to minimise the space used.

      Comment by Jonathan Lewis — May 22, 2011 @ 12:25 pm BST May 22,2011 | 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: