1. 创建分区表
create table zhanglei_test (c1 number, c2 varchar2(10))
partition by range(c1)
(partition p10 values less than (10),
partition p20 values less than (20),
partition p30 values less than (30),
partition pmax values less than (maxvalue)
)
2. 创建index
alter table zhanglei_test add constraint PK_test primary key (c1)
3. 插入记录
insert into zhanglei_test (c1,c2) values (1,'hello');
insert into zhanglei_test (c1,c2) values (2,'hello');
insert into zhanglei_test (c1,c2) values (3,'hello');
insert into zhanglei_test (c1,c2) values (4,'hello');
insert into zhanglei_test (c1,c2) values (5,'hello');
insert into zhanglei_test (c1,c2) values (6,'hello');
insert into zhanglei_test (c1,c2) values (7,'hello');
insert into zhanglei_test (c1,c2) values (8,'hello');
insert into zhanglei_test (c1,c2) values (9,'hello');
insert into zhanglei_test (c1,c2) values (10,'hello');
insert into zhanglei_test (c1,c2) values (11,'hello');
insert into zhanglei_test (c1,c2) values (12,'hello');
......
insert into zhanglei_test (c1,c2) values (33,'hello');
insert into zhanglei_test (c1,c2) values (34,'hello');
insert into zhanglei_test (c1,c2) values (35,'hello');
insert into zhanglei_test (c1,c2) values (36,'hello');
insert into zhanglei_test (c1,c2) values (37,'hello');
insert into zhanglei_test (c1,c2) values (38,'hello');
insert into zhanglei_test (c1,c2) values (39,'hello');
insert into zhanglei_test (c1,c2) values (40,'hello');
4. 查看index状态
select * from user_indexes
where table_name in ('ZHANGLEI_TEST')
--valid
SELECT * FROM USER_TAB_PARTITIONS
WHERE TABle_name in ('ZHANGLEI_TEST')
5.扩分区，报错ORA-14074
ALTER TABLE ZHANGLEI_TEST ADD PARTITION P40 VALUES LESS THAN (40);
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition
ORA-14074: 分区界限必须调整为高于最后一个分区界限
6.两种处理办法：
a. SPLIT PARTITION
ALTER TABLE ZHANGLEI_TEST SPLIT PARTITION pmax AT (60) INTO (PARTITION P60, PARTITION PMAX) UPDATE GLOBAL INDEXES ;
ALTER TABLE ZHANGLEI_TEST SPLIT PARTITION pmax AT (70) INTO (PARTITION P70, PARTITION PMAX) UPDATE GLOBAL INDEXES ;
ALTER TABLE ZHANGLEI_TEST SPLIT PARTITION pmax AT (80) INTO (PARTITION P80, PARTITION PMAX) UPDATE GLOBAL INDEXES ;
ALTER TABLE ZHANGLEI_TEST SPLIT PARTITION pmax AT (90) INTO (PARTITION P90, PARTITION PMAX) UPDATE GLOBAL INDEXES ;
ALTER TABLE ZHANGLEI_TEST SPLIT PARTITION pmax AT (100) INTO (PARTITION P100, PARTITION PMAX) UPDATE GLOBAL INDEXES ;
ALTER TABLE ZHANGLEI_TEST SPLIT PARTITION pmax AT (110) INTO (PARTITION P110, PARTITION PMAX) UPDATE GLOBAL INDEXES ;
b. SPLIT PARTITION & ADD PARTITION
ALTER TABLE ZHANGLEI_TEST DROP PARTITION "PMAX" UPDATE GLOBAL INDEXES
ALTER TABLE ZHANGLEI_TEST ADD PARTITION "P100" VALUES LESS THAN (100) ;
ALTER TABLE ZHANGLEI_TEST ADD PARTITION "PMAX" VALUES LESS THAN (MAXVALUE);
备注：不光是maxvalue，只要add partition value的值小于当前partition的值，都会报同样的错误。
参考文献：http://www.dbasupport.com/forums/showthread.php?threadid=34300&highlight=ORA14074
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9252210/viewspace-626170/，如需转载，请注明出处，否则将追究法律责任。
