Oracle表碎片整理方法(alter table move)操作总结_Oracle

一、表碎片的产生原因与影响

oracle 表在经历大量 insert、delete、update 操作后，数据块中会产生大量"空洞"。这些空洞是被删除数据腾出来的空间，但它们没有被有效回收，依然被表的**高水位线（high water mark, hwm）**标记为"已使用"。

碎片带来的危害：

存储浪费：已分配但无法有效利用的空间
性能下降：全表扫描需要读取更多数据块，i/o开销增大
索引效率降低：索引与数据块之间的映射关系变得松散
高水位线问题：即使表内数据很少，全表扫描仍会扫描到高水位线以下的所有块

碎片检测方法：

-- 收集表的统计信息
begin

  dbms_stats.gather_table_stats(ownname=>'用户名',
                                tabname=>'表名',
                                estimate_percent=>1,
                                method_opt=>'for all columns size 1',
                                no_invalidate=>false,
                                cascade=>true,
                                degree => 4); 
end; 
/

-- 对比表的实际数据大小与段占用大小
set linesize 200 pagesize 999
col owner format a15
col table_name format a20
col column_name format a12
select owner,
       table_name,
       num_rows,
       avg_row_len,
       blocks,
       round(blocks * 8 / 1024, 2) as "hwm_mb",
       round(num_rows * avg_row_len / 1024 / 1024, 2) as "real_data_mb",
       round((blocks * 8 - num_rows * avg_row_len / 1024) / 1024, 2) as "waste_mb"
from dba_tables
where owner = '用户名'
  and table_name = '表名';
若hwm_mb远大于 real_data_mb（如 500mb vs 20mb），则说明碎片严重，需要整理。
  
-- 评估数据库中大表碎片率
set linesize 200 pagesize 999
col owner format a15
col table_name format a20
col column_name format a12
select owner,
    table_name,
    num_rows,
    blocks,
    avg_row_len,
    round((1 - (num_rows * avg_row_len / (blocks * 8192))) * 100, 2) as fragment_pct
from dba_tables
where blocks > 100  -- 只关注大表
order by fragment_pct desc;

二、表碎片整理的四种方法

方法	核心原理	是否在线	索引影响	依赖对象和权限等	额外空间需求
create table as select (ctas)	将有效数据创建为新表，再重命名替换	否（需停业务）	需手动重建	需要手工处理，复杂	需要一倍于表的空间
alter table move	将表数据物理搬迁到新的段中，重建整个表	否（全程排他锁）	全部失效，需重建	不受影响	需要一倍于表的空间
dbms_redefinition（在线重定义）	通过物化视图日志同步数据，实现零停机重组	是（近乎零停机）	自动维护	通过自带过程一键完成	需要额外空间
alter table shrink space	原地整理数据块，将数据行向前压缩	是（大部分过程在线）	自动维护，保持有效	不受影响	几乎不需要额外空间

本文描述alter table move方法，其它三种方法请参考文章《oracle表在线重定义操作总结（dbms_redefinition）》《oracle ctas+rename 表重建方法操作总结》《oracle表碎片整理方法（alter table shrink）操作总结》

三、alter table move 操作

1、原理与特点

alter table ... move通过完全重新组织表的段（segment），将数据按顺序重新写入新的数据块，完成后，删除旧段，将新段重命名为原表，彻底消除碎片并重置高水位线。

由于数据被物理搬迁到全新的数据块中，每一行的 rowid 都会发生变化。这意味着所有基于 rowid 的索引全部失效（状态变为 unusable），必须手动重建。

move优点：

碎片清理最彻底，hwm 完全重置到最低位置
可以顺便调整存储参数（pctfree、pctused、表空间迁移等）
全表扫描性能恢复到最优水平
oracle 12c 及以上支持 move online（仅限堆表），可减少锁表时间
表上的依赖对象（触发器、包、函数、视图）和权限，不受影响，不需任何改动！

move缺点（代价）：

全程排他锁，操作期间表不可进行任何 dml 操作
需要额外一倍于表的空闲空间（新旧段同时存在）
所有索引全部失效，必须手动重建
操作期间产生大量 i/o 和 redo（除非使用 nologging）

2、move操作语法

-- 基本用法：原地 move（同表空间）
alter table your_table move;

-- 指定新表空间
alter table your_table move tablespace new_tablespace;

-- 同时调整 pctfree（可以顺便优化存储参数）
alter table your_table move pctfree 10 pctused 40;

