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