Alter Database Character Set Clauses
To set the character set
ALTER DATABASE CHARACTER SET INTERNAL_CONVERT <CHARACTER SET>;
To set the national character set
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_CONVERT <CHARACTER SET>;
Export database with setting CHARACTER SET to avoid character set mismatch b/w databases
To set the character set
ALTER DATABASE CHARACTER SET INTERNAL_CONVERT <CHARACTER SET>;
ALTER DATABASE CHARACTER SET INTERNAL_CONVERT WE8MSWIN1252;
To set the national character set
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_CONVERT <CHARACTER SET>;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_CONVERT AL16UTF16;
Export database with setting CHARACTER SET to avoid character set mismatch b/w databases
1) Change the environment variable of NLS_LANG to that of your target database:
echo $NLS_LANG export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1 echo $NLS_LANG AMERICAN_AMERICA.WE8ISO8859P1
The above can be queried using
SQL> select * from v$nls_parameters;
Format:
NLS_LANG = LANGUAGE_TERRITORY.CHARACTER_SET
2) Do your export. You should see something like this before the data starts to get exported:
.. .. Export done in WE8ISO8859P1 character set and UTF8 NCHAR character set server uses UTF8 character set (possible charset conversion) .. ..3) Now you should be able to import the .dmp file into your target database without issues.
Conversely you can do this on the import too. Change the NLS_LANG variable before importing and this should convert the .dmp file to the target database compatible but I personally think it's easier doing this on the export.
In Windows you do:
set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
Then do the export.