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 dataDELETE from Gjds_bus_oil_logWHERE 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;
--All indexes will be invalidated after compression, and the index needs to be rebuiltALTER INDEX index_name REBUILD;
--then analyze the table belowANALYZE table Gjds_bus_oil_log COMPUTE STATISTICS for TABLE for all INDEXES for all INDEXED COLUMNS;
--View the total number of blocksSELECT segment_name, extents, BLOCKSFrom user_segmentsWHERE segment_name = ' gjds_bus_oil_log ';--View high water levelSELECT BLOCKS, Empty_blocksFrom dba_tablesWHERE table_name = ' Gjds_bus_oil_log 'and OWNER = ' BUS ';--See how many blocks are actually usedSELECT COUNT (DISTINCT dbms_rowid. Rowid_block_number (ROWID)) Used_blockFrom Gjds_bus_oil_log S;
Another way is to use shrink SPACEShrink SPACE cannot be used in the following casesIOT 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 movementALTER TABLE gjds_bus_oil_log ENABLE ROW movement;--Compression SectionALTER 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
