Skip to Main Content
  • Questions
  • Toatl free space and largest extents

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, shiva.

Asked: April 28, 2008 - 7:12 am UTC

Last updated: April 30, 2008 - 9:36 am UTC

Version: 8.1.6

Viewed 1000+ times

You Asked

Hi Tom,

I am new to this blog. I have one doubt regarding fragmentation.

select TABLESPACE_NAME,
sum(BYTES),
max(BYTES)
from dba_free_space
group by TABLESPACE_NAME;

If we give this query we will get sum(BYTES)-sum of freespace and max(BYTES)-largest free extent.
My question is:-

If the value of max(BYTES) is same as sum(BYTES) then is it a tablespace fragmentation?
Whether we should coalesce the tablespace? Please Explain.

Thanks
Shiva



and Tom said...

... I am new to this blog. I have one doubt regarding fragmentation. ...

I have lots of doubt as to "fragmentation", I have yet to see it consistently defined and used. Everyone says it, no one defines it :)


If the sum(bytes) = max(bytes) that means there is just one free extent. It is impossible to be fragmented by "anyones" defintion with just 1 of something.

Now, if you had a tablespace with lots and lots of tiny free extents...
but lots of free space

(eg: avg(bytes) is much much smaller than sum(bytes) - lots of free space because sum(bytes) is large, but not lots of contiguous free space because avg(space) by extent is very small)

then you might be 'fragmented' in one sense of the word - and alter tablespace coalesce MIGHT be useful (not in 9i when you get to use locally managed tablespaces... but maybe in the really old stuff you are using). However, it might not do a thing as coalesce just joins together adjacent free extents (something SMON does in the background normally). If none of the free extents are adjacent - there isn't much you can do short of moving everything out of that tablespace into a new one (and it is highly unlikely you really need to do that).



Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Wasn't it 8i?

A reader, April 29, 2008 - 7:11 pm UTC

Wasn't it 8.1.5 that introduced locally managed tablespaces?
Tom Kyte
April 30, 2008 - 9:36 am UTC

LMT in 8i

Roland Scheifele, April 30, 2008 - 4:15 am UTC

Yes, but not by default. It was an option in 8i.