Checking database character set

If you want to export an Oracle database to import the data in other database (imp/exp), or you want to create a new Oracle database similar to other database then you have to pay attention to the database character set and NLS_LANG environment variable. If you don't, in the case of an import/export you will end with data different from the source database, and in the case of creation of a database your application might not store data as expected, and both problems are hard to detect and difficult to solve. Therefore, spend a minute checking the NLS_CHARACTERSET in the source database and setting the NLS_LANG environment variable at the time of import or database creation, and you will not have to reexport or recreate a database for character set issues.

To check the NLS_CHARACTERSET and all localization parameters of your source database, you can query the NLS_DATABASE_PARAMETERS table:

SQL> select * from NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8ISO8859P1
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                dd-mon-yyyy
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.1.0

20 rows selected.

Having the NLS_LANGUAGE, NLS_TERRITORY and NLS_CHARACTERSET parameter values you can set environment variable NLS_LANG accordingly, or you can just set it in the target database the same way as the source database provided the value in NLS_LANG variable matches the NLS database parameter values:

oracle@myserver:~$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1

And if you're creating a database, don't forget to declare the character set properly:

CREATE DATABASE mydb
...
    CHARACTER SET WE8ISO8859P1
...

More information:

Oracle Database Globalization Support Guide