ORA-14400: inserted partition key does not map to any partition

最新推荐文章于 2024-12-27 18:01:19 发布

cnktbkanb141909486

最新推荐文章于 2024-12-27 18:01:19 发布

阅读量584

点赞数

文章标签：数据库

本文介绍如何在Oracle数据库中创建范围分区表时遇到ORA-14400错误的原因及解决方法，包括如何正确添加maxvalue分区。

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

小叶同学的问题又来了，创建了一个分区表，insert数据的时候报错ORA-14400

我根据他的sql模拟了一下这个错误：
建表：
SQL> create table par_goolen
2 (
3 owner varchar2(30),
4 object_name varchar2(128),
5 created date
6 )
7 partition by range(created)
8 (partition P1 values less than (to_date('2006-01-01','YYYY-MM-DD')),
9 partition P2 values less than (to_date('2014-01-01','YYYY-MM-DD')),
10 partition P3 values less than (to_date('2015-01-01','YYYY-MM-DD'))
11 );

Table created.

往分区表里插入数据报错：
SQL> insert into par_goolen select owner,object_name,created from dba_objects;
insert into par_goolen select owner,object_name,created from dba_objects
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

报这个错，是因为分区表为range分区，但是没有maxvalue的分区
如上面创建的表，如果插入的数据created大于2015-01-01，则会报ORA-14400错误，另外一个，如果created字段插入的值为null，则也会报ORA-14400错误，因为oracle会吧null值存储在maxvalue分区

SQL> alter session set nls_date_format='yyyy-mm-dd';

Session altered.

SQL> select max(created) from dba_objects;

MAX(CREATE
----------
2015-01-04

SQL> select count(*) from dba_objects where created is null;

COUNT(*)
----------
0

插入null，也会报错
SQL> insert into par_goolen values('goolen','table',null);
insert into par_goolen values('goolen','table',null)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

添加maxvalue分区：
SQL> alter table par_goolen add partition pmax values less than (maxvalue);

Table altered.

SQL> insert into par_goolen select owner,object_name,created from dba_objects;

50914 rows created.

SQL> insert into par_goolen values('goolen','table',null);

1 row created.

SQL> select * from par_goolen partition (pmax) where created is null;

OWNER OBJECT_NAME CREATED
------------------------- ----------------------------------- ----------
goolen table

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

转载于:http://blog.itpub.net/23249684/viewspace-1389393/