Hi Vikram ,

Here is an excerpt from note 77635.1.

What is High Water Mark?
--------------------------------------------
All Oracle segments have an upper boundary containing the
data within the segment. This upper boundary is called the
"high water mark" or HWM. The high water mark is an
indicator that marks blocks that are allocated to a segment,
but are not used yet. This high water mark typically bumps
up at 5 data blocks at a time. It is reset to "zero"
(position to the start of the segment) when a TRUNCATE
command is issued. So you can have empty blocks below
the high water mark, but that means that the block has been
used (and is probably empty caused by deletes). Oracle does
not move the HWM, nor does it *shrink* tables, as a result
of deletes. This is also true of Oracle8. Full table scans
typically read up to the high water mark. Data files do not
have a high water mark; only segments do have them.

How to determine the high water mark ?
-----------------------------------------------------------
To view the high water mark of a particular table::
ANALYZE TABLE <tablename> ESTIMATE/COMPUTE STATISTICS;
This will update the table statistics. After generating the statistics,
to determine the high water mark: SELECT blocks, empty_blocks,
num_rows FROM user_tables WHERE table_name = <tablename>;
BLOCKS represents the number of blocks 'ever' used by the segment.
EMPTY_BLOCKS represents only the number of blocks above
the 'HIGH WATER MARK' . Deleting records doesn't lower the
high water mark. Therefore, deleting records doesn't raise the
EMPTY_BLOCKS figure.

HTH.

Regards,
Prem.

>>-----Original Message-----
>>From: [Email Address Removed] ,
>>Can anyone please explain .
>>What is HIGH WATER MARK ?

Oracle LazyDBA home page