High Water Mark - Some Useful Information to remember

High Water Mark

• The high water mark is the boundary between used and unused space in a segment. As requests for new free blocks that cannot be satisfied by existing free lists are received, the block to which the high water mark points to becomes a used block, and the high water mark is advanced to the next block. In other words, the segment space to the left of the high water mark is used, and the space to the right of it is unused.

• The high-water mark is the level at which blocks have never been formatted to receive data.

• When a table is created in a tablespace, some initial number of blocks/extents are allocated to the table. Later, as the number of rows inserted increases, extents are allocated accordingly.

• Inserting records into the table would increase the high water mark.

• Deleting the records does not lower the high water mark. Therefore, deleting the records does not raise the 'Empty_blocks'. After deleting the records, if you query dba_segments or dba_tables, there would be no change.

• ALTER TABLE <TABLE_NAME> DEALLOCATE UNUSED; >>> would not bring the high water mark down.

• The high water mark can be reset with a truncate table or if the table is moved to another tablespace, or with Shrink Space.

           SQL> ALTER TABLE <tablename> SHRINK SPACE;

• The ALTER TABLE MOVE can even be a good method to optimize the HWM even if the move occurs within the same tablespace

• High Water Mark After Exporting/Deleting/Importing a Table >>> NO, the HWM is not reset.

• All Oracle segments have an upper boundary containing the data within the segment. This upper boundary is called the "high water mark" or HWM.
• The high water mark is an indicator that marks blocks that are allocated to a segment, but are not used yet. It is reset to ""zero"" (position to the start of the segment) when a TRUNCATE command is issued.  So you can have empty blocks below the high water mark, but that means that the block has been used (and is probably empty caused by deletes). Oracle does not move the HWM, nor does it *shrink* tables, as a result of deletes."
• Data files do not have a high water mark; only segments do have them.
• Full table scans typically read up to the high water mark.