非分区表转换为分区表的三种方式

创建测试环境

--创建非分区表
CREATE TABLE T_NOPARTITIONTAB( ID INT, NAME VARCHAR(20));
--插入测试数据
begin
  for i in 1..1000 loop
    insert into t_nopartitiontab(id, name) 
    values(i, DBMS_RANDOM.RANDOM_STRING('U',5)||':'|| lpad(i,4, '0'));
  end loop;
end;
commit;
--查看数据量
SELECT COUNT(*) FROM SYSDBA.T_NOPARTITIONTAB;

--创建分区表
create table t_partitiontab (
  id      int,
  name   varchar(20)
) partition by range(id) ( 
  partition p1 values less than (200),
  partition p2 values less than (400),
  partition p3 values less than (600),
  partition p4 values less than (800),
  partition pN values less than (maxvalue));

方法一、直接插入数据

INSERT INTO T_PARTITIONTAB SELECT * FROM T_NOPARTITIONTAB;
COMMIT;

查看插入结果，插入成功，且分区成功

方法二、导入导出的方式

逻辑导出非分区表

[dmdba@hzq bin]$ ./dexp sysdba/SYSDBA:5555  FILE=/home/dmdba/imp_exp.dmp TABLES="SYSDBA"."T_NOPARTITIONTAB" 

另一个实例下创建同名的表T_NOPARTITIONTAB

create table T_NOPARTITIONTAB (
  id      int,
  name   varchar(20)
) partition by range(id) ( 
  partition p1 values less than (200),
  partition p2 values less than (400),
  partition p3 values less than (600),
  partition p4 values less than (800),
  partition pN values less than (maxvalue));

逻辑导入

[dmdba@hzq bin]$ ./dimp sysdba/SYSDBA@192.168.227.20:5555  FILE=/home/dmdba/imp_exp.dmp TABLES="SYSDBA"."T_NOPARTITIONTAB" ignore=y

导入成功，且成功分区

方法三、交换分区的方式

--创建分区表
create table t_partitiontab (
  id      int,
  name   varchar(20)
) partition by range(id) ( 
  partition p1 values less than (200),
  partition p2 values less than (400),
  partition p3 values less than (600),
  partition p4 values less than (800),
  partition pN values less than (maxvalue));

使用exchange交换分区

ALTER TABLE T_PARTITIONTAB EXCHANGE PARTITION PN WITH TABLE T_NOPARTITIONTAB;

查看数据情况

使用split将分区拆分

alter table t_partitiontab split partition pN at (1000) into (partition p5, partition pmax);

再用merge合并分区

alter table t_partitiontab merge partitions p5, pmax into partition pN;

查看数据情况