oracle11.2中分区功能测试之add&split partition对global&local index的影响

生产库中某些大表的分区异常，需要对现有表进行在线操作，以添加丢失分区，因为是生产库，还是谨慎点好，今天有空，针对add&split分区对global&local索引的影响进行了测试，测试版本为oracle11.2.0.4，过程如下：首先，创建分区表：CREATE TABLE TP1 ( C1 INT PRIMARY KEY, C2 VARCHAR2(10), C3 CHAR(10)  )                        partition by range (c1) (  partition p1 values less than(6), partition p2 values less than(11), partition p3 values less than(16), partition maxvalue values less than(maxvalue)); 但在add partition时遇到了问题，报错如下：ORA-14074: 分区界限必须调整为高于最后一个分区界限根本原因是存在最后maxvalue分区，于是，再创建一个不带maxvalue的分区表：CREATE TABLE TP2 ( C1 INT PRIMARY KEY, C2 VARCHAR2(10), C3 CHAR(10)  )                        partition by range (c1) (  partition p1 values less than(6), partition p2 values less than(11), partition p3 values less than(16));然后，创建local索引：create index idx_tp2_c2 on tp2(c2) local;因为主键就是个global索引，所以，不需要另外创建global index，我们insert数据：insert into tp2 values(1,'aaa','aaa'); insert into tp2 values(2,'aaa','aaa'); insert into tp2 values(3,'aaa','aaa'); insert into tp2 values(4,'aaa','aaa'); insert into tp2 values(5,'aaa','aaa'); insert into tp2 values(6,'aaa','aaa'); insert into tp2 values(7,'aaa','aaa'); insert into tp2 values(8,'aaa','aaa'); insert into tp2 values(9,'aaa','aaa'); insert into tp2 values(10,'aaa','aaa'); insert into tp2 values(11,'aaa','aaa'); insert into tp2 values(12,'aaa','aaa'); insert into tp2 values(13,'aaa','aaa'); insert into tp2 values(14,'aaa','aaa'); insert into tp2 values(15,'aaa','aaa'); commit;