LMT – Tablespace Fragmentation – Do we really need to act?

In olden days, the dictionary managed tablespaces (DMT) used failed allocating extents even if there are continues free space, but not coalesced. So, there were manual intervention required to coalesce the free space to make it a bigger extent. But, when Oracle introduced recyclebin, we can’t really coalesce the free space as the extents are not really dropped rather marked for deletion and space will be re-claimed when there is a space pressure.

I was wondering what will happen if all my recyclebin extents are 64KB and a segment is looking for a 1024 KB free extent – when we use autoallocate. In the case of the dictionary tablespace, this is going fail as any single free extent cannot allocate 1024 KB out of multiple fragments of 64KB free extents. DB A need to run COALESCE command and make it bigger extent, so that the space request will go through.

So, what will happen in the case of a LOCALLY MANAGED TABLESPACE with AUTOALLOCATE clause – if we use UNIFORM SIZE this issue will never occur.

I have a tablespace F5, 54MB size and all its free spaces is in recyclebin, fragmented badly.

So, de-fragmentation and coalescing tablespace is no more needed for a LMT and Oracle will do it for you as when required. This will bring another risk – what if Oracle is not able to coalesce ( not being continue recylebin free extents) while there is enough free space. Unfortunately we can’t pro-actively monitor this. Or if there is a way, please feel free to comment.