Doc

    Name:    fragment.sql

    This script lists tablespace disk fragmentation information

#

ttitle 'Tablespace Fragmentation Information'

SELECT    SUBSTR(ts.name,1,10)                            TSPACE,
          tf.blocks                                       BLOCKS,
          SUM(f.length)                                   FREE,
          COUNT(*)                                        PIECES,
          MAX(f.length)                                   BIGGEST,
          MIN(f.length)                                   SMALLEST,
          ROUND(AVG(f.length))                            AVERAGE, 
          SUM(DECODE(SIGN(f.length-5), -1, f.length, 0))  DEAD
  FROM      sys.fet$           F, 
            sys.file$          TF, 
            sys.ts$            TS
  WHERE     ts.ts#  =  f.ts#
  AND       ts.ts#  =  tf.ts# 
  GROUP BY  ts.name, tf.blocks;