The Concept of High Water Mark

In Oracle databases, the High Water Mark (HWM) defines the boundary betwean used and unused space within a segment. Frequent data modifications, specifically repeated insertions followed by deletions, often cause the HWM to rise significantly. While logical rows are removed during deletion, the underlying physical blocks remain allocated to the segment unless specific maintenance is performed. This results in inefficient space usage where storage capacity is reserved for non-existent data.

Identifying Elevated Watermarks

Administrators can detect this condition by querying the USER_TABLES data dictionary view. In a healthy segment, there is typically a balanced ratio between allocated blocks and stored row counts. However, if the BLOCKS column retains a high value while NUM_ROWS drastically decreases or reaches zero, it indicates a raised High Water Mark.

Verification Through Testing

1. Verify Block Configuration and Initial State First, determine the default block size configuration and verify that the segment allocation starts at zero.

   SET SERVEROUTPUT ON SIZE 1000000
   COL SEGMENT_NAME FORMAT A30
   
   SELECT SEGMENT_NAME,
          ROUND(BYTES / BLOCKS / 1024, 2) AS BLOCK_SIZE_KB
   FROM USER_EXTENTS
   WHERE SEGMENT_NAME = 'SPACE_TEST_TBL'
     AND ROWNUM = 1;
   
   -- Expected Output: Indicates standard 8KB block size
   

2. Table Creation and Statistics Gathering Create a test structure and capture the initial baseline metrics before inserting data.

   CREATE TABLE SPACE_TEST_TBL (
       ID_COL      NUMBER NOT NULL,
       NAME_COL    VARCHAR2(20),
       POS_COL     VARCHAR2(15),
       MANAGER_ID  NUMBER,
       START_DATE  DATE,
       PAY_AMT     NUMBER(7,2),
       BONUS_AMT   NUMBER(7,2),
       DEP_CODE    NUMBER
   );
   
   BEGIN
       DBMS_STATS.GATHER_TABLE_STATS(
           OWNNAME => USER,
           TABNAME => 'SPACE_TEST_TBL',
           CASCADE => TRUE
       );
   END;
   /
   
   SELECT NUM_ROWS, BLOCKS
   FROM USER_TABLES
   WHERE TABLE_NAME = 'SPACE_TEST_TBL';
   
   -- Expected Result: NUM_ROWS = 0, BLOCKS = 0
   

3. Data Population Load records into the table to force block allocation, then udpate statistics to reflect the new usage.

   INSERT INTO SPACE_TEST_TBL VALUES (1,'SMITH','CLERK',100,SYSDATE,1000,0,10);
   INSERT INTO SPACE_TEST_TBL VALUES (2,'JONES','MANAGER',100,SYSDATE,2000,200,20);
   INSERT INTO SPACE_TEST_TBL VALUES (3,'BLAKE','MANAGER',100,SYSDATE,2850,NULL,30);
   COMMIT;
   
   EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SPACE_TEST_TBL');
   
   SELECT NUM_ROWS, BLOCKS
   FROM USER_TABLES
   WHERE TABLE_NAME = 'SPACE_TEST_TBL';
   
   -- Observation: Row count increases significantly alongside allocated block count
   

4. Block Density Enalysis To understand storage efficiency, query how many rows reside per individual data block using the DBMS_ROWID package.

   SELECT REL_FILE_ID,
          BLK_NUM,
          COUNT(1) AS CNT
   FROM (
       SELECT DBMS_ROWID.ROWID_RELATIVE_FNO(RCID) AS REL_FILE_ID,
              DBMS_ROWID.ROWID_BLOCK_NUMBER(RCID) AS BLK_NUM
       FROM (
           SELECT ROWID RCID FROM SPACE_TEST_TBL
       )
   ) GROUP BY REL_FILE_ID, BLK_NUM;
   
   -- Typical result shows around 170 rows per 8KB block
   

5. Deletion and Re-evaluation Remove all data completely (purge) and check if the blocks return to zero.

   DELETE FROM SPACE_TEST_TBL PURGE;
   
   EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'SPACE_TEST_TBL');
   
   SELECT NUM_ROWS, BLOCKS
   FROM USER_TABLES
   WHERE TABLE_NAME = 'SPACE_TEST_TBL';
   
   -- Critical Finding: NUM_ROWS = 0, But BLOCKS retain previous high allocation
   

A marked disparity between the NUM_ROWS metric and the BLOCKS metric serves as the definitive signifier of a raised High Water Mark. This confirms that while the logical content has been cleared, the physical storage has not been returned to the database pool.