Buddy Yuan的个人技术博客

一般我们建range的分区表,都会设置一个maxvalue,用于存放越界的值,但是由于种种原因,我们可能没有设置maxvalue.这就会造成在插入数据的时候报ORA-01440:inserted partition key is beyond highest legal partition key.那么当遇到这样的情况,我们怎么修改我们的边界值呢?以下是一种思路.

SQL> create table test
2 (
3 id number
4 )
5 PARTITION BY RANGE (id)
6 (
7 PARTITION test01 VALUES LESS THAN (10) TABLESPACE TEST1,
8 PARTITION test02 VALUES LESS THAN (30) TABLESPACE TEST2,
9 PARTITION test03 VALUES LESS THAN (70) TABLESPACE TEST3
10 );
Table created.

SQL> create index idx_test on test (id) global;
Index created.

SQL> begin
2 for i in 1..80 loop
3 insert into test values(i);
4 commit;
5 end loop;
6 end;
7 /
begin
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
ORA-06512: at line 3

现在我想把最后的边界值从70改成100,然后符合我这个insert执行.此时我的test03分区已经有数据了,比较巧妙的办法是我们用exchange partition把的这个分区先转换成普通表,然后drop分区,然后在添加合理边界值的分区,然后再次exchange partition.这样我们就可以完成我们的操作.

1.将分区转换成普通表

SQL> create table convert as select * from test where 1<>1;
Table created.

SQL> alter table test
2 exchange partition test03
3 with table convert
4 including indexes without validation
5 update global indexes;
Table altered.

2.然后drop分区,并增加合理分区键值

SQL> alter table test drop partition test03;
Table altered.

SQL> alter table test add partition test03
2 values less than(100) tablespace test3;
Table altered.

3.再次exchange partition

SQL> alter table test
2 exchange partition test03
3 with table convert
4 including indexes without validation
5 update global indexes;
Table altered.

SQL> begin
2 for i in 1..80 loop
3 insert into test values(i);
4 commit;
5 end loop;
6 end;
7 /

PL/SQL procedure successfully completed.