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

最新推荐文章于 2021-04-13 08:00:14 发布

原创最新推荐文章于 2021-04-13 08:00:14 发布 · 1.2k 阅读

1 ·

CC 4.0 BY-SA版权

oracle 案例专栏收录该内容

42 篇文章

订阅专栏

本文详细介绍了如何在数据库中创建分区表、建立索引、插入数据，并通过多种方法解决分区扩展过程中遇到的问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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