Oracle普通表自动改造分区表的完整实战指南

引言

在生产系统中，随着业务数据的持续累积，单表数据量不断膨胀，逐渐出现查询性能下降、维护难度增加、统计信息采集缓慢等问题。一张普通堆表在数据量达到数千万甚至上亿行之后，全表扫描的代价变得难以承受，历史数据的归档清理需要逐行删除，产生大量undo和redo，严重影响系统可用性。

分区表是oracle vldb超大型数据库的核心能力。通过物理上将一张逻辑大表拆分为多个独立分区段，实现分区裁剪、分区级快速维护、并行dml与查询，极大优化海量数据场景查询性能与运维效率。然而，在生产环境中直接将普通表改为分区表，传统方式通常意味着停机调整，这在核心业务系统上基本不可接受。

oracle提供的dbms_redefinition包支持在线重定义功能，可以在业务连续运行的前提下完成结构转换。配合interval自动分区和自动化维护脚本，可以实现从普通表到分区表的自动改造和持续维护。本文将系统讲解这一完整技术路径。

第一章 改造前的全面评估

1.1 为什么要改造成分区表

oracle官方建议当表的大小大于2gb的时候使用分区表进行管理。分区表带来的核心收益包括四个方面。

分区裁剪方面，当sql的where条件携带分区键时，优化器自动过滤不需要访问的分区，只扫描少量分区，大幅降低io开销。一个查询只需要扫描最近一个月的数据，就不必读取过去三年的全部数据。

分区级运维方面，可以直接对分区执行drop、truncate、exchange等操作，海量历史数据归档清理不再需要delete全表，元数据操作秒级完成，不会产生大量undo和redo。

并行处理方面，查询、dml、备份恢复可以以分区为粒度做并行，充分利用多核处理器的算力。一个涉及全表的聚合查询可以按分区拆分，分配到多个并行执行服务器上同时处理。

存储隔离方面，不同分区可以放置在不同表空间，冷热数据分开存储，历史冷数据可以放到低成本存储介质。

但分区表不是万能优化手段。如果业务sql的where条件不带分区键，会发生全分区扫描，性能反而可能不如普通表。因此改造前的评估至关重要。

1.2 表大小与空间需求评估

改造前第一步是评估原表的实际大小。需要查询表段大小以及lob段和索引段的大小。

查询表段大小的sql如下：

select owner, segment_name, segment_type,
       round(sum(bytes) / 1024 / 1024 / 1024, 2) gb
from dba_segments
where owner = 'app_user'
  and segment_name = 'biz_log'
group by owner, segment_name, segment_type;

如果表中包含clob或blob等大字段，还需要查询lob段和lob索引段的大小：

select s.owner, s.segment_name, s.segment_type,
       round(s.bytes / 1024 / 1024 / 1024, 2) gb
from dba_lobs l
join dba_segments s
  on s.owner = l.owner
 and s.segment_name in (l.segment_name, l.index_name)
where l.owner = 'app_user'
  and l.table_name = 'biz_log'
order by s.segment_type, s.segment_name;

在线重定义需要双倍空间，包括原表和索引算一份，中间表和索引算另一份。此外还需要考虑undo、redo和归档空间。凡是做在线重定义，不能只看原表大小，还要把中间表、索引、lob、undo、redo和归档空间一起算进去。

1.3 主键与唯一约束检查

dbms_redefinition支持两种重定义方式：按主键方式和按rowid方式。生产环境中，如果原表具备主键条件，优先选择主键方式，也就是dbms_redefinition.cons_use_pk。

检查原表是否有主键或唯一约束：

select owner, table_name, constraint_name, constraint_type, status
from dba_constraints
where owner = 'app_user'
  and table_name = 'biz_log'
  and constraint_type in ('p', 'u');

如果没有主键和唯一约束，可以使用rowid方式。rowid方式从10g开始支持，但不能用于索引组织表，而且重定义完成后会存在隐藏列m_row$$。

1.4 分区键的选择

