It is an older article and it's assuming the year is 2015. Change the script so partitions look like this:
PARTITION BY RANGE (created_Date) (PARTITION tab1_part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')) TABLESPACE users, PARTITION tab1_part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')) TABLESPACE users);The whole script should look like this:
DROP TABLE tab1 PURGE; CREATE TABLE tab1 ( id NUMBER, code VARCHAR2(20), description VARCHAR2(50), created_date DATE, CONSTRAINT tab1_pk PRIMARY KEY (id) ) PARTITION BY RANGE (created_Date) (PARTITION tab1_part_2017 VALUES LESS THAN (TO_DATE('01/01/2018', 'DD/MM/YYYY')) TABLESPACE users, PARTITION tab1_part_2018 VALUES LESS THAN (TO_DATE('01/01/2019', 'DD/MM/YYYY')) TABLESPACE users); CREATE INDEX tab1_code_idx ON tab1(code) LOCAL; INSERT INTO tab1 SELECT level, CASE WHEN MOD(level,2)=0 THEN 'CODE1' ELSE 'CODE2' END, CASE WHEN MOD(level,2)=0 THEN 'Description for CODE1' ELSE 'Description for CODE2' END, CASE WHEN MOD(level,2)=0 THEN SYSDATE ELSE ADD_MONTHS(SYSDATE, 12) END FROM dual CONNECT BY level <= 100000; COMMIT; EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');This is because of this:
WHEN MOD(level,2)=0 THEN SYSDATE ELSE ADD_MONTHS(SYSDATE, 12)SYSDATE will be inserted into tab1_part_2017 and ADD_MONTHS(SYSDATE, 12) will be inserted into tab1_part_2018 and you will get equal number of rows in both partitions (50000 in each to be exact).
Error
While inserting data to a partitioned table, got below error.
SQL> insert into TEST_RANGE values(to_date('24032020','ddmmyyyy'),100);
insert into TEST_RANGE values(to_date('24032020','ddmmyyyy'),100);
*
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition
Cause
Error due to High partition range is not valid with the value you are using in Insert Operation.
Solution
You need to make a valid partition for inserting the data into the Partition Table
You can check partition of table with its range value with following Query:
SQL> select partition_name,high_value from dba_tab_partitions where table_name='TEST_RANGE';
For More Detail regarding Parttion
table, use following query:
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;
Fix ORA-14400: inserted partition key does not map to any partition
_____________________________________________________________________________________________________________________
Problem description:
Insert to a partitioned table from the application is getting failed with following error message.
java.sql.SQLException: ORA-14400: inserted partition key does not map to any partition
Solution Description
First of you have to check whether in your partitioned table has a default partition. Default partition means all the records which are not belongs to any of the other partition will get inserted into the default partition. The range will be specified with a limit of MAXVALUE.
If this partition did not belong to your table, you will have to add the partition using alter tablecommand.
ALTER TABLE “MED_AUD_DOC"
ADD PARTITION "P_DEFAULT" VALUES LESS THAN (MAXVALUE)
STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "TAB_MED_2XL"
LOB ("DOCUMENT") STORE AS SECUREFILE ( TABLESPACE "LOB_MEDI_2XL" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION NONE
CACHE NOCOMPRESS DEDUPLICATE LOB STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) ;
Try it
_____________________________________________________________________________________________________________________
Website Stats