-- 重建所有索引
select 'alter index ' || index_name || ' rebuild nologging parallel 4;' 
from dba_indexes 
where owner = '用户名' and table_name = '表名';

-- 执行生成的sql后，关闭并行
select 'alter index ' || index_name || ' logging noparallel;' 
from dba_indexes 
where owner = '用户名'  table_name = '表名';

3、并发（parallel）+ nologging

开启并行 + nologging（大表推荐，减少 redo 生成）

-- 使用并行度4进行move（利用多核cpu）、nologging（减少redo生成）
alter table your_table move nologging parallel 4;

--制定新表空间
alter table your_table move nologging parallel 4 tablespace new_tablespace;

-- 操作完成后应立即关闭并行
alter table your_table noparallel logging;

-- 重建所有索引
select 'alter index ' || index_name || ' rebuild nologging parallel 4;' 
from dba_indexes 
where owner = '用户名' and table_name = '表名' and index_type <>'lob';

-- 执行生成的sql后，关闭并行
select 'alter index ' || index_name || ' logging noparallel;' 
from dba_indexes 
where owner = '用户名'  table_name = '表名' and index_type <>'lob';

4、表中lob字段的move策略

包含lob字段的表，move时必须显式指定lob段的存储子句，否则lob段不会随表移动。

lob字段move基础语法

-- 错误示范：这样只搬了表段，lob 段完全不动
alter table your_table move;

-- 正确做法：必须带 lob 子句
alter table your_table move
  lob (lob_column) store as (tablespace target_tablespace);

-- 同时搬迁表段和 lob 段到不同表空间
alter table your_table move tablespace new_table_ts
  lob (lob_column) store as securefile (tablespace new_lob_ts);

-- 多个 lob 列的情况
alter table your_table move
  lob (clob_col1) store as (tablespace lob_ts)
  lob (blob_col2) store as (tablespace lob_ts);

-- 转换为 securefile 并开启压缩和去重（推荐）
alter table your_table move
  lob (lob_column) store as securefile (
    tablespace lob_ts
    compress high
    deduplicate
  );

lob字段move基础案例

-- 创建包含多种lob类型的测试表
create table doc_system (
    doc_id number primary key,
    doc_name varchar2(200),
    doc_content clob,
    doc_image blob
) tablespace users
  lob (doc_content) store as (tablespace users)
  lob (doc_image) store as (tablespace users);

-- 查看当前lob段位置
select segment_name, tablespace_name, segment_type
from user_segments
where segment_name in (
    select segment_name from user_lobs where table_name = 'doc_system'
);

-- 完整move操作（移动数据和所有lob段）
alter table doc_system move
    tablespace data_tbs
    lob (doc_content) store as (tablespace lob_tbs1)
    lob (doc_image) store as (tablespace lob_tbs2)
    parallel 4
    nologging;

-- 关闭并行
alter table doc_system logging noparallel;

-- 重建所有索引
select 'alter index ' || index_name || ' rebuild nologging parallel 4;' 
from user_indexes 
where table_name = 'doc_system' and index_type <>'lob';

-- 执行生成的sql后，关闭并行
select 'alter index ' || index_name || ' logging noparallel;' 
from user_indexes 
where table_name = 'doc_system' and index_type <>'lob';

5、分区表的move策略

对于分区表，可以逐个分区move，减少对整体业务的影响。

-- 查看分区信息
select partition_name, tablespace_name, num_rows
from user_tab_partitions
where table_name = 'big_part_table';

-- 逐个move分区
alter table big_part_table move partition p2024_q1
    tablespace data_tbs
    lob (xml_data) store as (tablespace lob_tbs)
    parallel 2;

-- 重建该分区的本地索引
alter index idx_part_local rebuild partition p2024_q1 parallel 2;

四、案例

环境准备： 创建测试表并模拟碎片

-- 创建测试表
create table test_move as select * from dba_objects;
-- 插入更多数据
insert into test_move select * from test_move;
commit;
insert into test_move select * from test_move;
commit;

-- 创建索引
create index idx_test_move on test_move(object_id);

-- 查看初始状态
select segment_name, blocks, bytes/1024/1024 as size_mb
from user_segments where segment_name = 'test_move';

-- 模拟大量删除（产生碎片）
delete from test_move where rownum <= 100000;
commit;

