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;