The high water mark refers to the page number
of the first page in the extent following the last allocated extent.
For example, if a table space has 1000 pages and an extent size
of 10, there are 100 extents. If the 42nd extent is the highest allocated
extent in the table space that means that the high-water mark is 420.
Tip: Extents are indexed from 0. So the high water mark is the last
page of the highest allocated extent + 1.
Practically
speaking, it's virtually impossible to determine the high water
mark yourself; there are administrative views and table functions
that you can use to determine where the current high water mark is,
though it can change from moment to moment as row operations occur.
Note that the high water mark is not an indicator of the number
of used pages because some of the extents below the high-water mark
might have been freed as a result of deleting data. In this case,
even through there might be free pages below it, the high water mark
remains as highest allocated page in the table space.
You can lower the high water mark of a table space by consolidating
extents through a table space size reduction operation.
Example
Figure 1 shows a series of allocated
extents in a table space.
Figure 1. High water mark
When an object is dropped, space is freed in the table space.
However, until any kind of storage consolidation operation is performed,
the high water mark remains at the previous level. It might even move
higher, depending how new extents to the container are added.