【实验】【PARTITION】RANGE分区表未指定maxvalue分区将无法插入相关数据

1.创建未包含maxvalue分区的表
sec@ora10g> create table t_partition_range (id number,name varchar2(50))
2 partition by range(id)(
3 partition t_range_p1 values less than (10) tablespace tbs_part01,
4 partition t_range_p2 values less than (20) tablespace tbs_part02,
5 partition t_range_p3 values less than (30) tablespace tbs_part03
6 );

Table created.

2.模拟插入数据
sec@ora10g> insert into t_partition_range values (5,'Andy1');

1 row created.

sec@ora10g> insert into t_partition_range values (15,'Andy2');

1 row created.

sec@ora10g> insert into t_partition_range values (25,'Andy3');

1 row created.

sec@ora10g> insert into t_partition_range values (35,'Andy4');
insert into t_partition_range values (35,'Andy4')
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

到这里报错了，原因就是找不到对应的分区，无法插入

3.添加maxvalue分区即可继续插入数据
sec@ora10g> alter table t_partition_range add partition t_range_p4 values less than(maxvalue) tablespace tbs_part04;

Table altered.

sec@ora10g> insert into t_partition_range values (35,'Andy4');

1 row created.

sec@ora10g> insert into t_partition_range values (45,'Andy5');

1 row created.

sec@ora10g> commit;

Commit complete.

4.查询每一个分区中包含的数据
sec@ora10g> select * from t_partition_range partition(t_range_p1);

        ID NAME
---------- --------------------------------------------------
         5 Andy1

sec@ora10g> select * from t_partition_range partition(t_range_p2);

        ID NAME
---------- --------------------------------------------------
        15 Andy2

sec@ora10g> select * from t_partition_range partition(t_range_p3);

        ID NAME
---------- --------------------------------------------------
        25 Andy3

sec@ora10g> select * from t_partition_range partition(t_range_p4);

        ID NAME
---------- --------------------------------------------------
        35 Andy4
        45 Andy5

5.小结
就是因为这个实验的原因，往往在生产数据库中使用RANGE分区表时，都要指定一个maxvalue分区，以防止出现异常数据无法插入问题。在考虑使用分区技术的时候要考虑周全才是上策。

-- The End --

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/519536/viewspace-608839/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/519536/viewspace-608839/