Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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

SQL> SELECT USERENV ('language') FROM DUAL;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

The rest of our instances are set as follows:

SQL> SELECT USERENV ('language') FROM DUAL;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8ISO8859P1

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

1 Answer 1

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.

So,

  • 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:

http://download.oracle.com/docs/cd/B28359_01/server.111/b28298/ch11charsetmig.htm

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

 
discard

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.