Free Space Coalescing Report (dcontig.sql)

What This Script Does

This script analyzes tablespace free space coalescing statistics to show how effectively Oracle has merged adjacent free extents. It provides insight into tablespace fragmentation and the efficiency of automatic space management by displaying the percentage of extents and blocks that have been coalesced.

Script

rem  dcontig.sql
rem
ttitle 'Free Space Coalesced'
rem
col tablespace_name format a10 heading 'TABLESPACE'
col total_extents format 9999 heading 'TOTAL|EXTS'
col extents_coalesced format 9999 heading 'EXTS|MERGE'
col percent_extents_coalesced format 999.9 heading '%|EXTENT|MERGE'
col total_bytes format 9999999 heading 'TOTAL|BYTES|(KB)'
col bytes_coalesced format 9999999 heading 'BYTES|MERGE|(KB)'
col total_blocks format 999999 heading 'TOTAL|BLOCKS'
col blocks_coalesced format 999999 heading 'BLOCKS|MERGE'
col percent_blocks_coalesced format 999.9 heading '%|BLOCKS|MERGE'
rem
select tablespace_name,
       total_extents,
       extents_coalesced,
       percent_extents_coalesced,
       total_bytes/1024 total_bytes,
       bytes_coalesced/1024 bytes_coalesced,
       total_blocks,
       blocks_coalesced,
       percent_blocks_coalesced
  from sys.dba_free_space_coalesced
 order by tablespace_name
/

Usage

-- Run the script in SQL*Plus or SQLcl
@dcontig.sql

-- No parameters required - analyzes all tablespaces

Required Privileges

SELECT on DBA_FREE_SPACE_COALESCED
Typically requires DBA role

Sample Output

Free Space Coalesced

            TOTAL EXTS     %     TOTAL   BYTES  TOTAL BLOCKS      %
TABLESPACE  EXTS MERGE EXTENT BYTES(KB) MERGE BLOCKS  MERGE  BLOCKS
                       MERGE           (KB)                  MERGE
---------- ----- ----- ------ -------- ------- ------ ------ -------
SYSTEM       156    45   28.8  1234567  356789  15432   4321   28.0
SYSAUX       234    67   28.6  2345678  678901  29315   8487   28.9
USERS         89    12   13.5  3456789  456789  43210   5710   13.2
TEMP          45    15   33.3  4567890 1234567  57112  15432   27.0
UNDOTBS1      78    34   43.6  5678901 2345678  71011  29315   41.3

Key Output Columns

TABLESPACE - Tablespace name
TOTAL EXTS - Total number of free extents
EXTS MERGE - Number of extents that have been coalesced
% EXTENT MERGE - Percentage of extents coalesced
TOTAL BYTES (KB) - Total free space in kilobytes
BYTES MERGE (KB) - Bytes in coalesced extents
TOTAL BLOCKS - Total number of free blocks
BLOCKS MERGE - Number of blocks in coalesced extents
% BLOCKS MERGE - Percentage of blocks coalesced

Understanding the Metrics

Coalescing Effectiveness

High % MERGE (>30%) - Good coalescing, minimal fragmentation
Low % MERGE (<15%) - High fragmentation, many small gaps
UNDO/TEMP - Often show higher merge rates due to cyclic usage

Fragmentation Indicators

Many extents with low merge % = fragmented tablespace
Few extents with high merge % = well-organized space
Compare total extents to merged extents

Common Use Cases

Fragmentation Assessment

@dcontig.sql
-- Look for tablespaces with:
-- High TOTAL EXTS but low % MERGE
-- These need defragmentation

Space Management Evaluation

@dcontig.sql
-- Tablespaces with consistently high merge %
-- indicate good space management
-- Consider for similar configuration

Pre-Maintenance Check

@dcontig.sql
-- Run before major operations
-- Identify tablespaces needing reorganization
-- Plan maintenance windows accordingly

Performance Analysis

Fragmentation Impact

Many Small Extents - Slower space allocation
Poor Coalescing - Increased overhead
Scattered Free Space - Cannot allocate large extents

Improvement Strategies

-- For highly fragmented tablespaces:
-- 1. Export/Import objects
-- 2. Move tables to new tablespace
-- 3. Rebuild indexes
-- 4. Consider locally managed tablespaces

Troubleshooting

Low Coalescing Rates

-- Check for dictionary managed tablespaces
SELECT tablespace_name, extent_management
FROM dba_tablespaces
WHERE extent_management = 'DICTIONARY';

-- Consider converting to locally managed

High Extent Count

-- Identify objects causing fragmentation
SELECT segment_name, count(*) extent_count
FROM dba_extents
WHERE tablespace_name = '&tablespace'
GROUP BY segment_name
ORDER BY extent_count DESC;

Space Allocation Failures

-- Despite free space, cannot allocate
-- Check largest contiguous free chunk
SELECT tablespace_name,
       MAX(bytes)/1024/1024 max_chunk_mb
FROM dba_free_space
GROUP BY tablespace_name;

Best Practices

Tablespace Management

Use locally managed tablespaces
Implement uniform extent sizes
Monitor coalescing trends
Schedule regular reorganization

Preventive Measures

Set appropriate initial extent sizes
Use automatic segment space management
Monitor extent allocation patterns
Plan for growth requirements

Database Space Report (../administration/ddbspace.md) - Overall space usage
Maximum Shrink Analysis (../administration/maxshrink.md) - Reclaimable space
Tablespace DDL Generation (../schema-analysis/qtsddl.md) - Recreate tablespaces

Notes

DBA_FREE_SPACE_COALESCED view may not exist in all Oracle versions
Locally managed tablespaces handle coalescing automatically
Dictionary managed tablespaces require SMON to coalesce
High fragmentation impacts database performance