当表被执行过多的DELETE 回收碎片空间的几种方式

最新推荐文章于 2025-03-08 13:13:25 发布

原创最新推荐文章于 2025-03-08 13:13:25 发布 · 1k 阅读

1 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

91 篇文章

订阅专栏

本文介绍了在数据库中，当表经历大量DELETE操作导致空间碎片时，可以采取的六种有效方法来回收空间。包括使用Create Table As Select (CTAS)、在线重定义、数据泵导入导出、Alter Table Move、Alter Table Shrink Space以及通过Insert Into和Truncate的组合操作。这些方法旨在减少碎片，提高表的性能。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 创建新表替换原来的表

Create table As Select (CTAS) - This copies the table rows into a clean area, lowering the high-water-mark, packing the rows densely (as dictated by PCTFREE) and releasing free space.

create table test nologging as select * from source_table;

2. 在线重定义

Online reorg - Using the dbms_redefinition package you can use parallel CTAS to reorganize tables, while the tables continue to accept updates.

3. 导入导出

Data pump (expdp, impdp) - Rarely used in high-speed production apps, except for backups.

4. 使用 alter table move

Alter table move - The alter table xxx move command moves rows down into un-used space and adjusts the HWM but does not adjust the segments extents, and the table size remains the same. The alter table move syntax also preserves the index and constraint definitions.

5.使用alter table shrink

Alter table shrink space - Using the "alter table xxx shrink space compact" command will re-pack the rows, move down the HWM, and releases unused extents. With standard Oracle tables, you can reclaim space with the "alter table shrink space"

6.使用insert into 到中间表，再 truncat 原表，最后在将数据插入原表中

insert /*+ append nologging */ into target_test select * from source_table;

需要将index disable，重新 rebuild