Shrink mysql 命令_shrink space引发的悲剧。

The compaction phase of a segment shrink operation is done online. So, conventional DML operations as well as queries can coexist with a segment shrink operation.

During the compaction phase, locks are held on individual rows containing the data. This causes concurrent DML operations such as updates and deletes to serialize on these locks. However, only packets of rows are locked at one time to avoid the locking of the entire segment.

Similarly, conventional DML operations can block the progress of the compaction phase until they commit.

The COMPACT clause is useful if you have long-running queries that might span the shrink operation and attempt to read from blocks that have been reclaimed. When you specify the SHRINK SPACE COMPACT clause, the progress of the shrink operation is saved in the bitmap blocks of the corresponding segment. This means that the next time a shrink operation is executed on the same segment, the Oracle database remembers what has been done already. You can then reissue the SHRINK SPACE clause without the COMPACT clause during off-peak hours to complete the second phase.

During the second phase of the segment shrink operation, when the HWM is adjusted, the object is locked in exclusive mode. This phase is for a very short duration and does not affect the availability of the segment significantly. However, dependent cursors are invalidated.

所以还是分两个阶段做。先压缩，在降低HWM。

降低HWM可能引发大面积的堵塞，最好在没有什么负载的时候做。