Managing DB Objects   «Prev  Next»
Lesson 11Oracle Fragmentation
ObjectiveImpact of Oracle tablespace fragmentation.
Extents and segments let you control the way Oracle stores data, while still optimizing storage. The interaction between different extents within a tablespace can create a problem that can impact performance.
Tablespace fragmentation
A tablespace can contain many different segments for different database objects.
Each of these extents can be a different size. Extents are added to a tablespace as additional storage space for each database object is needed. Extents can also be dropped from a tablespace, as when their database objects are dropped. This can lead to a condition know as tablespace fragmentation, where a tablespace contains holes that cannot be reused for new extents.
The following Slide Show shows how this type of fragmentation can occur.

TableSpace Database Objects
The overall effect of this fragmentation is twofold: Your Oracle database will require more storage space, because the tablespace contains a large portion of wasted space, and accessing data within the fragmented tablespace will take longer, because the larger tablespace will require more disk head movement.
Diagnosing tablespace fragmentation
The problem of tablespace fragmentation only occurs as your tablespace is used over time. How will you know when your tablespaces have become fragmented?
You can use a variety of queries against the data dictionary views that report on space usage in the tablespace, but one of the easiest ways to determine whether you have a tablespace fragmentation problem is to use the Tablespace Manager. The Tablespace Manager is a part of the Add-on Tuning Pack for Oracle Enterprise Manager.
Add-on Tuning Pack:
One of any number of applets Oracle offers to extend the capabilities of the Oracle Enterprise Manager.
The next lesson shows how to avoid and fix fragmentation.