Oracle Database - High Water Mark (HWM) - boundary between used and unused space

Card Puncher Data Processing


The High water mark is the boundary between used and unused space:

You can think at the high-water mark (HWM) as the rightmost block that ever contained data in a (data file|segment)


<MATH> \begin{array}{rrl} \text{High Water Mark} & = & \text{TOTAL BLOCKS} - \text{UNUSED BLOCKS} - 1 \\ & = & \text{USER_SEGMENTS.BLOCKS} - \text{USER_TABLES.EMPTY_BLOCKS} - 1 \end{array} </MATH>

select file_id, max(block_id+blocks-1) hwm
from dba_extents
group by file_id

The DBMS_SPACE.UNUSED_SPACE returns values for USED BLOCKS and TOTAL BLOCK for a segment. Calculate the HWM as (TOTAL_BLOCKS – USED BLOCKS).



  • rebuilt,
  • truncated, (TRUNCATE will reset the HWM of a table back to 'zero')
  • or shrunk (shrinking of a segment is a new Oracle 10g feature that is supported only if the segment is in an ASSM tablespace).


DBMS_SPACE.SPACE_USAGE procedure shows the space usage of data blocks under the segment High Water Mark.



You have to understand Oracle storage.

Each table is made up of logisch segment that are made of physique extents and each extent is made up of oracle blocks - a common block size is 8k. So you have a table with 10 extents (80K).

You populate your table with 2 million rows of data and you will have many hundreds of extents. Now lets assume that you delete over half of the records in the table. Oracle still has the same number of extents but many of the blocks are empty. When you run a query against the table Oracle will scan through all the blocks including empty ones looking for data.

So you can think of the total number of extents / blocks used as the high water mark.


The high water mark divide a segment into used blocks free blocks

Blocks below the high water mark (used blocks) have at least once contained data. This data might have been deleted. Since Oracle knows that blocks beyond the high water mark don't have data, it only reads blocks up to the high water mark in a full table scan. Oracle keeps track of the high water mark for a segment in the segment header. Moving the high water mark In normal DB operations, the high water mark only moves upwards, not downwards. The exceptions being the truncate. If there is a lot of free space below the high water mark, one might consider to use alter table move statements. See On shrinking table sizes.


Good documentation

Documentation / Reference

Discover More
Card Puncher Data Processing
Oracle Database

Documentation about the Oracle database
Oracle Segment Extent Data Block
Oracle Database - (Data|Db|Logical|Oracle) Block or Page

Articles which talk : block management. ?? At the finest level of granularity in the logical structure of an Oracle Database, the data is stored in data blocks. The data block sizes should be a...
Oracle Segment Extent Data Block
Oracle Database - (Fragmented) (Unused|Free) space (Reclaim|Shrink|resize)

Used and free space. A data file contains ordered extents (by block Id). One segments is made up of one of several extents (not ordered, contiguous). Over time, updates and deletes on objects within...
Card Puncher Data Processing
Oracle Database - Buffer IO (Logical IO)

A buffer is a container for data. A logical I/O, also known as a buffer I/O, refers to reads and writes of buffers in the buffer cache. When a requested buffer is not found in memory, the database performs...
Card Puncher Data Processing
Oracle Database - DBMS_SPACE Package

The UNUSED_SPACE procedure of the DBMS_SPACE package returns space information on a segment: the position of the high water mark (ie the last block within the extent which contains data) and the amount...
Card Puncher Data Processing
Oracle Database - Data Files

Every Oracle database has one or more physical datafiles (OS File), which contain all the database data. The data of logical database structures, such as tables and indexes, is physically stored in the...
Card Puncher Data Processing
Oracle Database - Direct (path insert|load) (/*+ APPEND */)

A direct-path insert is also known as: direct load A direct-path insert is a bulk operation which will only bypass redo log generation in three cases : the database is in NOARCHIVELOG mode database...
Card Puncher Data Processing
Oracle Database - Full Table Scans

in Oracle During a full table scan, all blocks in the table that are under the high water mark are scanned. Each row is examined to determine whether it satisfies the statement's WHERE clause. When Oracle...
Card Puncher Data Processing
Oracle Database - Optimizer Statistics

Optimizer statistic in Oracle You can collect exact or estimated statistics physical storage characteristics and data distribution in these schema objects by using the DBMS_STATS...
Card Puncher Data Processing
Oracle Database - Table Size

How to calculate the space of a table ? Then: The above shows us: we have 55 blocks allocated to the table 53 blocks are totally empty (above the HWM) 1 block contains data (the other block...

Share this page:
Follow us:
Task Runner