ORACLE: Reset High Water Mark

What is "Reset High Water Mark for the following tables...开发者_如何学JAVA" in Oracle?

The High Water Mark is the maximum number of blocks which have ever contained data. Deleting records from a table frees up the space but does not move the HWM. In order to reset the High Water Mark we have to drop and recreate the table, or just truncate it.

Normally we wouldn't bother. However, the High Water Mark defines the range of a full table scan. So if we have a table which once contained a lot of records but no longer does so and also is subject to frequent full table scans then perhaps reseting the HWM would be a good use of our time.

If (and that's a big if), you really need to move the HWM down, you can use

alter table X move

which may necessitate associated index rebuilds, or

alter table X shrink space

depending on version, tablespace management scheme etc.