Skip to content

Generate Tablespace Coalesce Statements (atsfree.gen)

What This Script Does

This generator script creates ALTER TABLESPACE COALESCE statements for all tablespaces in the database. Coalescing combines adjacent free extents into larger free extents, reducing fragmentation.

The Script

select 'alter tablespace ' ||
       tablespace_name ||
       ' coalesce;'
  from sys.dba_tablespaces
 order by tablespace_name
/

Usage

-- Basic usage
@atsfree.gen

-- No parameters required - generates for all tablespaces

Parameters

This script does not prompt for any parameters - it generates statements for all tablespaces.

Required Privileges

SELECT ANY DICTIONARY
-- OR --
SELECT ON DBA_TABLESPACES
-- Plus ALTER TABLESPACE privilege

Sample Output

alter tablespace EXAMPLE coalesce;
alter tablespace SYSAUX coalesce;
alter tablespace SYSTEM coalesce;
alter tablespace TEMP coalesce;
alter tablespace UNDOTBS1 coalesce;
alter tablespace USERS coalesce;

Key Features

All Tablespaces: Generates statements for every tablespace
Alphabetical Order: Sorted by tablespace name
Simple Operation: No parameters needed
Fragmentation Reduction: Combines adjacent free space

Common Use Cases

Regular Maintenance

-- Coalesce all tablespaces during maintenance window
@atsfree.gen

After Large Deletes

-- Run after dropping multiple objects
@atsfree.gen

Tablespace Merge Generator - Coalesce specific tablespaces
Maximum Shrink Analysis - Analyze space usage