分区键的选择是改造成功的关键。分区键应当满足以下条件：是查询中高频使用的过滤条件字段；数据分布均匀，不会出现严重的数据倾斜；支持范围分区，通常是日期或时间戳字段。

本次示例中，原表biz_log包含log_time字段，数据主要分布在2022年到2026年，适合按log_time做range分区。

1.5 表空间规划

分区表通常需要为不同分区规划不同的表空间。热数据分区放在高性能存储表空间，冷数据分区放在低成本存储表空间。如果采用interval自动分区，还需要通过alter table ... modify default attributes tablespace动态调整新分区的默认表空间。

第二章 在线重定义的核心机制

2.1 底层原理

dbms_redefinition的核心依赖是物化视图日志。对源表创建mv log后，重定义期间源表上的dml变化会被持续记录；中间表通过mv log增量同步源表数据；同步完成后，oracle在数据字典层面交换两张表的名字，瞬间完成，业务无感知。

换句话说，真正的大活都提前通过增量同步干完了，最后只剩一次字典级的名字交换。这使得最终切换的锁表时间极短，通常在1秒以内。

2.2 与传统改造方案的对比

传统的普通表转分区表方案包括create table as select加insert方式，这种方式会锁原表，业务必须停机，适合离线测试库。exchange partition方式适合原表数据就是一个分区的范围的场景。

在线重定义的优点是对业务影响非常小，锁表时间非常短，速度较快，在实践中15g的表仅用了12分钟。缺点是需要使用与原表同样大小的存储空间，包括索引和lob字段，需要占用一定的系统资源。

2.3 支持的主要场景

在线重定义支持普通表、分区表、索引组织表之间的相互转换，将表迁移至其他表空间，修改表的存储属性，以及重建表以减少碎片。这些场景覆盖了绝大多数表结构改造需求。

第三章 在线重定义的完整实施步骤

3.1 第一步：前提检查

使用can_redef_table过程检查表是否可以进行在线重定义：

exec dbms_redefinition.can_redef_table('app_user', 'biz_log');

如果需要指定按主键方式：

exec dbms_redefinition.can_redef_table(
    'app_user', 'biz_log', dbms_redefinition.cons_use_pk);

如果返回错误，需要根据错误信息排查原因。常见问题包括表没有主键且未指定rowid方式、表包含不支持的数据类型等。

3.2 第二步：创建中间分区表

创建中间表，结构与目标结构一致。中间表应包含分区定义：

create table app_user.biz_log_tmp (
    log_id number(20) not null,
    log_time date not null,
    user_id number(10),
    operation varchar2(200),
    detail clob,
    ip_address varchar2(50)
)
partition by range (log_time)
interval (numtoyminterval(1, 'month'))
(
    partition p202201 values less than (to_date('2022-02-01', 'yyyy-mm-dd')),
    partition p202202 values less than (to_date('2022-03-01', 'yyyy-mm-dd')),
    ...
    partition p202601 values less than (to_date('2026-02-01', 'yyyy-mm-dd'))
);

创建中间表时不要创建索引。索引应当在数据同步完成后通过copy_table_dependents创建，否则每次增量同步都需要维护索引，严重影响性能。

中间表创建完成后，为其添加主键约束，确保可以按主键方式重定义：

alter table app_user.biz_log_tmp
add constraint biz_log_tmp_pk primary key (log_id) using index;

3.3 第三步：启动重定义

启动在线重定义，执行全量数据同步：

exec dbms_redefinition.start_redef_table(
    'app_user', 'biz_log', 'biz_log_tmp');

如果原表没有主键，需要指定rowid方式：

exec dbms_redefinition.start_redef_table(
    'app_user', 'biz_log', 'biz_log_tmp',
    null, dbms_redefinition.cons_use_rowid);

对于大型表，可以通过启用并行来提高性能：

alter session force parallel dml parallel 8;
alter session force parallel query parallel 8;

start_redef_table执行期间会进行全量数据同步，将原表所有数据复制到中间表。这个过程耗时与表大小成正比，但对于业务没有影响，dml操作可以继续在原表上执行。

