Changing the Oracle Database Characterset.

Hello,

If you want to change your database character set due to do some requirements.

Let’s do it step by step

1. Check your current database settings

SQL> set line999
SQL> select * from v$nls_parameters;
PARAMETER                VALUE
--------------           --------------
NLS_LANGUAGE             AMERICAN
NLS_TERRITORY           AMERICA
NLS_CURRENCY            $
NLS_ISO_CURRENCY        AMERICA
NLS_NUMERIC_CHARACTERS  .,
NLS_CALENDAR            GREGORIAN
NLS_DATE_FORMAT         DD-MON-RR
NLS_DATE_LANGUAGE       AMERICAN
NLS_CHARACTERSET        UTF8
NLS_SORT                BINARY
NLS_TIME_FORMAT         HH.MI.SSXFF AM

PARAMETER               VALUE
--------------          --------------
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_NCHAR_CHARACTERSET  AL16UTF16
NLS_COMP                BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP     FALSE

2. Shutdown Database

SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.

3. Follow the below steps:

SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET internal_use WE8ISO8859P9;
SQL> SHUTDOWN IMMEDIATE; 
SQL> startup;

It is OK!