KB : How to check database table fragmentation
1) Rule of thumb to perform de-fragmentation for Database table which are greater than 80%.
2) Example of the output of Database: -
OWNER TABLE_NAME TABLESPACE_NAME TOTAL_SIZE_MB ACTUAL_SIZE_MB FRAGMENTED_SPACE_MB
------------ --------------------- ------------------ ------------- -------------- -------------------
GC_DIALER SCBTAIWANCL GC_DIALER_DATA 605 20.62 584.38
GC_DIALER SCBPAKISTANCL GC_DIALER_DATA 463.39 17.88 445.51
GC_DIALER SCBINDONESIACL GC_DIALER_DATA 364 15.66 348.34
GC_DIALER SCBPAKISTANPTPCL GC_DIALER_DATA 261.68 .11 261.57
GC_DIALER BD_FRM GC_DIALER_DATA 250.97 .23 250.74
3) Calculate the percentage of database table fragmentation: -
a) SCBTAIWANCL - calculation of percentage of Fragmentation (584.38 / 605 * 100) = 96.6%
b) SCBPAKISTANCL - calculation of percentage of Fragmentation (445.51 / 463.39 * 100) = 96.14%