oracle 把非分区表转化为分区表（对split partition 和exchange partition的使用）

功能： 将一个非分区表转换为分区表基本思路： SST_DAY_TOTAL是(数据量上百万条，列比较多)一个非分区表，此时创建一个与SST_DAY_TOTAL同结构的分区表zhaozhenlong_partition，维护数据，删除SST_DAY_TOTAL表，将zhaozhenlong_partition更名为SST_DAY_TOTA，删除zhaozhenlong_partition*/--具体步骤：--1、查询非分区表数据select to_char(rpt_date, 'yyyymm'), count(*) from SST_DAY_TOTAL group by to_char(rpt_date, 'yyyymm') order by to_char(RPT_DATE, 'yyyymm');--2、创建分区表（结构和非分区表SST_DAY_TOTAL相同）create table zhaozhenlong_partition( GAS_ID VARCHAR2(12) not null, RPT_DATE DATE not null, INV_NO VARCHAR2(12), ......)tablespace sdlgpartition by range(rpt_date)(partition rest values less than (maxvalue))--3、交换数据（数据从非分区表到分区表）alter table zhaozhenlong_partition exchange partition rest with table SST_DAY_TOTAL;--4、查询分区表数据select to_char(RPT_DATE, 'mm-yyyy'), count(*) from SST_DAY_TOTAL group by to_char(RPT_DATE, 'mm-yyyy');--辅助脚本/* select 'alter table zhaozhenlong_partition split partition rest at (to_date(''' || to_char(RPT_DATE, 'yyyymm') ||''',''yyyymm'')) into (partition p' || to_char(RPT_DATE, 'yyyymm') || ', partition rest);' from zhaozhenlong_partition group by to_char(RPT_DATE, 'yyyymm') order by to_char(RPT_DATE, 'yyyymm')*/--5、对rest分区进行拆分alter table zhaozhenlong_partition split partition rest at (to_date('200608','yyyymm')) into (partition p200608, partition rest);alter table zhaozhenlong_partition split partition rest at (to_date('200609','yyyymm')) into (partition p200609, partition rest);alter table zhaozhenlong_partition split partition rest at (to_date('200610','yyyymm')) into (partition p200610, partition rest);alter table zhaozhenlong_partition split partition rest at (to_date('200611','yyyymm')) into (partition p200611, partition rest);alter table zhaozhenlong_partition split partition rest at (to_date('200612','yyyymm')) into (partition p200612, partition rest);--6、删除非分区表drop table SST_DAY_TOTAL--7、将分区表重命名为原非分区表名alter table zhaozhenlong_partition rename to SST_DAY_TOTAL--8、删除中间处理过程的分区表drop table zhaozhenlong_partition________________________________--补充如果已存在分区表，要将某未分区表（与已分区表结构完全相同）加入到分区中：alter table SST_DAY_TOTAL exchange partition p200608 with table zhaozhenlong_1;alter table SST_DAY_TOTAL exchange partition p200609 with table zhaozhenlong_2;如果zhaozhenlong_2不符合分区规则，则会报错，则需要指定without validation 来禁止检查alter table SST_DAY_TOTAL exchange partition p200609 with table zhaozhenlong_2 without validation;当然，如果你确认zhaozhenlong_1表中的数据是符合分区规则的，那指定without validation 之后，不会对zhaozhenlong_1进行全表扫描，则会缩短exchange时间