Ways to reduce Oracle high watermark

1. Perform a table rebuild command ALTER TABLE table_name MOVE (verify that it is not a row, does not lower the watermark, but can release the tablespace)
When you create an object such as a table, whether or not you insert the data, it takes up some blocks, and Oracle assigns it the necessary space. Again, after releasing free space with ALTER TABLE move, some space is reserved for the table.

ALTER TABLE ... The move does not follow the parameters, not with the parameter table or in the original table space, move after remember to rebuild the index.
Query Invalid index statement: Select Index_name,table_name,tablespace_name,status from dba_indexes Where owner= ' hnunicom ' and status< > ' VALID ';
Rebuild INDEX statement: Alter INDEX INDEX_NAME rebuild tablespace tablespace_name;
If you want to continue to add data to this table later, there is no need to move, just freed up the space, only this table, other tables or segment can not use the space.

2. Execute ALTER TABLE table_name shrink space; (proven successful, recommended, can release database and disk space, large table can simultaneously reduce the table itself and table space of high watermark, small table can only reduce the table itself high watermark, for unknown reason)
Note that this command is new for Oracle 10g and must be allowed to move the ALTER TABLE table_name enable row movement before executing the instruction;


3. Copy the data to be persisted to the temporary table t,drop the original table, and then rename the temporary table T as the original table (not verified)

4. After exporting with exp, delete the original table/tablespace, then re-import it with IMP (verify success)

5. Alter Table table_name deallocate unused (verify not line, do not lower watermark)
Note: This proves that the deallocate unused to release the unused space above the HWM, but does not release the free space underneath the HWM, and does not move the HWM position.

6. Try to use TRUNCATE (verification is not possible, do not lower the watermark, you can free up the database space, but the TRUNCATE table after the default space size is the space before the deletion, such as to free up the computer disk space, you need to use Method 2 compression)

Ways to reduce Oracle high watermark