What is actually Tablespace Fragmentation and how does one prevent tablespace fragmentation?
Printable View
What is actually Tablespace Fragmentation and how does one prevent tablespace fragmentation?
One acromyn needed here. LMT. Local Managed Tablespaces with Uniform extents. Kiss all your fragmentation worries goodbye. Alternatively, create different sized Data-Dictionary managed tablespace with same INITIAL and NEXT extent sizes and police them.
Opps, posted with only half the question answered. Basically fragmentation occurs when objects are created, dropped, rebuilt in the same tablespace in which the objects have different extents sizes. Ie.
Table EMP (Initial 2M Next 2 M)
Table DEPT (Initial 1M Next 1M)
Tablespace occupied
EMP 2M, DEPT 1M, EMP 2MB.
If you drop the DEPT table, there with be a gap in the tablespace of 1M (Between the 2 EMP extents). This space will only be reused by a new segment which is <= 1M. And if none are created, you'll basically have 1M wsted. Hence fragmentation. This incident is more evident with indexes in which are rebuilt. Simple because when and index is rebuilt, the space it occupied prior to the rebuild is freed. Therefore creating freespace gaps in the tablespace.
The idea is to create tablespace in which every object has the same initial/next extents, so the space can be re-use by other segment extents.
Cheers
[Edited by grjohnson on 02-01-2002 at 12:33 AM]
Good question! I was wondering about this myself. :) But what
dictionary table do I query to find out if the tablespace is
fragmented? Thanks.
DBA_FREE_SPACE_COALESCED.
Check for PERCENT_EXTENTS_COALESCED column's data. If for any tablespace this is not 100, then that tablespace needs coalescing