REM ------------------------------------------------------------------------------
REM Filename:   tsfrag.sql
REM Purpose:    Shows whether the TBS is fragmented and the type of fragmentation.
REM Author:     Kam Muhamad Sirajdin [mdsirajdin@yahoo.com]
REM ------------------------------------------------------------------------------

TTI "Tablespace Fragmentation Details"

SELECT   dfsc.tablespace_name tablespace_name,
         DECODE (
            dfsc.percent_extents_coalesced,
            100,
            (DECODE (
                GREATEST ((SELECT COUNT (1)
                             FROM dba_free_space dfs
                            WHERE dfs.tablespace_name = dfsc.tablespace_name), 1),
                1,
                'No Frag',
                'Bubble Frag'
             )
            ),
            'Possible Honey Comb Frag'
         )
               fragmentation_status
    FROM dba_free_space_coalesced dfsc
ORDER BY dfsc.tablespace_name;

TTI off

