ORA-14074: partition bound must collate higher than that of the last partition

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. 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的值，都会报同样的错误。