Oracle – ORA-14300 partitioning key maps to a partition outside maximum permitted number of partitions”

ddloracleoracle11gtoad

While running Applier loading data to Oracle target in IDR 9.6.3 HotFix 1, after partitions were added to Target table
Problem Description

Informatica Data Replication (IDR) Applier process fails to Insert data to target with an Ora-14300 error, after an INTERVAL partition was added to the target table column.

Partition details:

PARTITION BY RANGE ("column_name") INTERVAL (NUMTODSINTERVAL(1,'DAY'))

The Applier log with the error logged is as follows:

"
* Informatica Data Replication Oracle  Applier, Version 9.6.3 HF1 Build 4825416
* Production Version 
* This copy of Data Replication is registered to IDR_0104, licensed for 1 machine 
* Copyright (C) 1993-2016 Informatica LLC. All Rights Reserved.
* See patents at https://www.informatica.com/legal/patents.html.
* Contact support at support@informatica.com
* Data Replication is running on host [cesp001] system [Linux] 
* Release [2.6.32-642.3.1.el6.x86_64] version [#1 SMP Sun Jun 26 18:16:44 EDT 2016]
* Machine [x86_64] CPU cores [4]

2016-10-07 13:58:12.399 (E2EE23E0) IDR-000240 I Started loading the configuration from the configuration SQLite database.
2016-10-07 13:58:12.406 (E2EE23E0) IDR-000239 I Finished loading the configuration from the configuration SQLite database.
2016-10-07 13:58:12.446 (E2EE23E0) IDR-000263 I Runtime settings: 
                                                apply.direct_load_for_audit_tables 0
                                                apply.local_time_zone_hours 8
                                                apply.recovery_table "IDR_TGT_USER"."IDR_RECOVERY"

                                                Target database:
                                                ORACLE
                                                Connection string for target database:
                                                IDR_TGT_USER/********@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.14)(PORT=1835))(CONNECT_DATA=(SERVER=DEDICATED)(ORACLE_SID=O1P)))
                                                Character set: AL32UTF8
                                                Ncharacter set: AL16UTF16
                                                NLS_LANG environment variable: AMERICAN_AMERICA.AL32UTF8
                                                Process ID: 26346
                                                Run ID: 350887
                                                Target ID: 118
                                                Multiple targets: no
                                                Continuous mode: disabled
                                                Number of threads: 1
                                                Path to the DBSYNC_HOME directory: /apps/idradmin/dbsync_home/DataReplication.linux.x86_64
                                                Path to the configuration file: /apps/idradmin/dbsync_home/DataReplication.linux.x86_64/configs/PRD.db
                                                Path to the Applier SQLite database: /apps/idradmin/dbsync_home/DataReplication.linux.x86_64/configs/PRD_loader.db
                                                Path to the intermediate file directory:/apps/idradmin/dbsync_home/DataReplication.linux.x86_64/output/PRD
2016-10-07 13:58:12.579 (E2EE23E0) IDR-070288 I Begin scan transaction file: './output/PRD_records1.trn'
2016-10-07 13:58:12.583 (E2EE23E0) IDR-070289 I End   scan transaction file: './output/PRD_records1.trn'
2016-10-07 13:58:12.587 (E2EE23E0) IDR-000384 I Started loading metadata revisions.
2016-10-07 13:58:12.587 (E2EE23E0) IDR-000385 I Finished loading metadata revisions.
2016-10-07 13:58:12.663 (E2EE23E0) IDR-070161 I Recovery run started.
2016-10-07 13:58:12.691 (E2EE23E0) IDR-070174 I Started parsing the intermediate file './output/PRD_records1.dat'.
2016-10-07 13:58:12.693 (E2EE23E0) IDR-070164 I Connecting to the target database.
2016-10-07 13:58:12.757 (E2EE23E0) IDR-070238 I Connected to the target database.
2016-10-07 13:58:12.759 (E2EE23E0) IDR-070175 I Completed parsing the intermediate file './output/PRD_records1.dat'. Processed 1706 records.
2016-10-07 13:58:12.777 (000D6700) IDR-070231 E The Applier thread 878336 encountered an error in the function 'MyOtlStream::flush'.
2016-10-07 13:58:12.777 (000D6700) IDR-009002 E The following error occurred when working with the database: 

ORA-14300: partitioning key maps to a partition outside maximum permitted number of partitions

                                                SQL statement: INSERT INTO "ODS_CRM"."TAB_REPL"(OP_TIME,OP_CODE,OP_CMT_SCN,OP_CMT_TIME,OP_XID,OP_NUM_IN_TX,"UPDATE_STAMP_O",
"FORMATTED_NAME_O","S_FORMATTED_NAME_O","PERSON_ID_TYPE2HGBST_ELM_O","GENDER2HGBST_ELM_O","CONT_LANG2HGBST_ELM_O","X_NATIONALITY2HGBST_ELM_O","X_RACE2HGBST_ELM_O",
"X_VIP_CODE2HGBST_ELM_O","X_BLCKLST_RSN2HGBST_ELM_O","OBJID_O","FIRST_NAME_O","S_FIRST_NAME_O","LAST_NAME_O","S_LAST_NAME_O","PHONE_O","FAX_NUMBER_O","E_MAIL_O","MAIL_STOP_O",
"EXPERTISE_LEV_O","TITLE_O","HOURS_O","SALUTATION_O","MDBK_O","STATE_CODE_O","STATE_VALUE_O","ADDRESS_1_O","ADDRESS_2_O","CITY_O","STATE_O","ZIPCODE_O","COUNTRY_O","STATUS_O",
"ARCH_IND_O","ALERT_IND_O","DEV_O","MOBILE_PHONE_O","MAIN_RSRC_O","BLG_EVT_GEN_STS_O","ID_VALUE_O","S_ID_VALUE_O","CONTACT_ID_O","LEGAL_NAME_O","PREVIOUS_NAME_O") 
VALUES (:COT           ,:COP         ,:CCS          ,:CCT           ,:CXD          ,:CNTX     ,:P1           ,:P2           ,:P3            ,:P4          ,:P5          ,:P6          ,:P7          ,:P8          
,:P9          ,:P10          ,:P11          ,:P12           ,:P13           ,:P14           ,:P15           ,:P16          ,:P17          ,:P18           ,:P19           ,:P20          ,:P21           ,:P22           ,:P23           ,:P24           ,:P25          ,:P26          ,:P27           ,:P28           ,:P29           ,:P30           ,:P31          ,:P32           ,:P33          ,:P34          ,:P35          ,:P36          ,:P37          ,:P38  ) 
                                                Additional info: 
                                                Error code: 14300
2016-10-07 13:58:12.778 (000D6700) IDR-070229 E Row index: 1. Columns: 142. Total columns: 142.
2016-10-07 13:58:12.778 (000D6700) IDR-070057 E The Applier thread 878336 encountered an OTL or database error when applying a row to the target.
2016-10-07 13:58:12.780 (000D6700) IDR-070062 E The Applier thread 878336 encountered an OTL or database error in the function 'ParallelPostRecordTask::FlushChangeRecordToDestination'.
2016-10-07 13:58:12.780 (000D6700) IDR-070061 E The Applier thread 878336 encountered an OTL or database error in the function 'ParallelPostRecordTask::svc'.
2016-10-07 13:58:12.785 (E2EE23E0) IDR-070090 E The Applier thread 0 returned the error code 5.
2016-10-07 13:58:12.787 (E2EE23E0) IDR-070082 E The Applier encountered a fatal error.
2016-10-07 13:58:12.794 (E2EE23E0) IDR-070132 E Could not finalize the 'PostTaskExecutor' mechanism that distributes records from the intermediate files across Applier threads.
"
​

​
Cause

This issue occurs because the column on which the partition condition was created is a virtual column created in IDR and the value is updated using SQL Expression. It is not a column originated from source.

On using virtual column, Applier first inserts physical columns and then updates virtual columns with the values calculated based on physical columns.

Thus, the partition column is inserted with NULL value first which invalidates the partition condition and results an Oracle error​.

Solution
To resolve this issue, perform one of the following:

Drop the current partition and use a different column [not a virtual column originated from IDR] as partition key and restart applier task.

OR

Based on the SQL expression being used to update the virtual column value, alter the column to include a DEFAULT value. Thus, on insert, the default value will be inserted first and the update will apply the value from the SQL expression.

You will need to enable row movement for the table, so that row is moved to an appropriate partition when Applier issues subsequent update.

Best Answer

One of the scenarios when this exception occurs is when the partition key is null in the record you are trying to insert.

Related Topic