Changing database character set

I changed character set in my database from WE8MSWIN1252 charset to AL32UTF8. I made it so:

>sqlplus.exe /nolog
>update sys.props$ set VALUE$=’AL32UTF8′ where NAME=’NLS_CHARACTERSET’;

>commit;
/*restart Oracle services or just execute shutdown and startup command */

ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER DATABASE CHARACTER SET AL32UTF8;
COMMIT WORK;
SHUTDOWN;
STARTUP;
SELECT * FROM nls_session_parameters;
–Instance parameters (change with ALTER SYSTEM):
SELECT * FROM nls_instance_parameters;
–Database parameters (set during database creation):
SELECT * FROM nls_database_parameters;

Result below:

LIVE LIVEDR
NLS_LANGUAGE VIETNAMESE VIETNAMESE
NLS_TERRITORY VIETNAM VIETNAM
NLS_CURRENCY ? ?
NLS_ISO_CURRENCY VIETNAM VIETNAM
NLS_NUMERIC_CHARACTERS ,. ,.
NLS_CHARACTERSET AL32UTF8 WE8MSWIN1252
NLS_CALENDAR GREGORIAN GREGORIAN
NLS_DATE_FORMAT DD-MM-RRRR DD-MM-RRRR
NLS_DATE_LANGUAGE VIETNAMESE VIETNAMESE
NLS_SORT VIETNAMESE VIETNAMESE
NLS_TIME_FORMAT HH24:MI:SSXFF HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT DD-MM-RRRR HH24:MI:SSXFF DD-MM-RRRR HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT HH24:MI:SSXFF TZR HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MM-RRRR HH24:MI:SSXFF TZR DD-MM-RRRR HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY ? ?
NLS_COMP BINARY BINARY
NLS_LENGTH_SEMANTICS BYTE BYTE
NLS_NCHAR_CONV_EXCP FALSE FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16 AL16UTF16
NLS_RDBMS_VERSION 10.2.0.4.0 10.2.0.4.0

After that, check NLS_CHARACTERSET parameter again. It changed to AL32UTF8, now we can import dump file or clone db.

Note that registry key HKEY_LOCAL_MACHINE\ SOFTWARE\ORACLE\HOME0\NLS_LANG must be updated by your characterset. No need to reboot server after this operation.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s