Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

We have two Oracle DB's that are set to:



The rest of our instances are set as follows:



This is creating all sorts of problems in data being sent from one db to another. Is it possible to convert the two "non-standard" db's to match the others. If so, how?

share|improve this question
up vote 2 down vote accepted

well, the first character set is Unicode, which basically supports all languages. The second one ("P1") is western-Europe ASCII, supporting English and some extra western characters.


  • in what languages is your data? what languages will you need to support?
  • Do you want to convert UNICODE db to ASCII db or the other way around?

Converting from WE8...P1 to Unicode can easily be done by exporting (setting NLS_LANG to WE8..P1), creating a new, empty database, and importing (again, setting NLS_LANG to WE8..P1). However, converting to Unicode has some side effects, especially regarding to limits of any varchar2 columns - if it was varchar2 (20), it usually means 20 bytes, so when using Unicode 20 bytes can hold less (non-English) characters... So, to avoid data truncation, some columns might need to be enlarged... All the details are here:

Converting from UTF8 to WE8..P1 is again, doing export with NLS_LANG=WE8...P1 (you will now lose all characters that don't map into P1 ASCII, like Arabic, Chinese etc), creating an empty We8..P1 database and importing the data with NLS_LANG=WE8ISO8859P1.

share|improve this answer
+1, but just a clarification on converting WE8..P1 to UTF8: Your new, empty DB must be created as UTF8, correct? – DCookie Jul 20 '09 at 18:21
absolutely... you create a new, empty DB with the required character set and import the data from the "wrong" character set/ – Ofir Manor Jul 22 '09 at 16:40

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.