Export (impdp) in one character set and import (impdp) in different

Environment: Oracle 10g, WIndows 2003

When I import data from

Source: 10.1.0.2.0 WE8MSWIN1252 AMERICAN_AMERICA database
with nls_length_semantics BYTE
Target: 10.2.0.1.0 AL32UTF8 AMERICAN_AMERICA database
with nls_length_semantics BYTE

I get error for some rows and those rows do not get imported, rest everything goes fine.

ORA-02374: conversion error loading table "ABC"
ORA-12899: value too large for column MYFIELD (actual: 263, maximum: 255)
ORA-02372: data for row: MYFIELD : 'per e-mail received 09oct06 ..

Please note that problem comes only with those rows where whole length is cooupied.. e.g. IF I have char(10), and all 10 bytes are occupied, then I get this problem. But If I have at least 1 byte free, then import is successful.

I am not sure changing source table column length will cause an issue or not due to application constraints.

I have tried setting Target db's nls_length_semantics to CHAR but that didn't help - I still get the error. Changing Target Column's length cause other issue with triggers and doesn't import any row - So I cann't use that option. Also due to many different db migration that we need to do, I donnot think field level changes will be good option.

I am not sure, how safe is to use nls_length_semantics to CHAR in Source db and then try.

By the way, I have read this:
http://forums.oracle.com/forums/thre...hreadID=267176

Is there any workaround/Option/advice I can use.

-D

You are moving from WE8MSWIN1252 to AL32UTF8 (Unicode)

Your affected columns on target database are too small and can't handle the growth-in-bytes or some Unicode characters.

In your example above MYFIELD column is as big as 263 bytes while column size is just 255

You might want to check Metalink Note:119119.1

A workaround -if permitted by application, is to pre-create affected tables in Target database giving offending columns a larger size. In this case don't forget to use skip=y during Import.

Quote:

---

Originally Posted by PAVB

You might want to check Metalink Note:119119.1

A workaround -if permitted by application, is to pre-create affected tables in Target database giving offending columns a larger size. In this case don't forget to use skip=y during Import.

---

Thanks PAVB.
That will help.
-D