Home » SQL & PL/SQL » SQL & PL/SQL » ORA-14400: inserted partition key does not map to any partition (Oracle 11g, Windows)
ORA-14400: inserted partition key does not map to any partition [message #560069] Tue, 10 July 2012 06:30 Go to next message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi,

I have two tables in which one is partitioned table with the following details.

 CREATE TABLE "SCOTT"."TBL_MITTAL" 
   (	"ACCOUNT_NAME" VARCHAR2(50 BYTE), 
	"BILL_NO" VARCHAR2(50 BYTE), 
	"BILL_DATE" VARCHAR2(50 BYTE), 
	"CLI" VARCHAR2(50 BYTE), 
	"ANI" VARCHAR2(50 BYTE), 
	"CHARGE_START_DATE" TIMESTAMP (6), 
	"DUR" NUMBER, 
	"FROM_LOCATION" VARCHAR2(50 BYTE), 
	"TO_LOCATION" VARCHAR2(50 BYTE), 
	"INVOICE_ID" VARCHAR2(50 BYTE), 
	"CIRCLE" VARCHAR2(20 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;


CREATE TABLE "SCOTT"."TBL_TEMP" 
   (	"ACCOUNT_NAME" VARCHAR2(50 BYTE), 
	"BILL_NO" VARCHAR2(50 BYTE), 
	"BILL_DATE" VARCHAR2(50 BYTE), 
	"CLI" VARCHAR2(50 BYTE), 
	"ANI" VARCHAR2(50 BYTE), 
	"CHARGE_START_DATE" TIMESTAMP (6), 
	"DUR" NUMBER, 
	"FROM_LOCATION" VARCHAR2(50 BYTE), 
	"TO_LOCATION" VARCHAR2(50 BYTE), 
	"INVOICE_ID" VARCHAR2(50 BYTE), 
	"CIRCLE" VARCHAR2(20 BYTE)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(
  BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" 
  PARTITION BY RANGE ("CHARGE_START_DATE") 
 (PARTITION "P0_JAN11"  VALUES LESS THAN (TIMESTAMP' 2011-02-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_FEB11"  VALUES LESS THAN (TIMESTAMP' 2011-03-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_MAR11"  VALUES LESS THAN (TIMESTAMP' 2011-04-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_APR11"  VALUES LESS THAN (TIMESTAMP' 2011-05-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_MAY11"  VALUES LESS THAN (TIMESTAMP' 2011-06-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_JUN11"  VALUES LESS THAN (TIMESTAMP' 2011-07-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_JUL11"  VALUES LESS THAN (TIMESTAMP' 2011-08-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_AUG11"  VALUES LESS THAN (TIMESTAMP' 2011-09-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_SEP11"  VALUES LESS THAN (TIMESTAMP' 2011-10-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_OCT11"  VALUES LESS THAN (TIMESTAMP' 2011-11-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_NOV11"  VALUES LESS THAN (TIMESTAMP' 2011-12-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_DEC12"  VALUES LESS THAN (TIMESTAMP' 2012-01-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_JAN12"  VALUES LESS THAN (TIMESTAMP' 2012-02-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_FEB12"  VALUES LESS THAN (TIMESTAMP' 2012-03-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_MAR12"  VALUES LESS THAN (TIMESTAMP' 2012-04-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_APR12"  VALUES LESS THAN (TIMESTAMP' 2012-05-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS , 
 PARTITION "P0_MAY12"  VALUES LESS THAN (TIMESTAMP' 2012-06-01 00:00:00') 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" NOCOMPRESS ) ;

SQL> select count(1) from tbl_mittal;

  COUNT(1)
----------
   1857496

SQL> select min(charge_start_date) from tbl_mittal;

MIN(CHARGE_START_DATE)
---------------------------------------------------------------
28-JAN-11 11.30.54.000000 AM


SQL> select count(1) from tbl_temp;

  COUNT(1)
----------
         0


When I am trying to insert record from tbl_mittal into tbl_temp table. I am facing "ORA-14400: inserted partition key does not map to any partition" error 

SQL> insert into tbl_temp select * from tbl_mittal;
insert into tbl_temp select * from tbl_mittal
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition


AS tbl_mittal is having hugh number of records so I am providing only few rows from tbl_mittal table as test data. 

ACCOUNT_NAME	BILL_NO	BILL_DATE	CLI	ANI	CHARGE_START_DATE	DUR	FROM_LOCATION	TO_LOCATION	INVOICE_ID	CIRCLE
100000103574231	282710951193	01-Feb-11	1723006000	9314881823	28-JAN-11 11.30.54.000000000 AM	59.04	CHANDIGARH	JAIPUR	271095119	
100000118441871	283487201987	15-Jun-12	1409470011	8255318966	15-MAY-12 09.10.36.000000000 AM	28.03	CHANDIGARH	BANTWAL	348720198	
100000118441871	283487201987	15-Jun-12	1409470011	8255319271	15-MAY-12 09.10.41.000000000 AM	38.32	CHANDIGARH	BANTWAL	348720198	
100000118441871	283487201987	15-Jun-12	1409470011	8255319330	15-MAY-12 09.10.46.000000000 AM	28.81	CHANDIGARH	BANTWAL	348720198	
100000118441871	283487201987	15-Jun-12	1409470011	8255319302	15-MAY-12 09.10.53.000000000 AM	28.96	CHANDIGARH	BANTWAL	348720198	
100000118441871	283487201987	15-Jun-12	1409470011	8255319393	15-MAY-12 09.10.57.000000000 AM	38.34	CHANDIGARH	BANTWAL	348720198	
100000118441871	283487201987	15-Jun-12	1409470011	8255319610	15-MAY-12 09.11.12.000000000 AM	31.16	CHANDIGARH	BANTWAL	348720198	
100000118441871	283487201987	15-Jun-12	1409470011	8255319553	15-MAY-12 09.11.16.000000000 AM	32.1	CHANDIGARH	BANTWAL	348720198	
100000118441871	283487201987	15-Jun-12	1409470011	8255319641	15-MAY-12 09.11.30.000000000 AM	28.96	CHANDIGARH	BANTWAL	348720198


I also tried to upload the same data using sqlldr.

sqlldr log file contents is as follows:

Total logical records skipped:          0
Total logical records read:       1857532
Total logical records rejected:    801092
Total logical records discarded:       37

Run began on Tue Jul 10 16:24:11 2012
Run ended on Tue Jul 10 16:29:54 2012

Elapsed time was:     00:05:42.75
CPU time was:         00:01:12.34



So some sqlldr bad file contents is as follows.

100000118441871,283487201987,15-JUN-12,1723958000,9355115251,10-JUN-12 05.56.05.000000 PM,36.99,CHANDIGARH,AMBALA,348720198,,                         
100000118441871,283487201987,15-JUN-12,1723958000,7520533825,10-JUN-12 05.56.14.000000 PM,44.12,CHANDIGARH,AGRA,348720198,,                           
100000118441871,283487201987,15-JUN-12,1723958000,9356452151,10-JUN-12 05.56.17.000000 PM,116.83,CHANDIGARH,JALANDHAR,348720198,,                     
100000118441871,283487201987,15-JUN-12,1723958000,9331223048,10-JUN-12 05.56.21.000000 PM,28.33,CHANDIGARH,KOLKATA,348720198,,                        
100000118441871,283487201987,15-JUN-12,1723958000,7827927893,10-JUN-12 05.56.24.000000 PM,3384.33,CHANDIGARH,DELHI,348720198,,                        
100000118441871,283487201987,15-JUN-12,1723958000,9555045467,10-JUN-12 05.56.25.000000 PM,100.92,CHANDIGARH,DELHI,348720198,,                         
100000118441871,283487201987,15-JUN-12,1723958000,9318648640,10-JUN-12 05.56.28.000000 PM,29.32,CHANDIGARH,SHIMLA,348720198,,                         
100000118441871,283487201987,15-JUN-12,1723958000,8799452498,10-JUN-12 05.56.30.000000 PM,53.93,CHANDIGARH,LUCKNOW,348720198,,                        
100000118441871,283487201987,15-JUN-12,1723958000,9331391646,10-JUN-12 05.56.36.000000 PM,71.24,CHANDIGARH,KOLKATA,348720198,,                        
100000118441871,283487201987,15-JUN-12,1723958000,8882185779,10-JUN-12 05.56.39.000000 PM,23,CHANDIGARH,DELHI,348720198,,                             
100000118441871,283487201987,15-JUN-12,1723958000,8010087610,10-JUN-12 05.56.42.000000 PM,31.04,CHANDIGARH,DELHI,348720198,,


Kindly help me to resolve this.

Thanks in Advance

Pradeep

Report message to a moderator

Re: ORA-14400: inserted partition key does not map to any partition [message #560071 is a reply to message #560069] Tue, 10 July 2012 06:41 Go to previous messageGo to next message
cookiemonster
Messages: 13968
Registered: September 2008
Location: Rainy Manchester
Senior Member
The partitions are specified to hold values lower than the value specified for the partition.
So the min value is not the problem. What's the max value of timestamp in the table?

Report message to a moderator

Re: ORA-14400: inserted partition key does not map to any partition [message #560077 is a reply to message #560071] Tue, 10 July 2012 07:02 Go to previous message
pradies
Messages: 250
Registered: May 2008
Senior Member
Hi CokieMonster,


Thanke for your response. I added two more partitions and problem has been solved.

Thanks for the solution..

Pradeep

Report message to a moderator

Previous Topic: Insert from PL/SQL BLOB ..
Next Topic: trigger problem
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Sep 02 06:35:49 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.