High watermark for Oracle Clear table

When you add a record, HWM slowly moves up, but when you delete a record, HWM does not move down. Therefore, deleting large amounts of data with the delete from command can cause performance problems caused by HWM.

The most straightforward way to delete a high water level is to truncate Table but this way is too violent, so use the following method to remove the high watermark.

--Delete data

DELETE from Gjds_bus_oil_log

WHERE VERSION < (13112-100);

--Fast Compression

Move is best done when you are free to remember move is a TM lock will be generated after the move to remember to rebuild index

ALTER TABLE Gjds_bus_oil_log MOVE;

--After compression, all indexes will be invalidated and the index needs to be rebuilt.

ALTER INDEX index_name REBUILD;

--then analyze the table below

ANALYZE table Gjds_bus_oil_log COMPUTE STATISTICS for TABLE for all INDEXES for all INDEXED COLUMNS;

--View the total number of blocks

SELECT segment_name, extents, BLOCKS

From User_segments

WHERE segment_name = ' gjds_bus_oil_log ';

--View high water level

SELECT BLOCKS, Empty_blocks

From Dba_tables

WHERE table_name = ' Gjds_bus_oil_log '

and OWNER = ' BUS ';

--See how many blocks are actually used

SELECT COUNT (DISTINCT dbms_rowid. Rowid_block_number (ROWID)) Used_block

From Gjds_bus_oil_log S;

Another way is to use shrink SPACE

Shrink SPACE cannot be used in the following cases

IOT Mapping Tables
Tables with ROWID based materialized views
Tables with function-based indexes     --Function Index
securefile lobs
Compressed Tables

If a function index is required to delete the function index, you can use the

--Enable row movement

ALTER TABLE gjds_bus_oil_log ENABLE ROW movement;

--Compression section

ALTER TABLE gjds_bus_oil_log SHRINK SPACE;

Also need to rebuild indexes and re-parse tables

Reference: http://www.blogjava.net/decode360/archive/2009/07/14/287767.html

High watermark for Oracle Clear table