Oracle交换分区测试

1、用exchange分区表减少初始化过程中对业务中断的影响

2、创建分区表

create table t_p (id number,name varchar2(30),addr varchar2(50))

partition by range(id)

(partition p1 values less than(10),

partition p2 values less than(20),

partition p3 values less than(30));

3、创建需要交换的普通表并插入数据

create table t_pe (id number,name varchar2(30),addr varchar2(50));

insert into t_pe;

insert into t_pe;

4、操作步骤

1）将数据初始化到一个新表t_pe，表结构（字段）跟原分区表保持一致。

2）将t_pe交换到t_p的p1分区，具体如下：alter table t_p exchange partition p1 with table t_pe;

3）重建分区表上的索引

SELECT INDEX_NAME,STATUS,NUM_ROWS,PARTITION_NAME FROM DBA_IND_PARTITIONS WHERE INDEX_NAME='IDX_TP_ID';

alter index idx_tp_id rebuild partition p1;