pramodmann

 Profile Answers by pramodmann 

• May 27th, 2014

 

1. High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.

2. Delete Table operation wont reset HWM.

3. TRUNCATE will reset HWM.

4. The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.

chandu

• Dec 8th, 2014

 

logical line bt/w used blocks and unused blocks

saumitra

• Feb 9th, 2015

 

The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.

The blocks below the HWM level is used blocks, they are already used.

for example, if you delete some huge records from the database, that data will delete but the blocks are not ready to used, because that blocks are still below HWM level, so delete command never reset the HWM level,

At the same time you truncate the date, then the data will delete and that used blocks will goto above the HWM level, now its ready to used. now they consider has free blocks.

Prabhat Sahu

• Oct 4th, 2017

 

-- High Level Watermark:
-------------------------
--> HLW 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 HWM level will be on left most datablock(consider the datablocks arrange horizontally).
--> After inserting few records, it fill few datablock, and HWM will increase.
--> After deleting few intermediate records, it will vacate few datablock, but still the HWM will be same. -> Till REBUILD/TRUNCATE/ SHRINK.
--> Once we will TRUNCATE the table the HWM will be on reset to initial position (left most datablock).
--> After creating a table(when its empty)
The HWM will be on left side.
|
v
+--+--+--+--+--+--+--+--+--+--+
| | | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--+--+--+--+--+--+--+--+--+--+
--> After inserting few records, it fill few datablock, and HWM will increase.
|
v
+--+--+--+--+--+--+--+--+--+--+
| | | | | | | | | | |
|xx |xx|xx|xx|xx |xx|xx | | | |
|xx |xx|xx|xx|xx |xx|xx | | | |
+--+--+--+--+--+--+--+--+--+--+
--> After deleting few intermediate records, it will vacate few/part of datablock,
but still the HWM will be same. -> Till REBUILD/TRUNCATE/ SHRINK.
|
v
+--+--+--+--+--+--+--+--+--+--+
| | | | | | | | | | |
| |xx| |xx| |xx| | | | |
|xx |xx| |xx|xx|xx|xx | | | |
+--+--+--+--+--+--+--+--+--+--+
--> Once we will TRUNCATE the table the HWM will be on reset to initial position (left most datablock).
The HWM will be reset to left most side datablock.
|
v
+--+--+--+--+--+--+--+--+--+--+
| | | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--+--+--+--+--+--+--+--+--+--+

Prabhat Sahu

 Profile Answers by Prabhat Sahu 

• Oct 15th, 2018

 

-- High Level Watermark:
-------------------------
--> HLW 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 HWM level will be on left most datablock(consider the datablocks arrange horizontally).
--> After inserting few records, it fill few datablock, and HWM will increase.
--> After deleting few intermediate records, it will vacate few datablock, but still the HWM will be same. -> Till REBUILD/TRUNCATE/ SHRINK.
--> Once we will TRUNCATE the table the HWM will be on reset to initial position (left most datablock).
--> After creating a table(when its empty)
The HWM will be on left side.
|
v
+--+--+--+--+--+--+--+--+--+--+
| | | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--+--+--+--+--+--+--+--+--+--+
--> After inserting few records, it fill few datablock, and HWM will increase.
|
v
+--+--+--+--+--+--+--+--+--+--+
| | | | | | | | | | |
|xx |xx|xx|xx|xx |xx|xx | | | |
|xx |xx|xx|xx|xx |xx|xx | | | |
+--+--+--+--+--+--+--+--+--+--+
--> After deleting few intermediate records, it will vacate few/part of datablock,
but still the HWM will be same. -> Till REBUILD/TRUNCATE/ SHRINK.
|
v
+--+--+--+--+--+--+--+--+--+--+
| | | | | | | | | | |
| |xx| |xx| |xx| | | | |
|xx |xx| |xx|xx|xx|xx | | | |
+--+--+--+--+--+--+--+--+--+--+
--> Once we will TRUNCATE the table the HWM will be on reset to initial position (left most datablock).
The HWM will be reset to left most side datablock.
|
v
+--+--+--+--+--+--+--+--+--+--+
| | | | | | | | | | |
| | | | | | | | | | |
| | | | | | | | | | |
+--+--+--+--+--+--+--+--+--+--+