shiva -- Thanks for the question regarding "Toatl free space and largest extents", version 8.1.6
Submitted on 28-Apr-2008 7:12 Central time zone
Last updated 30-Apr-2008 9:36
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 we 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).
Wasn't it 8i?
April 29, 2008 - 7pm Central time zone
Reviewer: A reader from Concord, CA
Wasn't it 8.1.5 that introduced locally managed tablespaces?
LMT in 8i
April 30, 2008 - 4am Central time zone
Reviewer: Roland Scheifele from Germany
Yes, but not by default. It was an option in 8i.