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.

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 FlipBook shows how this type of fragmentation can occur.

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.

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.

One of any number of applets Oracle offers to extend the capabilities of the Oracle Enterprise Manager.