Oracle frees only unused space above the high water mark (that is, the point beyond which database blocks have not yet been formatted to receive data). Oracle deallocates unused space beginning from the end of the object and moving toward the beginning of the object to the high water mark.
Hey , it's not that funny . I think this forum is the place to
let everyone review the technology , raise the issue and make big discussion and improve together . I do read document but as oracle is like a ocean , you are not able to explore every detail . like alter table
deallocate command , it is a confusing point I guess . Unless someone bring it on the table , I think quite a lot oracle guys are not so familar with it .
hmm do you really read the doco, see what it says in SQL reference about this command in the FIRST paragraph
Use the deallocate_unused_clause to explicitly deallocate unused space at the end of the table, partition or subpartition, overflow data segment, LOB data segment, or LOB index and makes the space available for other segments in the tablespace. You can free only unused space above the high water mark (that is, the point beyond which database blocks have not yet been formatted to receive data)