3.4 第四步：同步依赖对象

全量同步完成后，使用copy_table_dependents同步依赖对象，包括索引、约束、触发器和权限等：

declare
    num_errors pls_integer;
begin
    dbms_redefinition.copy_table_dependents(
        uname            => 'app_user',
        orig_table       => 'biz_log',
        int_table        => 'biz_log_tmp',
        copy_indexes     => dbms_redefinition.cons_orig_params,
        copy_triggers    => true,
        copy_constraints => true,
        copy_privileges  => true,
        ignore_errors    => true,
        num_errors       => num_errors
    );
end;
/

这一步提前做，可以防止重定义完成后新表没有可用索引而产生性能问题。

3.5 第五步：增量同步

在全量同步完成后和最终切换之前，可以多次执行增量同步，将源表上发生的dml变更同步到中间表，缩短最终交换时的锁定窗口：

exec dbms_redefinition.sync_interim_table(
    'app_user', 'biz_log', 'biz_log_tmp');

每次增量同步都只同步上一次同步之后发生的变化，数据量越来越小。建议在业务低峰期多做几次增量同步，把最后一次同步的数据量压到最小。

3.6 第六步：完成重定义

执行finish_redef_table，oracle会在数据字典层面交换两张表的名字：

exec dbms_redefinition.finish_redef_table(
    'app_user', 'biz_log', 'biz_log_tmp');

这一步会短暂锁定原表，锁定时间取决于最后一次增量同步的数据量，通常在秒级以内。完成后，原表名biz_log指向的是新的分区表结构，中间表名biz_log_tmp指向的是旧的非分区表。

3.7 第七步：清理与验证

完成重定义后，删除中间表释放空间：

drop table app_user.biz_log_tmp;

然后收集统计信息，检查索引名和并行度设置：

exec dbms_stats.gather_table_stats('app_user', 'biz_log', cascade => true);

验证表的分区结构是否生效：

select table_name, partitioning_type, interval
from user_part_tables
where table_name = 'biz_log';

select partition_name, high_value, tablespace_name
from user_tab_partitions
where table_name = 'biz_log'
order by partition_position;

第四章 异常处理与回滚

4.1 可随时中止

在线重定义的一个关键优势是支持随时回滚。任何一步出问题都可以使用abort_redef_table中止，原表不受影响：

exec dbms_redefinition.abort_redef_table(
    'app_user', 'biz_log', 'biz_log_tmp');

中止后，中间表仍然存在，可以手动清理。原表保持完整，业务不受任何影响。

4.2 常见错误处理

在执行在线重定义过程中可能遇到ora-12008、ora-12034等物化视图日志相关错误。这类错误通常是因为源表上有不兼容的数据类型或约束。需要检查源表的约束条件和触发器，确保满足在线重定义的前提条件。

如果增量同步速度过慢，可能是因为索引在同步过程中被维护。解决方法是在start_redef_table之前不创建任何索引，等全量同步完成后通过copy_table_dependents创建索引。

第五章 interval自动分区与自动化维护

5.1 interval分区的原理

oracle 11g引入了interval分区特性。在范围分区表中，不需要定义maxvalue分区，oracle会根据分区定义的步长动态分配新分区来容纳超过范围的数据。

创建按月自动分区的interval分区表示例：

create table biz_log (
    log_id number(20) not null,
    log_time date not null,
    operation varchar2(200),
    detail clob
)
partition by range (log_time)
interval (numtoyminterval(1, 'month'))
(
    partition p202601 values less than (to_date('2026-02-01', 'yyyy-mm-dd'))
);

当插入一条log_time为2026年3月的数据时，oracle会自动创建一个新的分区来容纳这条数据。分区名由系统自动生成，格式为sys_p开头加上数字编号。

5.2 自动分区的表空间管理

interval分区的一个挑战是分区自动创建时的表空间分配。默认情况下，新分区会使用表的默认表空间。如果需要将不同时间段的分区放在不同表空间，可以通过动态修改默认表空间实现：

