How to Decrease the High Water mark..

Does any one know is there a way to Decrease the High water mark...

I don't want to Truncate or Drop that table...
What about Alter Table Deallocate Unused, Will it Bring the HWM down or any other statement or is any Disadvantage for that Statement.

Thanks

Why dont you try that, and get it confirmed by querying user_tables after running analyze table command.

There is no harm in running alter table deallocate ...
I hope so.

--

Using the deallocate wouldn't reduce the hig water mark. What it would do is that it would release the unused space that are above the high water mark. Also consider using the KEEP.

To release the space below the high-water mark even if the high water mark is below MINEXTENTS use KEEP 0

Hope this would help you. Good luck,
Sam

DEALLOCATE will not bring down HWM.
You need to truncate or recreate the table again.

In 8i you can reduce the high water mark with ALTER TABLE ... MOVE:

ALTER TABLE emp MOVE TABLESPACE users;

The table is recreated, you have to rebuild the indexes (they are unusable), but grants, triggers etc. are still there.

If 8.1.6 , use alter table move .....