INTERVAL RANGE Partition Giving ORA-14400 (Doc ID 1081230.1)

Last updated on FEBRUARY 02, 2017

Applies to:

Oracle Database - Enterprise Edition - Version 11.1.0.7 and later
Information in this document applies to any platform.

Symptoms

The following INSERT fails for a INTERVAL RANGE PARTITION table:

SQL> insert into test1 values (20100401000000000000001,'');
commit;insert into test1 values (20100401000000000000001,'')
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Changes

The following table has been defined:

CREATE TABLE test1 (id number(23), datacol blob)
PARTITION BY RANGE(id)
INTERVAL(1000000000000000) STORE IN (users)
(PARTITION p1 VALUES LESS THAN (1000000000000000)
TABLESPACE users,
PARTITION p2 VALUES LESS THAN (2000000000000000)
TABLESPACE users,
PARTITION p3 VALUES LESS THAN (30000000000000000)
TABLESPACE users);


This is a normal Interval Partitioned Table:

At this point I will quote from the Data Warehousing Documents regarding INTERVAL PARTITIONs.

Interval partitioning is an extension to range partitioning in which, beyond a point in time, partitions are defined by an interval. Interval partitions are automatically created by the database when data is inserted into the partition.


So, the above table should be able to cope with the following SQL INSERT Statements.  These are detailed below in the CAUSE section.

SQL Used to validate new partition created:

select table_owner,table_name,partition_name,high_value from dba_tab_partitions where table_name='TEST1';

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms