Oracle中将非分区表转换为分区表的最佳实践_Oracle

虽然从 oracle 12.2 开始，技术上已经可以直接通过以下方式转换非分区表为分区表：alter table … modify但一些较为复杂分区场景，还是建议采用在线重定义操作来更改为分区表。

什么是表重定义（table redefinition）？

在线表重定义（online table redefinition） 允许在表正在被使用的情况下对其进行结构变更，且零停机时间。

它是如何工作的？

oracle 通过创建临时表和同步机制来实现在线重定义：

你创建一个具有目标结构的临时表（interim table）
oracle 将数据从原表复制到临时表
在此期间产生的任何变更都会被同步
最后，oracle 以原子方式将原表与临时表进行交换

测试案例

1. 创建 sequence（用于生成 order_id）

create sequence szr.customer_orders_seq
  start with 1
  increment by 1
  nocache
  nocycle;

2. 创建测试用的非分区表 customer_orders（11g 兼容）

create table szr.customer_orders (
    order_id        number        primary key, 
    order_date      date          not null,
    customer_id     number        not null,
    customer_name   varchar2(120),
    product_code    varchar2(50),
    quantity        number(8),
    unit_price      number(12,2),
    total_amount    number(14,2)  generated always as (quantity * unit_price) virtual,
    order_status    varchar2(20)  default 'pending',
    created_by      varchar2(60)
) tablespace users;

-- 创建 trigger 实现自增主键
create or replace trigger szr.customer_orders_trg
before insert on szr.customer_orders
for each row
begin
    if :new.order_id is null then
        select szr.customer_orders_seq.nextval
          into :new.order_id
          from dual;
    end if;
end;
/

3. 插入一些测试数据（覆盖不同年份）

insert into szr.customer_orders (order_date, customer_id, customer_name, product_code, quantity, unit_price, order_status, created_by)
select 
    add_months(to_date('2020-03-10','yyyy-mm-dd'), level*1) as order_date,
    5000 + mod(level, 300) as customer_id,
    'customer_' || to_char(5000 + mod(level, 300)) as customer_name,
    'prod_' || lpad(mod(level, 99)+1, 3, '0') as product_code,
    mod(level, 50) + 1 as quantity,
    round(dbms_random.value(10, 500), 2) as unit_price,
    case mod(level, 5) 
        when 0 then 'completed' 
        when 1 then 'shipped' 
        when 2 then 'pending' 
        else 'cancelled' 
    end as order_status,
    'user_' || mod(level, 10) as created_by
from dual
connect by level <= 1200;

commit;

-- 查看原表记录数和数据分布
select count(*) as total_rows from szr.customer_orders;

select min(order_date), max(order_date) from szr.customer_orders;

select trunc(order_date,'mm'), count(*) from szr.customer_orders group by trunc(order_date,'mm') order by 1;

4. 检查表是否支持重定义（使用 cons_use_pk）

set serveroutput on;
begin
  dbms_redefinition.can_redef_table(
    uname        => 'szr',
    tname        => 'customer_orders',
    options_flag => dbms_redefinition.cons_use_pk
  );
  dbms_output.put_line('表支持重定义（cons_use_pk）：成功');
end;
/

5. 创建具有目标分区结构的临时表（interim table）

create table szr.customer_orders_int
tablespace users
partition by range (order_date)
interval (numtoyminterval(1, 'month'))    
(
  partition p_before_2022 values less than (to_date('2022-01-01','yyyy-mm-dd'))
)
as 
select * from szr.customer_orders 
where 1=0;

-- 查看临时表当前分区
select partition_name, high_value 
from user_tab_partitions 
where table_name = 'customer_orders_int';

6. 启动重定义过程（使用 cons_use_pk）

set serveroutput on;
begin
  dbms_redefinition.start_redef_table(
    uname        => 'szr',
    orig_table   => 'customer_orders',
    int_table    => 'customer_orders_int',
    options_flag => dbms_redefinition.cons_use_pk
  );
  dbms_output.put_line('start_redef_table 执行完成');
end;
/

大表场景：可在 start_redef_table 前开启并行（如 alter session force parallel dml parallel 4;）。

回退操作：如果执行过程中发生错误，可以使用以下语句回退

begin
  dbms_redefinition.abort_redef_table(
    uname      => 'szr',
    orig_table => 'customer_orders',
    int_table  => 'customer_orders_int'
  );
end;
/

7. 复制原表的依赖对象

set serveroutput on;
declare
  l_num_errors  pls_integer;
begin
  dbms_redefinition.copy_table_dependents(
    uname            => 'szr',
    orig_table       => 'customer_orders',
    int_table        => 'customer_orders_int',
    copy_indexes     => 0,                    -- 不自动复制索引（主键索引已存在）
    copy_triggers    => true,
    copy_constraints => false,                -- 关键：关闭约束复制，避免 not null 冲突
    copy_privileges  => true,
    ignore_errors    => true,
    num_errors       => l_num_errors,
    copy_statistics  => false
  );
  dbms_output.put_line('copy_table_dependents 执行完成，错误数: ' || l_num_errors);
end;
/

8. 同步期间产生的新 dml 数据（推荐执行）

begin
  dbms_redefinition.sync_interim_table(
    uname      => 'szr',
    orig_table => 'customer_orders',
    int_table  => 'customer_orders_int'
  );
  dbms_output.put_line('sync_interim_table 执行完成');
end;
/

9. 完成重定义

begin
  dbms_redefinition.finish_redef_table(
    uname      => 'szr',
    orig_table => 'customer_orders',
    int_table  => 'customer_orders_int'
  );
  dbms_output.put_line('finish_redef_table 执行完成，表已成功转换为分区表');
end;
/

10. 验证结果

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

select partition_name, high_value, num_rows 
from user_tab_partitions 
where table_name = 'customer_orders' 
order by partition_position;

select count(*) from szr.customer_orders;

-- 删除临时表
drop table szr.customer_orders_int purge;

-- 收集统计信息
begin
  dbms_stats.gather_table_stats('szr', 'customer_orders', cascade => true);
end;
/