DBA_DATA_FILES, DBA_FREE_SPACE and DBA_SEGMENTS–A Myth Buster

Hi Friends,

I’ve been encountering with lot of questions related to free space and used space details in an Oracle database in various forums and discussions.

Is that really difficult to understands the Oracle space management system? No it’s not; I’m listing down my observations here, which could help discern through the different data dictionary view related to space management and bust the myths around them

The 3 main data dictionary views which are frequently being used by many people are DBA_DATA_FILES, DBA_SEGMENTS and DBA_FREE_SPACE.

Lot of queries available on the internet, which are intended to know the space utilization details are based on DBA_DATA_FILES & DBA_FREE_SPACE and some on DBA_DATA_FILES & DBA_SEGMENTS.

Some free space reporting queries uses DBA_DATA_FILES-DBA_SEGMENTS as free space and some directly uses DBA_FREE_SPACE as free space; Which one to use?

See the below results

SQL> select sum(bytes)/1024/1024 from dba_data_files where tablespace_name=’USERS_SMALL';

SUM(BYTES)/1024/1024
——————–
266016

SQL> select sum(bytes)/1024/1024 from dba_segments where tablespace_name=’USERS_SMALL';

SUM(BYTES)/1024/1024
——————–
241157

SQL> select sum(bytes)/1024/1024 from dba_free_space where tablespace_name=’USERS_SMALL';

SUM(BYTES)/1024/1024
——————–
24856

So DBA_DATA_FILES-DBA_SEGMENTS=266016-241157=24859, So there’s 3MB difference between both the results and in some cases it’s even more. So which one to believe? What’s the actual free space I can use for my objects?

The answer is DBA_FREE_SPACE which actually contains the free space that available to all the objects we’re going to create.

Then why I’m saying the DBA_DATA_FILES-DBA_SEGMENTS is incorrect? According to theory it’s perfectly correct but in reality It’s the LMT Headers/space management blocks + and the last “few” blocks at the end of the file which aren’t enough to make up a whole extent according to the extent allocation and segment space definitions of the tablespace. Thanks to Jonathan Lewis Sir for pointing that out. Please go through Jonathan’s blog http://jonathanlewis.wordpress.com/2012/05/31/lmt-headers/ to get a clear idea of what these LMT headers are.

So, DBA_DATA_FILES=DBA_SEGMENTS+DBA_FREE_SPACE+X

where X=first few blocks of the FILE that are taken up by space management blocks + the last “few” blocks at the end of the file which aren’t enough to make up a whole extent according to the extent allocation and segment space definitions of the tablespace

How could I get the ‘actual’ free space details without using the DBA_FREE_SPACE view? It’s the only way or any alternative exist?

An alternative is there through which you can get the actual free space information just by using DBA_DATA_FILES and DBA_SEGMENTS/DBA_EXTENTS.

See the query below

SQL> select sum(user_bytes)/1024/1024 from dba_data_files where tablespace_name=’USERS_SMALL';

SUM(USER_BYTES)/1024/1024
————————-
266013

There’s a column called USER_BYTES in DBA_DATA_FILES which contains the actual total space excluding the LMT headers+leftover blocks as mentioned earlier.

So DBA_DATA_FILES[USER_BYTES]-DBA_SEGMENTS=266013-241157=24856 which is exactly the same value we’re seeing in DBA_FREE_SPACE.

Don’t be calm and quite if some one argues with the results of DBA_DATA_FILES[BYTES]-DBA_SEGMENTS as the actual free space available in the Database. Just ask him to read this or even better solution is to ask him to fill with the database with data equivalent to what he’s claiming as free space Winking smile

CSM

About these ads

4 Responses to DBA_DATA_FILES, DBA_FREE_SPACE and DBA_SEGMENTS–A Myth Buster

  1. This comment is incorrect:
    “in reality the DBA_SEGMENTS ignores the space consumed by the headers in every block.”

    The reason why bytes and user_bytes are different is because the latter allows for the first few blocks of the FILE that are taken up by space management blocks; and the last “few” blocks at the end of the file which aren’t enough to make up a whole extent according to the extent allocation and segment space definitions of the tablespace. (See, for example, http://jonathanlewis.wordpress.com/2012/05/31/lmt-headers/ )

    • csmdba says:

      Thanks for stopping by Sir…It’s really glad to see you here…

      By following your comment can I infer
      DBA_DATA_FILES=DBA_SEGMENTS+DBA_FREE_SPACE+LMT_headers
      Where LMT_headers= first few blocks of the FILE that are taken up by space management blocks; and the last “few” blocks at the end of the file which aren’t enough to make up a whole extent according to the extent allocation and segment space definitions of the tablespace

      And in some environments I’ve seen the difference is even more as 15G. If it’s with the leftover blocks and space management blocks, would there any possibility that the difference is this much?

      Thanks In Advance,
      Chandra Sekhar Maddila

      • Probably better not to say “LMT_HEADERS” at that point since the stuff at the end of the file isn’t anything to do with the header blocks and can be used if you extend the file. (Perhaps lmt_headers + lmt_spare)

        Depends which way your 15G is – but remember that if you drop a table but don’t purge it from the recyclebin its space appears in both dba_segments and dba_free_space – so your formula would be double counting.

        If the 15G appears when the sum is less than the figure in dba_data_files then I think it has to be a bug. There was one where sum(dba_extents) didn’t match dba_segments – but I don’t recall which versions, but I think you could see a row in dba_segments with a value less than the sum of its extents.when the object had been created through parallel execution. (Check Randolf Geist’s blog, I think he has written about it.)

  2. csmdba says:

    I’ve updated the article as per your suggestions Sir…Hope this is correct now.
    Thanks again for stopping by and providing your invaluable suggestions which helped in “busting the myths of a myth buster” :)

    Regards,
    Chandra Sekhar Maddila

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

Follow

Get every new post delivered to your Inbox.

Join 331 other followers

%d bloggers like this: