Oracle High Water Mark

A high water mark is the set of blocks that have at one point contained data. You might have 1000 blocks allocated to a table but only 500 are under the High Water Mark. The blocks under the HWM are the blocks that will be read when the table is fully scanned.
The (HWM) high water mark for an Oracle table is a construct that shows the table at its greatest size. The Oracle table has a high water mark that shows the greatest size of the table, the point at which it consumed the most extents.
As a table undergoes deletes and updates, rows shrink and table data blocks become empty. For performance reasons, Oracle keeps the high water mark for a table rather than re-calculate the high water mark after blocks at the "end" of the table (the last extent) becomes empty.
For example assume that you have a million row table that takes 29 seconds to read. After deleting 897,000 rows, a full scan on the table will still take 29 seconds. This is because the table high water mark is not re-set after delete operations.

The issue with the high water mark is that full-table scans will always read up to the high water mark, even though Oracle may be reading through many empty blocks that were 1) allocated to the table, 2) used for rows, and 3) then deleted. Therefore, there are no easy SQL scripts that will reveal the high water mark for an Oracle table, but you can assume that it is the last extent that was allocated to the table for estimation purposes. Here is a simple query to find the high water mark for a table:

select 
   a.tablespace_name, 
   a.file_name, 
   ceil( (nvl(hwm,1)*8192)/1024/1024 ) "Mo" from dba_data_files a,
     ( select file_id, max(block_id+blocks-1) hwm
       from dba_extents
       group by file_id
     ) b 
where a.file_id = b.file_id(+)
order by tablespace_name, file_name;