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

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

lb517290079

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

阅读量1.2k

点赞数

分类专栏： oracle 案例

本文链接：https://blog.csdn.net/lb517290079/article/details/83728644

版权

oracle 案例专栏收录该内容

42 篇文章 0 订阅

订阅专栏

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