-- 收集统计信息
exec dbms_stats.gather_table_stats(user, 'test_move');

-- 查看碎片情况
select table_name, num_rows, blocks,
       round(blocks * 8 / 1024, 2) as "hwm_mb",
       round(num_rows * avg_row_len / 1024 / 1024, 2) as "real_data_mb"
from user_tables where table_name = 'test_move';


table_name                       num_rows     blocks     hwm_mb real_data_mb
------------------------------ ---------- ---------- ---------- ------------
test_move                          295036       5060      39.53        22.9

执行 move：

-- 第一步：执行 move（此操作会锁表，全程排他锁）
alter table test_move move;

-- 第二步：检查索引状态（必然失效）
select index_name, status from user_indexes where table_name = 'test_move';
index_name                     status
------------------------------ --------
idx_test_move                  unusable
-- 结果：idx_test_move  →  unusable

-- 第三步：重建索引
alter index idx_test_move rebuild nologging parallel 4;
alter index idx_test_move noparallel logging;  -- 恢复并行度

-- 第四步：恢复表的 logging 和并行度
alter table test_move logging noparallel;

-- 第五步：重新收集统计信息
exec dbms_stats.gather_table_stats(user, 'test_move');

-- 第六步：验证结果
select table_name, num_rows, blocks,
       round(blocks * 8 / 1024, 2) as "hwm_mb",
       round(num_rows * avg_row_len / 1024 / 1024, 2) as "real_data_mb"
from user_tables where table_name = 'test_move';

table_name                       num_rows     blocks     hwm_mb real_data_mb
------------------------------ ---------- ---------- ---------- ------------
test_move                          245036       3577      27.95         22.9

五、lob 字段的碎片处理补充

5.1 lob 存储的特殊性

lob（blob/clob）字段在 oracle 中有独立的存储结构，与普通表数据分开存放：

表段（heap segment）：存储普通列数据
lob 段（lob segment）：存储 lob 数据本身，段名格式为 sys_lobxxxxx$$
lob 索引段（lob index）：用于定位 lob 数据块，段名格式为 sys_ilxxxxx$$

关键认知：对表执行 alter table ... move 时，默认只处理表段本身，lob 段和 lob 索引段不会被自动处理。这是最常见的"踩坑点"——很多人做完 move 后发现 lob 表空间的空间完全没有释放。

5.2 判断 lob 类型：basicfile 还是 securefile

oracle 有两种 lob 存储类型，处理方式完全不同：

-- 查询 lob 列的存储类型
set linesize 200 pagesize 999
col table_name format a20
col column_name format a12
col segment_name format a25
col tablespace_name format a15
select table_name, column_name, segment_name, securefile, tablespace_name
from dba_lobs
where owner = 'your_schema'
  and table_name = 'your_table';

securefile = yes：securefile lob（oracle 11g 默认）
securefile = no：basicfile lob（旧式存储）

5.3 查询 lob 段占用空间：

select l.owner, l.table_name, l.column_name, l.segment_name,
       l.tablespace_name, l.securefile,
       round(s.bytes / 1024 / 1024, 2) as "lob_size_mb"
from dba_lobs l
join dba_segments s on l.owner = s.owner and l.segment_name = s.segment_name
where l.owner = 'your_schema'
order by s.bytes desc;

5.4 basicfile lob 的碎片处理

关键语法：必须显式指定 lob (column_name) store as 子句，否则 lob 段不会被处理！

-- 错误示范：这样只搬了表段，lob 段完全不动
alter table your_table move;

-- 正确做法：必须带 lob 子句
alter table your_table move
  lob (lob_column) store as (tablespace target_tablespace);

-- 同时搬迁表段和 lob 段到不同表空间
alter table your_table move tablespace new_table_ts
  lob (lob_column) store as securefile (tablespace new_lob_ts);

-- 多个 lob 列的情况
alter table your_table move
  lob (clob_col1) store as (tablespace lob_ts)
  lob (blob_col2) store as (tablespace lob_ts);

-- 转换为 securefile 并开启压缩和去重（推荐）
alter table your_table move
  lob (lob_column) store as securefile (
    tablespace lob_ts
    compress high
    deduplicate
  );

以上就是oracle表碎片整理方法(alter table move)操作总结的详细内容，更多关于oracle表碎片整理方法的资料请关注代码网其它相关文章！

Oracle表碎片整理方法(alter table move)操作总结

2026年09月22日 • Oracle •我要评论