Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Possible characterset issues w/ datapump

Re: Possible characterset issues w/ datapump

From: Janine Sisk <janine_at_furfly.net>
Date: Mon, 22 Jan 2007 17:47:41 -0800
Message-Id: <F2C94751-C1DA-4C76-930C-9C45D20984F6@furfly.net>


I had this problem when I was using exp/imp to move from 8.1.7 to 9.x- something a while back. A database in US7ASCII will indeed store characters that don't belong there just fine, at least that was my experience.

What I ended up doing was loading the data into a US7ASCII database on the destination side and then using alter database to change the charset to WE8ISO8859P1. That worked. The data was being mangled on the imp, not on the exp, but somehow Oracle's internal conversion managed to handle it where imp's could not.

I have no idea if this experience will hold true in these modern times, but it's worth a try.

janine

On Jan 19, 2007, at 1:58 PM, Don Seiler wrote:

> We're having an issue where a "" character in a varchar2 is being
> corrupted after being funnelled through datapump. I'm reluctant to
> blame anything right now. Here is some background on the two
> machines, SOURCE and DEST. Both machines are on RHEL 3.
>
> SOURCE:
> Oracle 10.2.0.2
> OS $LANG = en_US
> OS $NLS_LANG = AMERICAN
> NLS_CHARACTERSET = US7ASCII
>
> DEST:
> Oracle 10.2.0.1
> OS $LANG = en_US.UTF-8
> OS $NLS_LANG = AMERICAN
> NLS_CHARACTERSET = WE8ISO8859P1
>
> From Oracle SQL Developer on my laptop, when I query the field with
> the funny character on SOURCE, I just see a square. When I query the
> field on DEST, I see it properly. Here is where things get
> interesting.
>
> We use expdp to dump a file from SOURCE, then FTP that file over the
> DES, then impdp that file into DEST. The developers say that the ""
> gets corrupted somewhere in that process, and that the hex value of
> the field is different than on SOURCE. When I said earlier that it
> looked fine on DEST, it was after they ran an UPDATE zap to correct
> the data. I'm waiting for another trial to see the bad data for
> myself, so bear with me.
>
> Given that the DEST characterset is actually a superset of SOURCE, I
> wouldn't expect any corruption or dataloss. Is there anything else I
> can look at or specify to track down when and where any corruption is
> taking place?
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 22 2007 - 19:47:41 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US