I have the following problem. I got dmp fail made by Oracle expdp (just few tables) from database where the character set was we8dec. I imported it into database where the character set is al32utf8. Now some of the characters are missing. For example in the table where names are stored alla ž and š are gone and empty squares are displayed instead (if I paste the charater here it is displayed as � but it's empty square in sqldeveloper).
As much as I know about impdp charater conversion is made on the fly and there is nothing I can do about it. Am I corrent?
When I used function dump in database to have a closer look on those strange charaters I found that their value in hex is FDFF and after converting hex to decimal
SELECT CHR(65023) FROM DUAL;
gives me another strange character ż˙
Maybe this output is also useful
SELECT dump(name, 1017) from ...
Typ=1 Len=11 CharacterSet=AL32UTF8: P,I,T,K,E,V,I,T,ef,bf,bd
Here those three last symbols should be Š (in hex those three symbols are in one value FDFF).
Does it mean that already in source systems those names were not correctly stored or did impdp messed things up? What are the options to correct the situation?
Source database version is 10.2.0.3.0 and I can't give exact expdp command because I recived that file and have no acces to that server. Target databse version is 220.127.116.11.0 ja impdp command:
impdp xxx@yyy directory=datapump dumpfile=filename.dmp remap_tablespace=xxx:yyy remap_tablespace=xxx:zzz logfile=impdp.log
+## Does it mean that already in source systems those names were not correctly stored or did impdp messed things up? What are the options to correct the situation?+
The impdp messed things up because characters were stored incorrectly in the source database. The characters you listed (š/ž) are WE8MSWIN1252 characters by not WE8DEC characters. They were stored in the source database thanks to a pass-through (garbage-in/garbage-out) configuration.
The replacement character you are talking about is the default Unicode replacement character U+FFFD, which encoded in UTF-8 (AL32UTF8) is 0xEF 0xBF 0xBD, i.e. CHR(15712189).
Your options are:
1) Correct the names manually, or
a) Delete the incorrect rows,
b) Create a small temporary database with WE8DEC database character set,
c) Import the problematic tables from your export file into this database
d) Run Character Set Scanner utility on the temporary database:
csscan full=y fromchar=we8mswin1252 tochar=we8mswin1252
Hopefully, only changeless data will be reported in scan.txt
e) Run the following in SQL*Plus as SYSDBA -- you must be the only logged session
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
f) Restart the database
g) Re-export the problematic tables
h) Import the new export file into the target AL32UTF8 database. This time the names should be fine.
i) Drop the temporary database.