Shrinking Tables, High Water Mark
HWM, is the marking mechanism by which oracle divides a segment
into Used Blocks and Free blocks.
Blocks beyond the HWM dont contain data and oracle reads blocks
upto HWM during Full table scan.
When a table is created, HWM is placed at Extent 0 block 0 for tables
For a Index it is placed at Extent 0 block 1.
During Normal DB operation i.e during Various Updates,Deletes, the
segment gets new blocks,some blocks get modified etc.This results
in increase of HWM, thing to note is that the delete statements do not
modify the HWM. Truncate of table however modifies the HWM
To improve the performance by lowering the HWM, or to reclaim the space
at OS level, we could move the table through the move command i.e
ALTER TABLE <TABLE_NAME> MOVE.
Above will relocate data in the same tablespace, optionally if specified to
different tablespace. Space will be released at object level, if required you
could resize the datafiles.
If Index are present on the table, they will get invalidated since the underlying
table structure has been modified and rowids have been changed.
You have to rebuild the index which will get it validated and will release the space.
ALTER INDEX <INDEX_NAME> REBUILD;