-- 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