How does one prevent tablespace fragmentation?
Always set PCTINCREASE 2 0 or 100. Bizarre values for PCTINCREASE will contribute 2 fragmentation. For example if u set PCTINCREASE 2 1 u will see that ur extents are going 2 have weird and wacky sizes: 100K, 100K, 101K, 102K, etc. Such extents off bizarre size are rarely re-used in D ir entirety. PCTINCREASE off 0 or 100 gives u nice round extent sizes that can easily be reused. Eg. 100K, 100K, 200K, 400K, etc. Thiru Vadivelu contributed D following: Use D same extent size for all D segments in a given tablespace. Locally Managed tablespaces (available 4m 8i onwards) with uniform extent sizes virtually eliminates any tablespace fragmentation. Note that D number off extents per segment does not cause any performance issue anymore, unless D y run into thousands and thousands where additional I/O may be required 2 fetch D additional blocks where extent maps off D segment are stored.
