ORACLE数据库把普通表转换成分区表的操作教程

oracle普通表转换成分区表的操作

前言

oracle官方建议当表的大小大于2gb的时候就使用分区表进行管理，分区表相对于小的表管理和性能上都有很大的优势，本文档暂时不介绍具体的优势，主要介绍几种普通表转换成分区表的方法；

方法概述

oracle官方给了以下四种操作的方法：

export/import method（导入导出）

insert with a subquery method（插入子查询的方法）

partition exchange method（交换分区法）

dbms_redefinition（在线重定义）

这些方法的思路都是创建一个新的分区表，然后把旧表的数据转移到新表上面，接着转移相应的依赖关系，最后进行表的重命名，把新表和旧表rename。

其中a、b、c这三种方法都会影响到的正常使用，本文档不做详细的介绍，本文档主要介绍d方法，这种方法是目前普遍在进行普通表转换成分区表的方法。

在线重定义进行分区表的操作

整个操作的思路如下，以scott下的emp表为例

1.先确认下表能不能进行分区

基于主键的确认

begin
dbms_redefinition.can_redef_table('soctt','emp',dbms_redefinition.cons_use_pk);
end;
/
pl/sql procedure successfully completed.

显示的是没有问题的

2.进行临时表的创建，以deptno作为分区的选项

create table scott.emp_1
(
  empnonumber(4),
  enamevarchar2(10 byte),
  jobvarchar2(9 byte),
  mgrnumber(4),
  hiredatedate,
  salnumber(7,2),
  commnumber(7,2),
  deptnonumber(2)
)
partition by range (deptno)
(
  partition emp_a1 values less than (20),
  partition emp_a2 values less than (30),
  partition emp_a3 values less than (40),
  partition emp_a4 values less than (50),
  partition emp_a5 values less than (60)
      )

3.开始执行数据的迁移

exec dbms_redefinition.start_redef_table('scott', 'emp', 'emp_1');

4.如果表的数据很多，3步的时候可能会很长，这期间系统可能会继续对表emp进行写入或者更新数据，那么可以执行以下的语句，这样在执行最后一步的时候可以避免长时间的锁定(该过程可选可不选)

begin 
  dbms_redefinition.sync_interim_table('scott', 'emp', 'emp_1');
end;
/

5.进行权限对象的迁移

declare
num_errors pls_integer;
begin
dbms_redefinition.copy_table_dependents('scott', 'emp','emp_1',
dbms_redefinition.cons_orig_params, true, true, true, true, num_errors);
end;
/

6.查询相关错误，在操作之前先检查，查询dba_redefinition_errors试图查询错误：

select object_name, base_table_name, ddl_txt from   dba_redefinition_errors;

7.结束整个重定义

begin
dbms_redefinition.finish_redef_table('scott', 'emp', 'emp_1');
end;
/

总结

做过一个大小2.3gb，总行数360万行的表，整个过程大概花了56秒的时间，整个过程还是相当快的。建议具体的生产环境的执行需要经过严格测试后执行，测试的过程中大概就能知道整个过程的执行时间长度。

另如果再执行的过程中发生错误，可以通过以下语句结束整个过程：

begin
dbms_redefinition.abort_redef_table(uname => 'scott',
orig_table => 'emp',
int_table => 'emp_1'
);
end;