... 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).