oracle表转为分区表,Oracle数据库在线非分区表转成分区表

在许多业务系统中，由于开始么有做很好的规划，随着时间迁移性能下降，这时需要对表数据进行分区，但是有影响业务连续性。本文介绍两种在线分区的方法。

一、在线重定义

前置条件：

1、 表必须有主键，在从定义过程主键不能被修改；

2、 表中不包含long、bfile字段；

3、 表的owner不能是sys或system

4、 需要两边的空间

具体过程：

1、建立测试表(原表)

SQL>conn hr/hr

SQL>create table unpar_table (

id number(10) primary key,

create_date date

);

SQL>insert into unpar_table select rownum, created from dba_objects;

SQL>Commit;

SQL > create index create_date_ind on unpar_table(create_date);

索引已创建。

2、建立l临时表(分区表)

create table par_table (id number primary key,create_date date) partition by range (create_date)

(partition p1 values less than (to_date('2013-12-31', 'yyyy-mm-dd')),

partition p2 values less than (to_date('2014-12-31', 'yyyy-mm-dd')),

partition p3 values less than (to_date('2015-12-31', 'yyyy-mm-dd')));

3、检查重定义的合理性

exec dbms_redefinition.can_redef_table('hr', 'unpar_table');

4、在线重定义

BEGIN

DBMS_REDEFINITION.START_REDEF_TABLE(

UNAME=>'HR',

ORIG_TABLE=>'par_table',

INT_TABLE=>'par_table2');

END;

/

这个过程可能时间很长，但是可以做DML操作，不会影响业务

5、 建立索引，在线重定义只重定义数据，索引还需要单独建立

SQL > create index create_date_ind2 on par_table(create_date);，

6、结束重定义

BEGIN

dbms_redefinition.finish_redef_table(

uname => 'HR',

orig_table => 'unpar_table',

int_table => 'par_table');

END;

/

可以发现unpar_table 已经变成分区表，并且数据按照create_date 分布在不同的分区

7、 测试

SQL>select count(*) from unpar_table partition (p1);

8、索引重命名

SQL> ALTER INDEX create_date_ind2 RENAME TO create_date_ind;

9、删除临时表,定义完成

SQL> Drop table par_table purge;

二、 分区交换

1、建立测试表(原表)

SQL>conn hr/hr

SQL>create table unpar_table (

id number(10) primary key,

create_date date

);

SQL>insert into unpar_table select rownum, created from dba_objects;

SQL>Commit;

SQL > create index create_date_ind on unpar_table(create_date);

2、建立分区表，这个表只有一个分区，后续再拆分

SQL>create table par_table (id number(10) primary key,create_date date) partition by range (create_date)

(partition p3 values less than (to_date('2015-12-31', 'yyyy-mm-dd')));

3、分区交换

SQL>alter table par_table exchange partition p3 with table unpar_table;

4、校验数据

SQL> select count(*) from par_table partition(p3);

COUNT(*)

----------

86837

5、分区拆分，根据实际数据拆分成若干分区

SQL>alter table par_table split partition p3 at(to_date('2014-12-31','yyyy-mm-dd')) into (partition p2014, partition p3);

SQL>alter table par_table split partition p2014 at(to_date('2013-12-31','yyyy-mm-dd')) into (partition p2013, partition p2014);

。。。。依次往更早时间拆分

6、删除旧表

SQL>drop table unpar_table purge;

7、重命名表

SQL>rename par_table to old_table_name;

8、重建索引

分区交换时，除主键外的所有索引都不会被交换，表上的其他索引需要重建。