Oracle Scratchpad

May 31, 2012

LMT Headers

Filed under: Infrastructure,Oracle — Jonathan Lewis @ 5:57 pm BST May 31,2012

Here’s another one of those little changes that sidles in when you’re not looking.

When locally managed tablespaces first appeared, there were a number of posts on Usenet ( and the metalink lists (the OTN database forum didn’t exist at the time) about “missing” space in data files. The commonest sort of comment was along the lines of

“I’ve created a data file of 100 MB, how come I can only create 99 extents of 1 MB each?”

“I’ve created a data file of 10 GB, how come I can only create 9 extents of 1 GB each?”

The answer was that Oracle reserved the first few blocks of the file (typically totalling 64KB of space, but 128KB if you were on a platform that supported a 32KB block size) as space management blocks to hold a bitmap identifying which (notional) extents in the file were free and which were allocated to data segments. For uniform extents each bit in the map represented the given unit size of an extent, for system-managed extent allocation each bit represented 64KB (the “lowest common denominator” for an extent). As a side effect of this bitmap/extent mapping you can “lose” a whole extent – less one block – if you don’t work out your file size quite carefully.

Here’s a little demo script, with some results, cut from a session using to demonistrate the point:

	tablespace tiny
	datafile 'C:\ORACLE\ORADATA\D10g\tiny.dbf'
	SIZE 1114112 reuse
	extent management local
	segment space management auto

	file_id, block_id, bytes, blocks
	tablespace_name = 'TINY'

SQL> @demo

Tablespace created.

---------- ---------- ---------- ----------
         8          9    1048576        128

1 row selected.


If you’re wondering about my choice of initial file size, it’s the equivalent of 1MB + 64KB. As you can see this results in 1MB of “user” space available, starting in block 9 of the file (leaving 8 blocks – 64KB given my block size – for any file-based overheads).

Let’s check the results (after changing the filename) for an 11g instance – in this case:

SQL> @temp

Tablespace created.

---------- ---------- ---------- ----------
         7        128      65536          8

1 row selected.


When we shift to 11g we see 1MB of overhead at the start of the file, and only 64KB left for user space.

I haven’t done a lot of testing to see how many different special cases there are but based on a couple of quick checks it looks as if Oracle will allocate 1MB of space management overhead at the start of file if the initial file size is at least 1MB + 64KB. (For smaller files the overhead is still 64KB.)

I suspect the reason behind this is that if a file were to become very large Oracle would eventually have to allocate a secondary space management bitmap somewhere in the middle of the file (which might make it hard to shrink the file at a later date), so it pre-emptively allocates a space management area large enough for a very large file when you create the initial file.

As a little throwaway test (to see if I could get a secondary map allocated) I created a single file of 2,047 MB, in a tablespace using locally managed extents of uniform size 16KB (yes, deliberately silly sizing), and freelist management. Here’s the slightly surprising result I got from querying dba_free_space for that tablespace:

	block_id, blocks,
	block_id + blocks - 1	end_block,
	bytes/1048576  		MB
	tablespace_name = 'TINY'
order by

---------- ---------- ---------- ----------
       128     126976     127103        992
    127104     126976     254079        992
    254080       7936     262015         62
sum                                    2046

3 rows selected.

I was expecting a single free space chunk – but that’s not the way it seems to work. For an 8KB block size (in this version of Oracle), the bitmap space management blocks start at block 3 of the file, and each bitmap block can map 63,488 extents – which in my case amounted to 126,976 blocks – and the code that reports dba_free_space seems to look at one bitmap block at a time to generate the report.

It’s probably the case that none of this matters to anyone – but it’s just another couple of details that go into my catalogue of implementation details just in case I find the boundary condition one day, perhaps relating to LOBs or SecureFiles, where something strange happens because of some side effect of this implementation.


If you want to create a file on an 8KB block size with a secondary bitmap, you should start with a small file (1MB or less), 16KB uniform extents and freelist management; this will give you 6 blocks of bitmaps, which can each map 63,488 extent, or 126,976 blocks, for a total of 5,952MB. Add 64KB for the initial file header information and then a bit more – call it 6GB to make it easy – and you’ve got a target to resize the file to the point where you get a secondary map. But I think that means you’ll need a 64-bit O/S.



  1. Very Interesting.

    Comment by baskar — June 1, 2012 @ 3:27 am BST Jun 1,2012 | Reply

  2. Reblogged this on lava kafle kathmandu nepal.

    Comment by lkafle — June 1, 2012 @ 3:44 am BST Jun 1,2012 | Reply

  3. […] Jonathan Lewis starts with the sentence, “When locally managed tablespaces first appeared, there were a number of posts on Usenet ( and the metalink lists (the OTN database forum didn’t exist at the time) about “missing” space in data files.” […]

    Pingback by Log Buffer #274, A Carnival of the Vanities for DBAs | The Pythian Blog — June 1, 2012 @ 7:01 am BST Jun 1,2012 | Reply

  4. Jonathan,

    I think it’s more likely they did this for alignment reasons. I wonder if they also align segments on the MB boundary (when using auto-sizing) inside the datafiles as well.

    Comment by Christo Kutrovsky — June 1, 2012 @ 6:39 pm BST Jun 1,2012 | Reply

    • Christo,

      That sounds like a reasonable hypothesis, especially when you start thinking about ASM and AUs of 1MB and 4MB, and the impact of Exadata striping across cells. You could probably imagine seeing some sort of performance side effect if the extent boundaries on your objects were 64KB out of step with the AU boundary.

      Comment by Jonathan Lewis — June 1, 2012 @ 7:54 pm BST Jun 1,2012 | Reply

  5. […] Thanks to Jonathan Lewis Sir for pointing that out. Please go through Jonathan’s blog to get a clear idea of what these LMT headers […]

    Pingback by DBA_DATA_FILES, DBA_FREE_SPACE and DBA_SEGMENTS–A Myth Buster | queryCSM — March 26, 2013 @ 3:41 pm BST Mar 26,2013 | Reply

  6. Jonathan,

    the change seems to be introduced for extent alignment reasons (as already pointed out by Christo), and the behaviour can be controlled via event 60060 as I’ve recently learned, which disables or enables the extent alignment on allocation unit boundaries.

    Enabling the event will revert to the pre-11g result for your test case, but has some more significant meaning for extent allocation in general (will publish an article soon).


    Comment by Randolf Geist — April 17, 2013 @ 7:14 pm BST Apr 17,2013 | Reply

  7. […] six blocks of the file are (currently) unusable. (I have a more extreme example of wasted space in an example I published a couple of years […]

    Pingback by Tablespace HWM | Oracle Scratchpad — March 16, 2015 @ 10:22 am BST Mar 16,2015 | Reply

  8. […] just discovered that I had already mentioned this phenomenon a few months ago with an example using a block size of […]

    Pingback by Free Space | Oracle Scratchpad — May 25, 2018 @ 4:19 pm BST May 25,2018 | 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 )

Google+ photo

You are commenting using your Google+ 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.

Powered by