Problem in deallocating unused space

[M.Shakeel Azeem]

I am trying to deallocate space within a table by adopting the following steps
1-SQL>alter table HR_ATTENDANCE compute statistics;

2-select avg_space, blocks, empty_blocks
from user_tables
where table_name ='HR_ATTENDANCE'

AVG_SPACE BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
4937 180 76

3-alter table HR_ATTENDANCE deallocate unused;

4-alter table HR_ATTENDANCE compute statistics;
5-repeat the step 2 and still found

AVG_SPACE BLOCKS EMPTY_BLOCKS
---------- ---------- ------------
4937 180 76
EMPTY_BLOCKS are blocks above the HWM
and when we use deallocate unused then empty_blocks above the HWM but i am still having unused blocks before and after deallocating unused space
What could be the reason?
Can anybody help me in this regard?
thanx in advance

[Bore]

The reason is that the empty blocks are below the HWM but they are not compacted.
That means that when you issue Deallocate unused, it starts dealocating until an used block is met. There may be other unised blocks below the used one, but they cannot be deallocated. Look below - E means empty U means full
EEEEUUEEEEUUUUEEEUEE| <-HWM
So, if you start deallocating, you'll deallocate the most left 2 Es but then you meed U and can't go further.
If you use 10g, you could do that using
alter table T shrink space; if I am not wrong

Regards
Boris

[jmodic]

Look below - E means empty U means full
EEEEUUEEEEUUUUEEEUEE| <-HWM
So, if you start deallocating, you'll deallocate the most left 2 Es but then you meed U and can't go further.

Boris, I think you've misunderstood how DEALOCATE UNUSED works. It frees the blocks above the HWM, not below. So in your example, not even the two rightmost (that "most left 2" in your post must be a typo, you've certaly ment "most right 2") blocks would be affected, since they are below the HWM.

EMPTY_BLOCKS are blocks above the HWM and when we use deallocate unused then empty_blocks above the HWM but i am still having unused blocks before and after deallocating unused space
What could be the reason?

Can you check what is the setting of the INITIAL, NEXT and MINEXTENTS for this table? It looks like your initial extent (or the combined size of your MINEXTENTS) is set to 256 blocks and if that is true, Oracle can not dealocate any space from this.

[Bore]

Well, I would agree. I really messed it up. Sorry for misleading post.

[LKBrwn_DBA]

Oracle (8i, 9i) will de-allocate only up to the HWM or "initial extent" size whichever is first, even if the HWM is bellow this segment, try using the KEEP option with small size to find out:

alter table HR_ATTENDANCE deallocate unused KEEP 64k;

Even though a small size will produce error, It will deallocate to the true HWM.