COMMON CAUSES OF FRAGMENTATION

Solution Description:
=====================
 
Transference of extents from an object to the free list will cause a
tablespace to become fragmented. The more often this movement of space occurs,
the more fragmented your tablespace will become. Here are some ways Oracle can
become fragmented:
 
SYSTEM TABLESPACE 
 
 Your system tablespace should only contain data dictionary information 
and your system rollback segment. Anything else in your system tablespace can 
potentially cause fragmentation. One of the most common ways this tablespace 
can become fragmented is if your users default and temporary tablespace point 
to this tablespace. You can check this by looking in dba_users. Users should
have a separate data and temp tablespace from system because creating and
dropping objects, and temporary segments will fragment the tablespace.
 
DATA and INDEX TABLESPACE 
 
 For these tablespaces, the concern for fragmentation is not at the 
tablespace level, but rather, the object level. If you drop and recreate many 
tables or indexes, this will cause fragmentation at the tablespace level. 
Objects that stay in this tablespace generally do not cause tablespace 
fragmentation. But tables and indexes can get fragmented. One of the most 
common ways to fragment a table or index is if you are doing large deletes and 
then inserts frequently. When you delete a lot of rows and then immediately 
insert rows, this will cause the table to grow. The reason you see this effect 
is because of delayed block cleanout . This will cause fragmentation in the 
table. 
  When you do any dml on a table, Oracle issues a fast commit, which 
means that those blocks have been marked for change, but the change will not 
actually be implemented until the block is touched again(i.e. doing a select 
statement after the delete will force Oracle to go and touch the block and 
actually delete the rows and then free up the space). This is delayed block 
cleanout. 
 Indexes will also become fragmented with inserts and deletes because 
of the way indexes are implemented. When a row is deleted, Oracle will not 
reuse the index space. Pctused for indexes is always 0, which means the index 
blocks will not be put on the free list for reuse. Therefore, indexes are 
always growing and can become very fragmented. This is the tradeoff for more 
efficient index performance. 
 
TEMP TABLESPACE 
 
This tablespace by nature gets fragmented. Temporary segments are constantly 
being created and dropped for sorting. There should not be any permanent 
objects in this tablespace, as that would prevent Oracle from coalescing all 
the free extents together. Another way this tablespace can become more 
fragmented is if initial and next extent are different and the pctincrease is 
set. Setting the initial and next extents to the same size and pctincrease to 
0 will ensure that Oracle will grab the same size extent every time. This will 
reduce uneven sized free extents. 

--temp tablespace总结：建立临时表空间时，建议：定UNIFORM。