High Water Mark concept

[farrokhp]

I know that the high water mark is the boundary between used and unused space in a segment and it means that segment space to the left of the high water mark is used, and the space to the right of it is unused.

But I need and example in the real world to absorb what does it mean exactly. Could somebody bring an example that HWM increase a lot and coalse or shrink doesn't help.

How can we adjust HWM for a table again?

Is it more important than no. of extents and other segment parameters?

Do we need to look at HWM daily?

Please clarify the confusion

[nirasha]

I do not know too much on HWM, but I will tell you some of what I do.

Say if you want to delete all the rows of a table, if you use the delete command, and use a select count(*) from that table, it takes an awfully long time, because it still refers to the HWM. If you truncate a table, a select count(*) is faster because truncate resets the HWM, so the system knows there is nothing (that's why truncate is refered to as DDL).

That's as much as I know. Hope it was some help.

Good luck,
Nirasha

[shawish_sababa]

Hi nirasha

first, no way to adjust HWM for a table again other than Truncate. delete will not change the hwm, nor any process or command (as far as i now in oracle 8.0. or 7.3)

second, look at mealink, doc number 1029850.6 it is about freelist, but there you can read about hwm in the context of space management in tables.

hope this helps

[mbaliga]

Hi,

Suppose you create a table with 1000 records and it it uses up 100 blocks. Then the high water mark is set at block 100. Now if you delete records from this table but still the high waater mark is set at block 100. The deleted space will be reused based on the PCTUSED parameter.

If you want to reset the mark then you need to export the data, recreate the table and import the data.

Hope this helps.

Baliga

[farrokhp]

From your answers it seems to me that HWM should take under consideration only if we have awful amounts of delete because it never return back to real end of records. Otherwise we it is harmless
Is that what you mean. Is it apply only to tables segments??

Is it possible we have lots of free space in TS or Datafiles but just because of HWM which at some point in the past fly high we got error "Not enough Space" is that what you mean??

More clarification please.

[badrinathn]

Baliga,
To add to the question:
In case I have no deletes on the table and have only inserts, in that case will the value under HWM gives me the number of blocks used by that table. And is this another way of knowing the total blocks(Or size) of the table
Badrinath