When you execute “alter table shrink <segment>” , there is no direct way to tell you how long it will take. A helpful aid to determine when shrinking will finish is to use dbms_space.space_usage package. This function will show you block activity between LHWM and HHWM. I wrote a pipelined function to see output of this function and you can find it here. You’d appreciate this little pipelined function instead of using so many dbms_output.put_line functions.
As of 10g, Oracle introduced LHWM and HHWM.
HHWM is the same as HWM in prior versions which is all blocks above this mark have not been formatted.
LHWM (Low High Water Mark) which all blocks below this mark have been formatted.
Between LHWM and HHWM there are 5 categories of blocks:
- 0 to 25 % empty
- 25% to 50% empty
- 50% to 75% empty
- 75% to 100% empty
You can use dbms_space.space_usage to report on the area between LHWM & HHWM
What to look for?
If you are shrinking a table, you will see the following behavior:
|Unformatted Blocks||Number of blocks will not change|
|0 to 25% blocks||Number of blocks will decrease until it becomes zero|
|25% to 50% blocks||Number of blocks will decrease until it becomes zero|
|50% to 75% empty||Number of blocks will decrease until it becomes zero|
|75% to 100% empty||Number of blocks will increase|
|Full Blocks||This is number of blocks below LHWM (actual table size) and will increase as the other numbers will decrease|
Shrinking index behavior is different. Full blocks will decrease first, while other block categories will increase, then Full blocks will increase again.