Changing Character Sets Of Database

When computer systems process characters, they use numeric codes instead of the graphical representation of the character. For example, when the database stores the letter A, it actually stores a numeric code that is interpreted by software as the letter. These numeric codes are especially important in a global environment because of the potential need to convert data between different character sets.

Character set currently used

To see which character set currently used by the database, please do the following:

SQL> conn / as sysdba

SQL> SELECT view_name FROM dba_views WHERE view_name LIKE '%NLS%';

VIEW_NAME
------------------------------
V_$NLS_PARAMETERS
V_$NLS_VALID_VALUES
GV_$NLS_PARAMETERS
GV_$NLS_VALID_VALUES
NLS_SESSION_PARAMETERS
NLS_INSTANCE_PARAMETERS
NLS_DATABASE_PARAMETERS
EXU9NLS

8 rows selected.

To see details

SQL> select * from nls_database_parameters;

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8ISO8859P1
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 10.2.0.2.0

20 rows selected.

Changing Character Sets

Note: Depending on the character sets involved this may result in data loss. So please try this in test environments before proceeding in production.

SQL> CONN / AS SYSDBA

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP RESTRICT;
SQL>ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL>ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL>ALTER DATABASE CHARACTER SET WE8MSWIN1252;

If the above fails:

SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

done ! Now see what we have done:

SQL>SELECT * FROM gv_$nls_parameters;