-- 先将表的默认表空间设置为2027年的表空间
alter table biz_log modify default attributes tablespace ts_2027;

-- 插入一条2027年的数据，触发自动分区创建
insert into biz_log values (1, date '2027-06-01', 'test', null);
rollback;

-- 再将默认表空间切换到2028年
alter table biz_log modify default attributes tablespace ts_2028;

这种方法需要维护任务配合，从某种程度上削弱了interval分区的自动化优势。另一种方案是使用dbms_scheduler定时任务，提前创建未来需要的分区并指定表空间。

5.3 自动删除过期分区

interval分区自动创建新分区，但不会自动删除旧分区。需要编写存储过程配合dbms_scheduler定时任务来清理过期分区。

以下是一个自动删除过期分区的存储过程示例：

create or replace procedure manage_partitions(
    p_table_name in varchar2,
    p_retention_months in number default 12
) is
    v_sql varchar2(500);
    v_partition_name varchar2(100);
    v_high_value varchar2(500);
    v_partition_date date;
    cursor c_partitions is
        select partition_name, high_value
        from user_tab_partitions
        where table_name = upper(p_table_name)
          and partition_name like 'sys_p%'
        order by partition_position;
begin
    for rec in c_partitions loop
        begin
            v_high_value := rec.high_value;
            execute immediate 'select ' || v_high_value || ' from dual'
                into v_partition_date;
        exception
            when others then
                continue;
        end;

        if v_partition_date < add_months(sysdate, -p_retention_months) then
            v_sql := 'alter table ' || p_table_name ||
                     ' drop partition ' || rec.partition_name ||
                     ' update global indexes';
            execute immediate v_sql;
        end if;
    end loop;
end;
/

然后通过dbms_scheduler创建定时任务，每天执行一次：

begin
    dbms_scheduler.create_job(
        job_name        => 'job_manage_partitions',
        job_type        => 'plsql_block',
        job_action      => 'begin manage_partitions(''biz_log'', 12); end;',
        start_date      => systimestamp,
        repeat_interval => 'freq=daily; byhour=2; byminute=0',
        enabled         => true
    );
end;
/

注意drop partition时需要加上update global indexes，否则全局索引会变为unusable状态。

5.4 自动化维护的完整框架

对于需要管理多个分区表的场景，可以建立一个集中式的分区维护框架。首先创建一张元数据表，记录需要维护的表及其保留策略：

create table part_maintenance (
    table_owner   varchar2(30),
    table_name    varchar2(30),
    retention_months number default 12,
    enabled       varchar2(1) default 'y',
    created_at    date default sysdate
);

insert into part_maintenance values ('app_user', 'biz_log', 12, 'y', sysdate);
insert into part_maintenance values ('app_user', 'order_log', 24, 'y', sysdate);
commit;

然后编写一个通用的维护存储过程，遍历元数据表中的所有表，逐一执行分区清理。这种方式将分区维护的配置和逻辑分离，新表加入维护范围只需在元数据表中插入一条记录即可。

第六章 性能调优与最佳实践

6.1 并行度配置

对于大型表的在线重定义，并行处理可以将完成时间缩短60%以上。在start_redef_table和sync_interim_table之前设置并行度：

alter session force parallel dml parallel 8;
alter session force parallel query parallel 8;

并行度应当根据服务器的cpu核心数和io能力合理设置，不宜过高以免造成资源争抢。

6.2 索引策略

分区表的索引分为本地索引和全局索引。本地索引的分区与表分区一一对应，当表分区被drop或truncate时，对应的索引分区自动维护。全局索引不分区，当表分区被drop时，全局索引会变为unusable，需要在ddl语句中加上update global indexes。

对于按时间分区的时间序列表，通常使用本地索引。对于需要跨分区唯一性约束的场景，使用全局索引。不建议使用全局分区索引，因为oracle不会自动维护全局分区索引。

6.3 统计信息管理

分区表的统计信息收集策略需要特别关注。全表统计信息收集 会扫描所有分区，在大表上耗时很长。建议使用增量统计信息收集：

