How to change the character set of a database. You may need to change your database character set due to do some requirements.

In this example, I am going to change my db character set from UTF8 to WE8ISO8859P9

Here are my steps:

1. Be sure you have a valid backup

2. Be sure you connect correct DB

# sqlplus "/as sysdba"

AQL> select name from v$database;

NAME
---------
YOUR_DB_NAME HERE

3. Check your current 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

4. Close Db

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

5. Follow the below steps:

SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size 2155464 bytes
Variable Size 939527224 bytes
Database Buffers 1191182336 bytes
Redo Buffers 5021696 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; << before set that parameter check current value.You need to reset that parameter after setup done.

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; << before set that parameter check current value.You need to reset that parameter after setup done.

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER DATABASE CHARACTER SET internal_use WE8ISO8859P9;

Database altered.

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

Total System Global Area 2137886720 bytes
Fixed Size 2155464 bytes
Variable Size 939527224 bytes
Database Buffers 1191182336 bytes
Redo Buffers 5021696 bytes
Database mounted.
Database opened.

6. Check your current new settings

SQL> set line999
SQL> select parameter,value from v$nls_parameters where parameter='NLS_CHARACTERSET ';

PARAMETER                  VALUE
--------------                       --------------

NLS_CHARACTERSET UTF8