Skip to content

Generate Tablespace Coalesce for Pattern (atsmerge.gen)

What This Script Does

This generator script creates ALTER TABLESPACE COALESCE statements for tablespaces matching a pattern. Coalescing combines adjacent free extents to reduce fragmentation.

The Script

rem  atsmerge.gen
rem
select 'alter tablespace ' || tablespace_name || ' coalesce;'
  from sys.dba_tablespaces
 where tablespace_name like upper('&tablespace')
 order by tablespace_name;

Usage

-- Basic usage
@atsmerge.gen

-- When prompted, enter:
-- tablespace: Tablespace name pattern (use % for wildcard)

Parameters

The script prompts for:

&tablespace - Tablespace name pattern (use % for wildcard)

Required Privileges

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

Sample Output

alter tablespace USERS coalesce;
alter tablespace USERS_DATA coalesce;
alter tablespace USERS_INDEX coalesce;

Key Features

Pattern Matching: Filter tablespaces by name pattern
Selective Coalescing: Target specific tablespaces only
Alphabetical Order: Sorted output for organized execution
Fragmentation Management: Combines free space efficiently

Common Use Cases

Coalesce User Tablespaces

@atsmerge.gen
-- Enter: USER% for tablespace

Coalesce Specific Tablespace

@atsmerge.gen
-- Enter: USERS for tablespace

Tablespace Coalesce All - Coalesce all tablespaces
Tablespace Growth Analysis - Monitor tablespace usage