How to Reset High Water Mark (HWM) and Remove Table Fragmentation in Oracle

Table fragmentation is a common issue in Oracle databases caused by frequent DELETE and UPDATE operations. Even after deleting large amounts of data, the space is not immediately reused, leading to wasted storage and performance issues.

High Water Mark (HWM) defines the boundary up to which Oracle scans blocks during a full table scan.

What is High Water Mark (HWM)?

HWM represents the highest block ever used in a table. During a full table scan, Oracle reads all blocks up to HWM—even if many blocks are empty.

• DML (DELETE/UPDATE) does not reduce HWM
• DDL operations can reset HWM
• Higher HWM = more I/O = slower performance

Step 1: Create Test Table

SQL> CREATE TABLE MEMO AS SELECT * FROM PAYROLL_FILE;

Table created.

Gather Statistics

SQL>ANALYZE TABLE MEMO COMPUTE STATISTICS;

Table analyzed.

Check Table Usage

SQL> SELECT blocks "Ever Used",empty_blocks "Never Used",
num_rows "Total Rows" FROM user_tables WHERE table_name='MEMO';

Ever Used  Never Used  Total Rows
---------- ----------- ----------
154        18          3680

Step 2: Delete Data (Create Fragmentation)

SQL> DELETE FROM MEMO WHERE owner='HRONE';

1784 rows deleted.

SQL> COMMIT;

Recheck Statistics

SQL>ANALYZE TABLE MEMO COMPUTE STATISTICS;

Table analyzed.

SQL>SELECT blocks "Ever Used",empty_blocks "Never Used",
num_rows "Total Rows" FROM user_tables WHERE table_name='MEMO';

Ever Used  Never Used  Total Rows
---------- ----------- ----------
154        18          1896

Even after deleting ~50% data, HWM remains same → Oracle still scans 154 blocks.

Check Fragmentation Level

Table Size

SQL> SELECT table_name,ROUND((blocks*8),2)||'kb' "SIZE" FROM 
user_tables WHERE table_name='GPAY_PAYMENT_MSTR';

TABLE_NAME            SIZE
--------------------- ----------
GPAY_PAYMENT_MSTR     14376kb

Actual Data Size

SQL>SELECT table_name,ROUND((num_rows*avg_row_len/1024),2)||'kb' "SIZE"
FROM user_tables WHERE table_name='GPAY_PAYMENT_MSTR';

TABLE_NAME            SIZE
--------------------- ----------
GPAY_PAYMENT_MSTR     9248.96kb

Wasted Space = 14376 - 9248.96 = ~5127 KB (~35% fragmentation)

Methods to Reset HWM and Remove Fragmentation

• ALTER TABLE MOVE + Rebuild Indexes
• CREATE TABLE AS SELECT (CTAS)
• DBMS_REDEFINITION (Online)
• Segment Shrink (10g+)

Method 1: ALTER TABLE MOVE

SQL> ALTER TABLE GPAY_PAYMENT_MSTR MOVE;

Table altered.

SQL> ALTER INDEX GPAY_PAYMENT_MSTR_PK REBUILD;

Index altered.

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('HRONE','GPAY_PAYMENT_MSTR');

PL/SQL procedure successfully completed.

Verify Size

SQL> SELECT table_name,ROUND((blocks*8),2)||'kb' "SIZE"
FROM user_tables WHERE table_name='GPAY_PAYMENT_MSTR';

GPAY_PAYMENT_MSTR    11376kb

MOVE resets HWM but makes indexes UNUSABLE → must rebuild indexes.

Method 2: CTAS (Create Table As Select)

SQL> CREATE TABLE GPAY_PAYMENT_MSTR_TEMP AS
SELECT * FROM GPAY_PAYMENT_MSTR;

Table created.

SQL> DROP TABLE GPAY_PAYMENT_MSTR PURGE;

Table dropped.

SQL> RENAME GPAY_PAYMENT_MSTR_TEMP TO GPAY_PAYMENT_MSTR;

Table renamed.

Post Step

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS('HRONE','GPAY_PAYMENT_MSTR');

SQL> SELECT status FROM user_indexes WHERE table_name='GPAY_PAYMENT_MSTR';

no rows selected

All indexes are dropped → must recreate indexes manually.

Method 3: SHRINK SPACE (Best for Online Operation)

Pre-requisite: ASSM tablespace + Row Movement enabled

SQL> ALTER TABLE MEMO ENABLE ROW MOVEMENT;

Table altered.

Step 1: Compact Data

SQL> ALTER TABLE MEMO SHRINK SPACE COMPACT;

Table altered.

Step 2: Reset HWM

SQL> ALTER TABLE MEMO SHRINK SPACE;

Table altered.

Benefits of SHRINK

Online operation (no downtime)
Indexes remain usable
No extra space required
Best method for production systems

Performance Impact

• Reduced full table scan I/O
• Better cache utilization
• Improved query performance

Best Practices

• Check fragmentation before action
• Use SHRINK for online systems
• Use MOVE for large reorganization
• Always gather stats after operation
• Monitor index status after MOVE

In production, a payroll table had 40% fragmentation due to daily deletes. Full table scans were slow. After applying SHRINK SPACE, performance improved by 30% and storage usage reduced significantly.

HWM does not reduce after DELETE.
Fragmentation increases I/O and reduces performance.
ALTER MOVE resets HWM but requires index rebuild.
SHRINK is best online solution in 10g+.

Conclusion

Understanding High Water Mark and fragmentation is critical for Oracle DBAs. Choosing the right reorganization method ensures optimal performance, efficient storage utilization, and stable production systems.

Please feel free to ask. thank you 🙂

Toufique Khan