High Water Mark in Oracle

What is High Water Mark in Oracle?

High water mark is the maximum amount of database blocks used so far by a segment (table/index etc). We know that when we are inserting data into a segment it will be placed in blocks. So whenever data is added to segment further more blocks will be utilized/allocated. 

For example assume a segment having data a,b,c,d,e shown as below.  Now as per my definition the maximum logical mark utilized by a segment is high water mark. Here my high water mark is from the block (with data a) to block (with data e).

a

b

c

d

E

1. Now some of data is deleted from the segment like below.

a

b

c

D

a

b

c

E

Here since we are doing deletion operation the data will be removed from the block but the block can’t be deallocated from the segment. Hence physically it means it is still using all the blocks. So the high water mark won’t change. High water mark is same as previous.

2. In the below case I am adding some more data to the existing segment. So the high water mark increases furthermore.

a

b

c

e

f

G

3. Now we shall again delete some data from the segment. From case-1 we already know that high water mark can’t be changed or can’t be reset by deletion operation, it will remain same.

a

b

c

e

f

4. if we perform any re-org activity on the segment then the blocks will be aligned with data in proper order and un using blocks will release space.so the high water mark changes now as below from block with data to block with data f .

a

b

c

e

F

5. Similarly truncation of segment also releases space and high water mark will change to initial state when table is created. To know the difference between truncation and deletion see

http://orcl11gdba.blogspot.in/2014/03/truncate-vs-deletion-of-table.html

cheers….!!!!!