How to Reclaim Oracle High Water Mark Space and Boost Query Performance

High Water Mark (HWM) is an Oracle segment‑level concept; during DML operations such as INSERT or DELETE the HWM only increases and never decreases, leaving empty blocks below the mark when rows are deleted.

Because Oracle allocates a segment and blocks when a table first receives data, the HWM can keep rising as more data is inserted, even after deletions, leading to wasted space and larger tablespace files.

Why Reclaim HWM?

Lowering the HWM reduces the amount of space a segment occupies, preventing unnecessary tablespace file growth.

Oracle SELECT scans all blocks up to the HWM; excess empty blocks degrade query performance.

Procedure to Reclaim HWM

After consulting references and testing, the following steps were applied to the BK_OPT_CUST_HOLD_DETAIL table in the TX_DATA_TBS tablespace.

Pre‑operation status check: record free space, tablespace file size, row count, index status, and current HWM block count.

Execute the HWM reclamation using ALTER TABLE ... SHRINK SPACE with the "row movement" feature enabled.

Post‑operation status check: compare the same metrics to verify that data and indexes are unchanged while space usage has decreased.

Key commands and observations:

-- Check free space (e.g., 721 MB)
SELECT free_space FROM dba_tablespace_usage_metrics WHERE tablespace_name='TX_DATA_TBS';

-- Check tablespace file size (e.g., 9900 MB)
SELECT bytes/1024/1024 FROM dba_data_files WHERE tablespace_name='TX_DATA_TBS';

-- Enable row movement
ALTER TABLE BK_OPT_CUST_HOLD_DETAIL ENABLE ROW MOVEMENT;

-- Shrink space
ALTER TABLE BK_OPT_CUST_HOLD_DETAIL SHRINK SPACE;

-- Disable row movement
ALTER TABLE BK_OPT_CUST_HOLD_DETAIL DISABLE ROW MOVEMENT;

Before shrink, the segment occupied 122,234 blocks (≈955 MB). After shrink, it used only 599 blocks (≈4.875 MB), freeing 121,635 blocks and about 950 MB of space. The tablespace free space increased from 721 MB to 1,671 MB, matching the reclaimed amount.

Note that the tablespace file size remains unchanged (still 9,900 MB) because SHRINK SPACE only affects segment allocation; to reduce the file size, a separate tablespace‑level RESIZE operation is required.

Summary

Shrinking space reclaims empty blocks below the HWM, reducing space usage and improving scan performance without affecting table data or indexes.

The operation works at the table/segment level; it does not shrink the physical tablespace file.

HWM reclamation is a DDL task that must be performed by a DBA, not by application code, and should be scheduled regularly.