一般我们建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.
Post a Comment