High Level Watermark, PCTFREE and PCTUSED

High Level Watermark(HLW) in datablock:

  • High Level Watermark is a term used with table segment in DB to show the amount of datablock occupied(level) by the table data.
  • As per PCTUSED and PCTFREE mentioned for datablock, the data will fillup in datablocks.
  • When a table created(without data), the High Level Watermark will be on left most datablock(consider the datablocks arrange horizontally).
  • After inserting few records, it fills few datablock, and High Level Watermark will increase.
  • After deleting few intermediate records, it will vacate few datablock, but still the High Level Watermark will be same. ->  Till REBUILD/TRUNCATE/ SHRINK.
  • Once we will TRUNCATE the table the High Level Watermark will be reset to initial position (leftmost datablock).

–> MSSM : Manual Segment Space Management.
-> can define PCTFREE and PCTUSED
–> ASSM : Automatic Segment Space Management.
            -> can only define PCTFREE


Let’s understand with an example:
Suppose PCTUSED = 50 : It is used to find out how much percentage of the block will be used to store rows.  
PCTFREE = 20 : It is used to find out how much percentage of the block will be used to store rows resulting from further updates to the rows in the same datablock.

–> After deleting a few intermediate records, it will vacate few datablock, but still, the High Level Watermark will be the same.  The free list [0: no free space / 1: free space available ] will be updated with vacant space in data block having less than 50% data
— > When UPDATE happens for any records, it will use the space available till PCT Free boundary.

Leave a Comment

Your email address will not be published. Required fields are marked *