oracle 在线压缩分区表,ORACLE 普通表转换成分区表的操作

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

【方法概述】oracle 官方给了以下四种操作的方法：

A)  Export/import method(导入导出)

B)  Insert with a subquery method(插入子查询的方法)

C)  Partition exchange method(交换分区法)

D)  DBMS_REDEFINITION(在线重定义)

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

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

【在线重定义进行分区表的操作】整个操作的思路如下，以 SCOTT 下的 EMP 表为例

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

基于主键的确认

DBMS_REDEFINITION.CAN_REDEF_TABLE('SOCTT','EMP',DBMS_REDEFINITION.CONS_USE_PK);

END;

/

PL/SQL procedure successfully completed.

显示的是没有问题的

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

(

EMPNONUMBER(4),

ENAMEVARCHAR2(10BYTE),

JOBVARCHAR2(9BYTE),

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. 对表 EMP 进行写入或者更新数据

如果表的数据很多，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 试图查询错误：

selectobject_name,base_table_name,ddl_txtfromDBA_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;