Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 







Table high water mark tips

Oracle Database Tips by Donald BurlesonFebruary 3, 2015

Question: Can you please explain the concept of a table high water mark (HWM)?  I understand that the data is always below the high water mark.  Can you show a script to display the high water mark for a table?

Answer:  The high water mark (HWM) for an Oracle table is a construct that shows the table at its greatest size.  Just as a lake has a high-water mark after a draught, an Oracle table has a high water mark that shows the greatest size of the table, the point at which it consumed the most extents.

Also see this great script to display all high water marks.

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 30 seconds to read.  After deleting 900,000 rows, a full scan on the table will still take 30 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 thought Oracle may be reading through many empty blocks that were allocated to the table, used for rows, and then deleted.

As such, 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:

   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;

This example script will query the dba_tables and dba_segments to show the table highwater mark:

set verify off
column owner format a10
column alcblks heading 'Allocated|Blocks' just c
column usdblks heading 'Used|Blocks'      just c
column hgwtr heading 'High|Water'         just c
break on owner skip page
    b.blocks                        alcblks,
    a.blocks                        usdblks,
    (b.blocks-a.empty_blocks-1)     hgwtr
    dba_tables a,
    dba_segments b
    and a.owner=b.owner
    and a.owner not in('SYS','SYSTEM')
    and a.blocks <> (b.blocks-a.empty_blocks-1)
    and a.owner like upper('&owner')||'%'
    and a.table_name like upper('&table_name')||'%'
order by 1,2;

Please see these other scripts to display the high water mark for a table.

The remedy for a too-high high water mark (e.g. a fragmented table will lots of empty blocks) is to reorganize the table with Data Pump (expdp and impdp), the dbms_redefinition utility, or with the table shrink or coalesce  Also see my notes on method for lowering a table high water mark.

Also see my notes on sessions high water mark

Oracle Training from Don Burleson 

The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!

Oracle training



Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster