If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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.