Thursday, February 24, 2011

Character set tips

Alter Database Character Set Clauses
---------------------------------------
Set the character set ALTER DATABASE CHARACTER SET INTERNAL_CONVERT <CHARACTER SET>;
ALTER DATABASE CHARACTER SET INTERNAL_CONVERT WE8MSWIN1252;
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.WE8ISO8859
echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1



The above can be queried using 
SQL> select * from v$nls_parameters;



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.
Conversely you can do this on the import too. Change the NLS_LANG variable before importing and this should convert the dmp file but I personally think it's easier doing this on the export.
In Windows (dos really) you do:
set NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1
Then do the export.