High water mark is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.
When an INSERT statement cannot find a free block to populate, it goes straight to the High Water Mark and allocates a new block.When rows are deleted, the High Water Mark does not come down.
Inorder to remove the empty blocks in the HWM you need to issue the following two commands
ALTER TABLE emp ENABLE ROW MOVEMENT;
ALTER TABLE emp SHRINK SPACE;
ROWIDs are normally assigned to a row for the life time of the row at insert time. Oracle can move a table row using the first command .
It will read the table, it will delete/insert the rows at the bottom of the table to move them up, it will generate redo, it will generate undo thus can relocate and reorganize rows in a table including it will change rowids
The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,but for a very short moment only.
- can be done online
- indexes will be maintained and remain usable
When an INSERT statement cannot find a free block to populate, it goes straight to the High Water Mark and allocates a new block.When rows are deleted, the High Water Mark does not come down.
Inorder to remove the empty blocks in the HWM you need to issue the following two commands
ALTER TABLE emp ENABLE ROW MOVEMENT;
ALTER TABLE emp SHRINK SPACE;
ROWIDs are normally assigned to a row for the life time of the row at insert time. Oracle can move a table row using the first command .
It will read the table, it will delete/insert the rows at the bottom of the table to move them up, it will generate redo, it will generate undo thus can relocate and reorganize rows in a table including it will change rowids
The second step adjusts the high watermark. For this Oracle needs an exclusive table lock,but for a very short moment only.
- can be done online
- indexes will be maintained and remain usable
No comments:
Post a Comment