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 - 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
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.


  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
Choosing a Character Set

Changing a Character Set: Example

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


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


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

Many thanks
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Forced accept.

Community Support Moderator
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.