exec dbms_stats.set_table_prefs('app_user', 'biz_log', 
    'incremental', 'true');
exec dbms_stats.set_table_prefs('app_user', 'biz_log',
    'incremental_level', 'partition');

这样每次只需要收集变化分区的统计信息，全局统计信息由oracle自动合并。

6.4 监控与告警

在线重定义期间应监控undo表空间使用率，避免事务回滚段耗尽导致操作失败。同时监控中间表的增长情况，确保有足够的空间完成全量同步。

重定义完成后，监控分区表的查询性能变化。通过awr报告对比改造前后的执行计划和io消耗，验证分区裁剪是否生效。

第七章 生产环境实战案例

7.1 案例背景

某业务系统的日志表biz_log数据量接近4000万行，表段约34gb，包含clob字段。数据主要分布在2022年到2026年，随着数据持续增长，查询和归档维护越来越困难。

改造目标是将biz_log在线改造成按log_time做range分区的分区表，业务希望尽量不停机，只接受最终切换阶段的短暂锁表。

7.2 实施过程

前期检查阶段，确认表没有主键和唯一约束，因此采用rowid方式重定义。空间评估发现表段34gb，lob段约12gb，索引段约8gb，总计需要准备约108gb的额外空间用于中间表。

中间表创建阶段，按照log_time按月分区，预先创建了2022年到2026年的分区。不创建任何索引，等待数据同步完成后统一创建。

启动重定义阶段，设置并行度为8，全量同步耗时约45分钟。增量同步执行了3次，每次耗时约2到5分钟，数据量逐次递减。

完成重定义阶段，最终finish_redef_table的锁表时间约1.5秒，业务基本无感知。

7.3 改造效果

改造完成后，查询最近一个月日志的响应时间从原来的12秒降至0.8秒，性能提升约15倍。历史数据归档从原来的delete操作改为drop partition，耗时从数小时降至秒级。

第八章 选型决策与注意事项

8.1 五大方案对比

普通表转分区表的主流方案包括ctas加insert、exchange partition、数据泵导出导入、dbms_redefinition在线重定义，以及oracle 19c的alter table modify partition by online。

ctas方式简单但会锁原表，业务必须停机，适合离线测试库。exchange partition适合原表数据就是一个分区的范围的场景。数据泵方式适合跨数据库迁移。dbms_redefinition是24乘7不停机业务的首选。19c的在线modify partition by是更简洁的方案，但功能覆盖范围有限。

8.2 空间规划要点

在线重定义需要双倍空间。除了表和索引本身，还要考虑lob段、undo、redo和归档空间。执行到一半空间爆了，处理起来会非常被动。

8.3 业务兼容性

分区表改造后，应用sql不需要修改即可正常执行，因为分区表在逻辑上仍然是一张完整的表。但需要注意sql的where条件尽量携带分区键，否则会发生全分区扫描。

8.4 回滚预案

在finish_redef_table之前，原表始终完整可用。任何步骤失败都可以abort_redef_table中止，不影响业务。完成finish后，如果发现问题，需要手动将中间表名改回原表名来恢复，操作较为复杂，因此finish之前务必在测试环境完整验证。

结语

oracle普通表自动改造分区表，核心在于利用dbms_redefinition的在线重定义能力，将耗时的大活通过增量同步提前完成，最终只需一次字典级的名字交换。配合interval自动分区和dbms_scheduler定时任务，可以实现从改造到持续维护的完整自动化。

实施这一方案的关键要点包括：充分的改造前评估，特别是空间和主键检查；中间表不创建索引以加速数据同步；多次增量同步将最终锁定窗口压到最小；以及配套的自动分区创建和过期分区清理任务。当分区裁剪生效、历史数据归档从小时级降至秒级时，改造的价值就得到了最直接的验证。

以上就是oracle普通表自动改造分区表的完整实战指南的详细内容，更多关于oracle普通表自动改造分区表的资料请关注代码网其它相关文章！