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

http://www.pczpg.com/a/2010/0307/2656.html

--具体步骤：

--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 sdlg

partition 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');

--辅助脚本

--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时间