9 Replies Latest reply: May 18, 2012 1:08 AM by 708091 RSS

    ORA-14300: partitioning key maps to a partition outside maximum permitted n

    708091
      INTERVAL PARTITION on date column - MERGE issue

      The table creation script where the data will be merged is following...
      CREATE TABLE STARTTRD.MCHNT_BILLING_DUMMY
      ( FIRST_DATA_MERCHANT_ID  VARCHAR2(20 BYTE),
        MERCHANT_DBA_NAME       VARCHAR2(100 BYTE),
        MARKER_BANK_CODE        VARCHAR2(5 BYTE),
        FEE_SEQ                 VARCHAR2(5 BYTE),
        FEE_SEQ_DESCRIPTION     VARCHAR2(30 BYTE),
        FEE_FREQUENCY           VARCHAR2(2 BYTE),
        FEE_RATE                NUMBER,
        BILLING_DATE            DATE,
        BILL_TO_ADDRESS1        VARCHAR2(100 BYTE),
        BILL_TO_ADDRESS2        VARCHAR2(100 BYTE),
        BILL_TO_CITY            VARCHAR2(50 BYTE),
        BILL_TO_STATE           VARCHAR2(50 BYTE),
        BILL_TO_ZIP             VARCHAR2(30 BYTE),
        BILL_TO_COUNTRY         VARCHAR2(50 BYTE),
        REJECT_REASON           VARCHAR2(100 BYTE),
        SYSTEM_ID               VARCHAR2(2 BYTE)
      )
      TABLESPACE STARTTRD_DATA
      PCTUSED    0
      PCTFREE    10
      INITRANS   1
      MAXTRANS   255
      PARTITION BY RANGE (BILLING_DATE)
      INTERVAL( NUMTOYMINTERVAL(1,'MONTH'))
      (  
        PARTITION BILLING_DATE_JAN VALUES LESS THAN (TO_DATE(' 2012-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          LOGGING
          NOCOMPRESS 
          TABLESPACE STARTTRD_DATA
          PCTFREE    10
          INITRANS   1
          MAXTRANS   255
          STORAGE    (
                      INITIAL          8M
                      NEXT             8M
                      MINEXTENTS       1
                      MAXEXTENTS       UNLIMITED
                      PCTINCREASE      0
                      BUFFER_POOL      DEFAULT
                     ),  
        PARTITION BILLING_DATE_FEB VALUES LESS THAN (TO_DATE(' 2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
          LOGGING
          NOCOMPRESS 
          TABLESPACE STARTTRD_DATA
          PCTFREE    10
          INITRANS   1
          MAXTRANS   255
          STORAGE    (
                      BUFFER_POOL      DEFAULT
                     )
      )
      NOCOMPRESS 
      NOCACHE
      NOPARALLEL
      MONITORING;
      and the MERGE SCRIPT is the following,
       MERGE  INTO MCHNT_BILLING_DUMMY main  
                                                                                 USING MCHNT_BILL_TEMP_MATCHED temp              
                                                                                     ON (  main.FIRST_DATA_MERCHANT_ID =temp.FIRST_DATA_MERCHANT_ID AND 
                                                                                              main.SYSTEM_ID = temp. SYSTEM_ID  AND 
                                                                                              main.FEE_SEQ = temp.FEE_SEQ )
                                                                    
                                                                       WHEN MATCHED THEN 
      
                                                                              UPDATE  SET main.MERCHANT_DBA_NAME  = temp.MERCHANT_DBA_NAME, 
                                                                                                   main.MARKER_BANK_CODE    =  temp.MARKER_BANK_CODE, 
                                                                                                   main.FEE_SEQ_DESCRIPTION =  temp.FEE_SEQ_DESCRIPTION ,    
                                                                                                   main.FEE_FREQUENCY           =  temp.FEE_FREQUENCY ,   
                                                                                                   main.FEE_RATE                     =  temp.FEE_RATE,          
                                                                                                   main.BILLING_DATE               =  temp.BILLING_DATE,          
                                                                                                   main.BILL_TO_ADDRESS1      =  temp.BILL_TO_ADDRESS1 ,       
                                                                                                   main.BILL_TO_ADDRESS2      =  temp.BILL_TO_ADDRESS2 ,      
                                                                                                   main.BILL_TO_CITY               =  temp.BILL_TO_CITY,           
                                                                                                   main.BILL_TO_STATE            =  temp.BILL_TO_STATE,          
                                                                                                   main.BILL_TO_ZIP                 =  temp.BILL_TO_ZIP ,            
                                                                                                   main.BILL_TO_COUNTRY        =  temp.BILL_TO_COUNTRY,        
                                                                                                   main.REJECT_REASON           =  temp.REJECT_REASON  
       
                                                                      WHEN NOT MATCHED THEN 
      
                                                                                           INSERT ( main.FIRST_DATA_MERCHANT_ID, 
                                                                                                         main.MERCHANT_DBA_NAME,     
                                                                                                         main.MARKER_BANK_CODE,       
                                                                                                         main.FEE_SEQ,           
                                                                                                         main.FEE_SEQ_DESCRIPTION,    
                                                                                                         main.FEE_FREQUENCY,   
                                                                                                         main.FEE_RATE,     
                                                                                                         main.BILLING_DATE ,          
                                                                                                         main.BILL_TO_ADDRESS1,       
                                                                                                         main.BILL_TO_ADDRESS2,      
                                                                                                         main.BILL_TO_CITY,           
                                                                                                         main.BILL_TO_STATE,          
                                                                                                         main.BILL_TO_ZIP,            
                                                                                                         main.BILL_TO_COUNTRY,        
                                                                                                         main.REJECT_REASON,         
                                                                                                         main.SYSTEM_ID ) 
                                                                                        VALUES (   
                                                                                                         temp.FIRST_DATA_MERCHANT_ID, 
                                                                                                         temp.MERCHANT_DBA_NAME,     
                                                                                                         temp.MARKER_BANK_CODE,       
                                                                                                         temp.FEE_SEQ,           
                                                                                                         temp.FEE_SEQ_DESCRIPTION,    
                                                                                                         temp.FEE_FREQUENCY,  
                                                                                                         temp.FEE_RATE,          
                                                                                                         temp.BILLING_DATE ,          
                                                                                                         temp.BILL_TO_ADDRESS1,       
                                                                                                         temp.BILL_TO_ADDRESS2,      
                                                                                                         temp.BILL_TO_CITY,           
                                                                                                         temp.BILL_TO_STATE,          
                                                                                                         temp.BILL_TO_ZIP,            
                                                                                                         temp.BILL_TO_COUNTRY,        
                                                                                                         temp.REJECT_REASON,         
                                                                                                         temp.SYSTEM_ID )  ;
      While Merging getting the error
      ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

      Edited by: :) on May 15, 2012 6:21 PM

      Edited by: :) on May 15, 2012 6:45 PM