-- #############################################################################################
--
-- %Purpose: Generate Script to coalesce free Space in cluttered Tablespaces
--
-- #############################################################################################
--
SELECT  a1.tablespace_name,COUNT(*) nbr_cont_wholes
    FROM    sys.dba_free_space a1, sys.dba_free_space a2
    WHERE   a1.tablespace_name=a2.tablespace_name
    AND     a1.block_id+a1.blocks = a2.block_id
    GROUP BY A1.tablespace_name
/
set heading off
spool alter_ts_coal.sql
SELECT 'ALTER TABLESPACE '||a1.tablespace_name||' COALESCE;'
    FROM    sys.dba_free_space a1, sys.dba_free_space a2
    WHERE   a1.tablespace_name=a2.tablespace_name
    AND     a1.block_id+a1.blocks = a2.block_id
    GROUP BY A1.tablespace_name
/
SPOOL OFF
set heading on
@alter_ts_coal.sql
