In the world of Oracle databases, the High Water Mark (HWM) is a critical concept that often flies under the radar for many professionals. It represents the boundary between the used and unused space in a database segment (like a table). When not managed properly, a high HWM can lead to inefficient space usage and degraded performance during full table scans.
How to Identify the High Water Mark:
1️⃣ Analyze the Table to Collect Statistics
ANALYZE TABLE COMPUTE STATISTICS;
2️⃣ Query DBA Tables to Check HWM Information
The DBA_SEGMENTS view provides information about allocated space, while DBMS_SPACE can give more detailed insights.
SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES/1024/1024 AS ALLOCATED_MB
FROM DBA_SEGMENTS
WHERE SEGMENT_NAME = ”;
To see unused space below the HWM:
DECLARE
total_blocks NUMBER;
used_blocks NUMBER;
free_blocks NUMBER;
BEGIN
DBMS_SPACE.UNUSED_SPACE (
segment_owner => ”,
segment_name => ”,
segment_type => ‘TABLE’,
total_blocks => total_blocks,
total_bytes => NULL,
unused_blocks => free_blocks,
unused_bytes => NULL
);
DBMS_OUTPUT.PUT_LINE(‘Unused Blocks: ‘ || free_blocks);
END;
/
Steps to Rectify High Water Mark Issues:
🔹 Step 1: Shrink the Table to Reclaim Space
ALTER TABLE SHRINK SPACE;
🔹 Step 2: Rebuild the Table if Shrinking Is Not Feasible
CREATE TABLE AS SELECT * FROM ;
DROP TABLE ;
RENAME TO ;
🔹 Step 3: Monitor Regularly and Schedule Maintenance
Use tools like Oracle Enterprise Manager or scripts to track table growth and HWM trends over time.
High Water Mark management is a crucial part of Oracle database tuning and space optimization. By understanding and addressing HWM, we ensure that our systems perform efficiently and handle growth effectively.