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

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)

## 3 - Calculation

$$\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}$$

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).

## 4 - Management

### 4.1 - Reset

• 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).

### 4.2 - Space

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

## 5 - Definition

### 5.1 - First

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.