select partition_name,column_name,high_value,partition_position
from ALL_TAB_PARTITIONS a , ALL_PART_KEY_COLUMNS b
where table_name='YOUR_TABLE' and a.table_name = b.name;
This query lists the column name used as key and the allowed values. make sure, you insert the allowed values(high_value). Else, if default partition is defined, it would go there.
EDIT:
I presume, your TABLE DDL would be like this.
CREATE TABLE HE0_DT_INF_INTERFAZ_MES
(
COD_PAIS NUMBER,
FEC_DATA NUMBER,
INTERFAZ VARCHAR2(100)
)
partition BY RANGE(COD_PAIS, FEC_DATA)
(
PARTITION PDIA_98_20091023 VALUES LESS THAN (98,20091024)
);
Which means I had created a partition with multiple columns which holds value less than the composite range (98,20091024);
That is first COD_PAIS <= 98 and Also FEC_DATA < 20091024
Combinations And Result:
98, 20091024 FAIL
98, 20091023 PASS
99, ******** FAIL
97, ******** PASS
< 98, ******** PASS
So the below INSERT fails with ORA-14400; because (98,20091024) in INSERT is EQUAL to the one in DDL but NOT less than it.
SQL> INSERT INTO HE0_DT_INF_INTERFAZ_MES(COD_PAIS, FEC_DATA, INTERFAZ)
VALUES(98, 20091024, 'CTA'); 2
INSERT INTO HE0_DT_INF_INTERFAZ_MES(COD_PAIS, FEC_DATA, INTERFAZ)
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
But, we I attempt (97,20091024), it goes through
SQL> INSERT INTO HE0_DT_INF_INTERFAZ_MES(COD_PAIS, FEC_DATA, INTERFAZ)
2 VALUES(97, 20091024, 'CTA');
1 row created.
I have the table with the following colums:
foo integer default 0
'ID', 'INTEGER'
'date','DATE'
'F','VARCHAR2(20 CHAR)'
I try to insert the following values:
insert into table (foo,id,date,F) values('1','4','01.01.2016','aa');
and I get the following error:
SQL-error: ORA-14400 inserted partition key does not map to any
partition
14400. 00000 — «inserted partition key does not map to any partition»*Cause: An attempt was made to insert a record into, a Range or Composite
Range object, with a concatenated partition key that is beyond
the concatenated partition bound list of the last partition -OR-
An attempt was made to insert a record into a List object with
a partition key that did not match the literal values specified
for any of the partitions.
*Action: Do not insert the key. Or, add a partition capable of accepting
the key, Or add values matching the key to a partition specification
What did I do wrong?
UPDATE:
call meta_ddl.create_table ('table1','create table table1 (foo integer default 0) $#TABLEOPTIONS#$');
call meta_ddl.add_column ('table1','ID', 'INTEGER');
call meta_ddl.add_column_with_default('table1','DATE','DATE', 'SYSDATE', 1);
call meta_ddl.add_column ('table1','F','VARCHAR2(20 CHAR)');
Здравствуйте.
Создал для примера таблицу с range партициями по полю id
-- Create table
create table UM_TASKS_NEW2
(
id INTEGER not null,
id_task INTEGER,
status VARCHAR2(50),
result CLOB,
start_date DATE,
end_date DATE,
duration FLOAT
)
partition by range (ID)
(
partition UM_TASKS_ID_1 values less than (1)
,
partition UM_TASKS_ID_2 values less than (5)
);заполнил данными, и при попытке заполнить строку с ID=5 воспроизвелась ошибка
ORA-14400 inserted partition key does not map to any partition
Раньше работал с разбивкой по полю даты start_date по каждому дню.
Insert с новой датой создавал автоматически новую.
С полем Number автоматически партиция в таком случае создаваться не будет?
19972 views
Less than a minute
0
While inserting data to a partitioned table, got below error.
SQL> insert into RANGE_TAB values(to_date(‘24032016′,’ddmmyyyy’),100);
insert into RANGE_TAB values(to_date(‘24032016′,’ddmmyyyy’),100);
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Solution:
This error is because, the value which we are trying to insert is not satisfying the partition key range criteria.
Lets check the partition details.
SQL> select partition_name,high_value from dba_tab_partitions where table_name='RANGE_TAB';
PARTITION_NAME HIGH_VALUE
-------------- --------------------------------------------------------------------------------
P3 TO_DATE(' 2016-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P2 TO_DATE(' 2016-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
P1 TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
Here we can see the partition key is defined maximum upto 2016-03-01 00:00:00 . But we are trying to insert 2016-03-24 00:00:00 , which is not getting mapped to any partition. so it is failing.
So to fix it , add another partition, with high_value greater than the value which we are trying to insert.
SQL> alter table RANGE_TAB add partition p4 values less than (to_date(‘01042016′,’ddmmyyyy’));
SQL> insert into RANGE_TAB values(to_date(‘24032016′,’ddmmyyyy’),100);
1 row created.
The ORA-14400: inserted partition key does not map to any partition error in Oracle comes when you try to insert value in a partition table which does not fit in any defined partition for the table.
As per Oracle Error Message Guide,
Cause: An attempt was made to insert a record into, a Range or Composite Range object, with a concatenated partition key that is beyond the concatenated partition bound list of the last partition -OR- An attempt was made to insert a record into a List object with a partition key that did not match the literal values specified for any of the partitions.
Action: Do not insert the key. Or, add a partition capable of accepting the key, Or add values matching the key to a partition specification
Reproduce ORA-14400 Error and solution
Let’s assume, there is list partition table xx_list_partition with 2 partitions on column batch_id.
xx_part_1 value 1
xx_part_2 value 2
Table Script
CREATE TABLE xx_list_partition (
value_1 NUMBER,
value_2 NUMBER,
batch_id NUMBER
)
PARTITION BY LIST ( batch_id )
( PARTITION xx_batch_1 VALUES ( 1 ),
PARTITION xx_batch_2 VALUES ( 2 )
);
Let’s insert a record with batch_id = 1.
insert into xx_list_partition values (1,2,1); 1 row inserted.
Insert is successful and 1 row inserted.
Now, insert record with batch_id = 3
insert into xx_list_partition values (1,2,3); ORA-14400: inserted partition key does not map to any partition
You can see, insert is not successful and ended with partition key does not map to any partition error.
Here, you are inserting data in xx_list_partition with batch_id = 3. Oracle Database is not able to find where this value/record should fit and you get partition error. In other words, there is no partition defined which can hold the value with batch_id = 3.
You would get same error while updating as well.
Solution
Make sure to insert/update the values which satisfy the partition or make sure you create a partition table always with the default clause to avoid ORA-14400: inserted partition key does not map to any partition error in Oracle Database.
Table Script
CREATE TABLE xx_list_partition (
value_1 NUMBER,
value_2 NUMBER,
batch_id NUMBER
)
PARTITION BY LIST ( batch_id )
( PARTITION xx_batch_1 VALUES ( 1 ),
PARTITION xx_batch_2 VALUES ( 2 ),
PARTITION xx_batch_default VALUES (DEFAULT)
);
insert into xx_list_partition values (1,2,1); insert into xx_list_partition values (1,2,3);
Both the Inserts are successful and data is inserted in the table. Oracle database maps batch_id = 3 records to default partition.
I hope you liked this article. Please share and subscribe.