Oracle Scratchpad

May 20, 2011

ASSM

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

3 Comments »

  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 GMT May 21,2011 | Reply

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

    TRUNCATE TABLE t DROP ALL STORAGE

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/statements_10007.htm#BABDDJHB

    Krgds
    Laurent

    Comment by laurentschneider — May 21, 2011 @ 7:20 pm GMT 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 GMT May 22,2011 | 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

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,308 other followers