Using IMP and EXP with UTF8 settings

Hi EE,

I am using Oracle 10g on Solaris. The database, I am told, is configured for UTF8. I have exported our development database and I need to import it into my test server which is also on solaris with the same oracle versions. The development app is fine but when I import the dump into the test environment, queries seem to bring back not only the english results but also ones with the double byte as well.

Here is the exp command I use
exp system@ITSMDEV file=/artmp/ardb01-utf8.dmp log=/artmp/ardb01-utf8.log owner=aradmin

and I import using
imp system@ITSMDEV file=/artmp/ardb01-utf8.dmp full=y

When I import, I get the following message that makes me think its not correct.
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
Export done in AL32UTF8 character set and UTF8 NCHAR character set
server uses UTF8 character set (possible charset conversion)

So I think the UTF8 Vs UTF8 NCHAR is something that may be causing it to behave different.

Please can someone help and explain how I can exp using just UTF8 and not UTF8 NCHar, if thats the issue.

Im not that great at Oracle but I know a little. :)

Thanks for your time guys
Kind regards
Danny
LVL 3
smurffAsked:
Who is Participating?
 
markwall1977Connect With a Mentor Commented:
Hmmm... the environment for the exp utility can override the internal DB parameter setting for NLS_LANG.  (This was true in 9i).

To be certain you are exporting in UTF8 you should override the NLS_LANG parameter from within your UNIX shell. The export util will then implicitly export the data in this charset from within your shell environment. This will avoid the conversion warning and ensure your binary dump file is in UTF8.

You could try running before you run the export command.

export NLS_LANG="UTF8" 

Open in new window

0
 
sonicefuCommented:

SELECT *
  FROM nls_database_parameters;

------------------------
use following statement as DBA user at Destination database for required character set conversion

ALTER DATABASE dbName CHARACTER SET requiredCharacterSet;

-----------------------

then again import
0
 
sonicefuCommented:
Choosing a Character Set
http://download-uk.oracle.com/docs/cd/B10501_01/server.920/a96529/ch2.htm#101203
------------------------------

Changing a Character Set: Example

The following statements change the database character set and national character set to the UTF8 character set:

ALTER DATABASE CHARACTER SET UTF8;
ALTER DATABASE NATIONAL CHARACTER SET UTF8;

The database name is optional, and the character set name is specified without quotation marks.
0
 
smurffAuthor Commented:
Thanks for the replies. I done the query and this is the result

NLS_CHARACTERSET
UTF8

So I dont understand why the import is saying possible char conversion, can someone please explain?

Many thanks
Danny
0
 
Computer101Commented:
Forced accept.

Computer101
Community Support Moderator
0
All Courses

From novice to tech pro — start learning today.