Alter Table Shrink Taking too Long

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:

  1. Unformatted
  2. 0 to 25 % empty
  3. 25% to 50% empty
  4. 50% to 75% empty
  5. 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.