CHARACTER SET MIGRATION in ORACLE

ISSUE:

Database is using an invalid (non-UTF8) character set: (NLS_CHARACTERSET = WE8MSWIN1252) was given when tried to install space walk application.

CAUSE:

database was created with WE8MSWIN1252 because host os is windows. 

SOLUTION:

user csscan, csalter, datapump to migrate character set to AL32UTF8

STEPS:

1. csscan full=Y tochar=AL32UTF8

2. export user with application data need be converted. drop them from database

3. run csalter to change dictinary table character set

4. import back the dropped user.

POINTS:

it is good to user AL32UTF8 as character set at beginning. 

datapump and origical exp/imp tools can do character conversion on fly.

TABLE/VIEW:

nls_session_parameters

nls_instance_parameters

nls_database_parameters

v$nls_valid_values

v$nls_parameters

CHECK database character set:

select * from nls_database_parameters where parameter like '%CHAR%';

NLS paramters:

PARAMETER

------------------------------

NLS_LANGUAGE

NLS_TERRITORY

NLS_CURRENCY

NLS_ISO_CURRENCY

NLS_NUMERIC_CHARACTERS

NLS_CHARACTERSET

NLS_CALENDAR

NLS_DATE_FORMAT

NLS_DATE_LANGUAGE

NLS_SORT

NLS_TIME_FORMAT

NLS_TIMESTAMP_FORMAT

NLS_TIME_TZ_FORMAT

NLS_TIMESTAMP_TZ_FORMAT

NLS_DUAL_CURRENCY

NLS_COMP

NLS_LENGTH_SEMANTICS

NLS_NCHAR_CONV_EXCP

NLS_NCHAR_CHARACTERSET

NLS_RDBMS_VERSION

reference:

http://www.morganslibrary.org/reference/character_sets.html

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch12scanner.htm#g1035448

http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch11charsetmig.htm#g1011430

https://forums.oracle.com/forums/thread.jspa?threadID=990007

http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch1overview.htm#i1005900

https://forums.oracle.com/forums/thread.jspa?messageID=3471873

https://forums.oracle.com/forums/thread.jspa?threadID=2206301