How do we identify the High Water Mark (HWM)?

Option 1 

We can use DBMS_SPACE.UNUSED_SPACE procedure. 

How do we reset the HWM? 

Option 1 
Alter table emp move tablespace 

This option requires rebuilding the indexes. since the index will be invalid/unusable after running the above command. The downside is, rebuilding the index is additional overhead when we use this option. Also users can not use the application or reports while rebuilding the index. 

Option 2 

1. Export the data 
2. truncate the table 
3. import the table 
4. Analyze the table 

Option 3 
1. copy the table data 
2. truncate the original table 
3. insert back. 

Option 4 
Use DBMS_REDEFINITION package to copy the table. 

Option5 
In oracle10g, we have better option to reset the high water mark. We need to enable row movement before we shrink the space. We do not need to rebuild the index after reseting the HWM. This is the best option to reset the HWM. But this feature is not available in oracle9i. 


sql>alter table emp enable row movement; 

Table altered. 


 sql>alter table emp shrink space compact;
 
Table altered.