Looking for something? Try here..

Thursday, February 24, 2011

Character set tips

Alter Database Character Set Clauses

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.WE8ISO8859
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.