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. 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的值，都會報同樣的錯誤。

參考文獻：