High Water Mark

[Sameer]

Hi All,

I want to reduce HWM of a table after deleting aprrox. 4000000 rows from table.

Is rebuilding only option?

After analyzing table, does the HWM goes to appropriate value?

Please advice.

Thanks

Sameer

[TimHall]

Your options are:

1) Export table, drop table, create table, import data.
2) ALTER TABLE ... MOVE
3) Truncate the table and repopulate:
CREATE temp_table AS select * from my_table;
TRUNCATE TABLE my_table;
INSERT INTO my_table SELECT * FROM temp_table;
COMMIT;

You can deallocate unused space above the high water mark using:

ALTER TABLE my_table DEALLOCATE UNUSED;

See:

http://otn.oracle.com/docs/products/...chema.htm#6870

Cheers