Resolving ORA-12899 During RDS to ATP Schema Import

Fix ORA-12899 Error During RDS to ATP Schema Import

by

In this blog, we will discuss how to resolve the following Oracle Data Pump import error encountered while migrating an AWS RDS Oracle schema backup to Oracle ATP:

KUP-11007: conversion error loading table "ORAKLDBA"."P_ATTD_CONFIG_TAG_DTL"
ORA-12899: value too large for column TAG_VALUE (actual: 801, maximum: 500)

KUP-11009: data for row: TAG_VALUE : 0X'4249544D41502033372C33372C392C34382C312CFFE02001C3'


KUP-11007: conversion error loading table "ORAKLDBA"."P_ATTD_CONFIG_TAG_DTL"
ORA-12899: value too large for column TAG_VALUE (actual: 805, maximum: 500)

KUP-11009: data for row: TAG_VALUE : 0X'4249544D41502033342C34352C392C34382C312CFFF020019C'

Although the schema import completed successfully, a few rows failed to import into the table ORAKLDBA.P_ATTD_CONFIG_TAG_DTL.

The import log showed the following warning:

import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions

This issue occurs because the source database uses a single-byte character set (WE8MSWIN1252), while Oracle ATP uses the multibyte AL32UTF8 character set.

In this article, we will analyze the root cause of the issue, verify the affected rows, and explore multiple solutions to successfully complete the import without data loss.

Root Cause Analysis:

Initially, it appeared that the column value exceeded the target column limit:

ORA-12899: value too large for column TAG_VALUE (actual: 801, maximum: 500)

However, after validating the source database, we confirmed:

  • Maximum actual source length = 452
  • Column definition = VARCHAR2(500 BYTE)
  • No row exceeded 500 characters

Source validation query:

SQL> select max(length(TAG_VALUE)) as max_length, max(lengthb(TAG_VALUE)) as max_bytes from ORAKLDBA.P_ATTD_CONFIG_TAG_DTL;

MAX_LENGTH  MAX_BYTES
---------- ----------
       452        452

SQL>

This confirms that the source data fits within the defined column size.

Why the Import Fails?

The issue occurs during character set conversion.

The source database stores data in a single-byte character set, where each character consumes 1 byte. During import into ATP (AL32UTF8), certain characters are converted into multibyte UTF8 characters.

As a result:

Source Value Length  : 452 bytes
After UTF8 Conversion: 801 bytes
Target Column Limit  : 500 bytes

Therefore, the import fails even though the original source value is only 452 characters long.

This is commonly seen when column data contains:

  • Special characters
  • Extended ASCII characters
  • Hidden/non-printable characters
  • Encoded bitmap or binary-like text values

Important Observation:

The target column definition was:

SQL> select column_name, data_length, char_used from dba_tab_columns where owner='ORAKLDBA' and table_name='P_ATTD_CONFIG_TAG_DTL' and column_name='TAG_VALUE';

COLUMN_NAME                    DATA_LENGTH CHAR_USED
------------------------------ ----------- ---------------
TAG_VALUE                              500 B

SQL>

Here:

B = BYTE semantics
Column limit is enforced in bytes, not characters

This becomes problematic when importing multibyte UTF8 data.

Solution:

Modify the target column from BYTE semantics to CHAR semantics and increase the column size:

The best solution is to modify the target column from BYTE semantics to CHAR semantics and increase the column size before re-importing the failed rows.

SQL> alter table ORAKLDBA.P_ATTD_CONFIG_TAG_DTL modify TAG_VALUE varchar2(1000 CHAR);

Table altered.

SQL>
SQL>
SQL> select column_name, data_length, char_used from dba_tab_columns where owner='ORAKLDBA' and table_name='P_ATTD_CONFIG_TAG_DTL' and column_name='TAG_VALUE';

COLUMN_NAME                    DATA_LENGTH CHAR_USED
------------------------------ ----------- ---------------
TAG_VALUE                             1000 C

SQL>

Why CHAR Semantics is Better?

Using VARCHAR2(1000 CHAR) means Oracle stores up to 1000 characters regardless of how many bytes are required internally. This avoids failures caused by UTF8 byte expansion.

Re-Import the Failed Table:

After modifying the column, re-run the import for the failed table data:

Use Data Pump with TABLE_EXISTS_ACTION=TRUNCATE

nohup impdp admin/Admin_User's_Password@ATPDB \
directory=DATA_PUMP_DIR \
credential=AWS_CREDENTIAL_NAME \
tables=ORAKLDBA.P_ATTD_CONFIG_TAG_DTL \
table_exists_action=TRUNCATE \
dumpfile=https://rds-to-atp..........amazonaws.com/EXP_ORAKLDBA_26May26_%U.dmp \
logfile=IMP_ORAKLDBA_P_ATTD_CONFIG_TAG_DTL.log \
remap_tablespace=USERS:DATA &

Successfully completed Import Log:

Import: Release 19.0.0.0.0 - Production on Tue May 26 13:04:04 2026
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle AI Database 26ai Enterprise Edition Release 23.26.2.2.0 - Production
Master table "ADMIN"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
import done in AL32UTF8 character set and AL16UTF16 NCHAR character set
export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
Warning: possible data loss in character set conversions

Starting "ADMIN"."SYS_IMPORT_TABLE_01":
admin/********@ATPDB directory=DATA_PUMP_DIR credential=AWS_CREDENTIAL_NAME tables=ORAKLDBA.P_ATTD_CONFIG_TAG_DTL table_exists_action=TRUNCATE dumpfile=https://rds-to-atp..........amazonaws.com/EXP_ORAKLDBA_26May26_%U.dmp logfile=IMP_ORAKLDBA_P_ATTD_CONFIG_TAG_DTL.log remap_tablespace=USERS:DATA

Processing object type SCHEMA_EXPORT/TABLE/TABLE
Table "ORAKLDBA"."P_ATTD_CONFIG_TAG_DTL" exists and has been truncated. Data will be loaded but all dependent metadata will be skipped due to table_exists_action of truncate

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "ORAKLDBA"."P_ATTD_CONFIG_TAG_DTL"                 730 KB   17088 rows

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "ADMIN"."SYS_IMPORT_TABLE_01" successfully completed at Tue May 26 13:04:31 2026 elapsed 0 00:00:21

Issue has been resolved now…!

I hope my written content will helped you. Your suggestions/feedback are most welcome from orakldba.com. Keep learning… Have a great day!!!
Please comment here for your any query related to above content. You can write to mail me through my Email.

Email :- orakldba@gmail.com
LinkedIn :- https://linkedin.com/in/prasantakirtaniya
Twitter :- https://twitter.com/prasantadba
Facebook Page :- https://www.facebook.com/orakldba
Website :- https://orakldba.com
ACE Profile :- https://ace.oracle.com/apex/ace/profile/prasantakirtaniya

Leave a Comment