0

I try to create table from ORACLE setup script After I run the script

SQL> @home/oracle/Desktop/Setup.sql

Following output appears:

Table dropped. Table created. Index created. INSERT INTO tab1 ERROR at line 1: ORA-14400: inserted partition key does not map to any partition Commit complete. PL/SQL procedure successfully completed.

And the table is created but it's empty.

0

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).

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.