Oracle Import - Character Set Problem
I am having a problem with Oracle Import that has me pulling my hair out. Here’s the situation.
I have a client providing me an Oracle Export file (pertinent portion of the export log is below) 8i is running on NT 4 SP6a.
Connected to: Oracle8i Release 126.96.36.199.0 - Production
JServer Release 188.8.131.52.0 - Production
Export done in WE8ISO8859P1 character set and WE8ISO8859P1 NCHAR character set
Note: grants on tables/views/sequences/roles will not be exported
Note: indexes on tables will not be exported
Note: constraints on tables will not be exported
About to export specified tables via Conventional Path ...
Current user changed to WEBSITE
. . exporting table BLA_BLA 99 rows exported
Export terminated successfully without warnings.
I am attempting import this file to a 9i server that has the character set WE8MSWIN1252. Before I run my import I set the following environmental variables:
I run my import and some characters are converted to ¿. (pertinent portion of the import log is below)
Connected to: Oracle9i Enterprise Edition Release 184.108.40.206.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 220.127.116.11.0 - Production
Export file created by EXPORT:V08.01.06 via conventional path
import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character set
import server uses WE8MSWIN1252 character set (possible charset conversion)
export server uses WE8ISO8859P1 NCHAR character set (possible ncharset conversion)
. importing SYSTEM's objects into SYSTEM
. importing WEBSITE's objects into WEBSITE
. . importing table "BLA_BLA" 99 rows imported
Import terminated successfully without warnings.
I suspect I am having the character conversion issue because the import thinks its using AL16UTF16 as the import NCHAR character set but I am setting it to WE8ISO8859P1 before I do the import. I am sure that the problem is not the export because I have viewed the export dump file and the ¿ character is not there.
Anyone have any ideas on how to resolve this?
May be you have some non ASCII characters in dump file. Did you try setting
UTF8 is superset of ASCII.
Just tried what you suggested and no luck, same results.
I do know that some of the characters in the dump are extended ASCII characters (i.e. > ASC(127)) But I do know they exist in the character set I am using because I can enter data and retrieve data using the same characters.
I even went as far as recreating the database using WE8ISO8859P1 as the character set and ended up with the same results.
I'm not sure if it makes a difference or not but I am see this behavior in columns typed as CLOBS.
Are you trying from the same machine where DB is running or doing from other box. Is the version is same (Exp/imp) when it got exported.
Since I got the same problem before when I am trying to import using export done by 8.1.7 client . And I am using 9.0.1 client to import from remote machine.
The export is done using Oracle8i Release 18.104.22.168.0 and the import
using Oracle9i Enterprise Edition Release 22.214.171.124.0. I do not have any IP connection to the 8i machine to run the export using the 9i client.
Did you get the chance to check oracle matrix . For what version of DB you can use different version clients. Since ORACLE 9i by default uses different character set than 8i. Myself I got lot of problems to import as well as loading data using direct path between 9i clients vs 8i Database .
Excerpt from Metalink Note #15095.1:
Regarding the National Characterset:
NCLOBs are exported/imported in UCS-2/AL16UTF16.
NCHAR/NVARCHAR2s are exported in the server's national character set.
So this is something you can't influence.
Does this mean I pretty much stuck and theres nothing I can do with respect to an import?
I'm not sure, unless you could be able to get to the server and see possible options with different NLS settings for Export.
Otherwise, You can try importing this if you have 8i installtions and play with it exporting with different NLS settings and see what helps.Here all I'm saying to you is trial and error if you can't seek help from Oracle on this issue.
Whichever is faster, read few more docs from metalink to figure out the issue OR T&E to figure out yourself.
Always big head ache with this NLS settings and character set conversions and lobs :(
Click Here to Expand Forum to Full Width