How to change Oracle database character set WE8MSWIN1252 to AL32UTF8

Changing character set for Oracle database.

My scenario is to support Arabic data format.

My existing oracle database character set is : WE8MSWIN1252

My requirement is to change this character set to  AL32UTF8 ( Which will support Arabic format)

Step :1

Set your ORACLE_HOME and SID .

Windows :


SET  ORACLE_HOME= E:\app\7stl\product\11.1.0\db_1

SET SID=ORCL

Linux : Use export command to set ORACLE_HOME and SID.



Step 2 :

Connect sqlplus through command prompt.

Enter user name as below shown in screenshot.
          user-name:    / as sysdba




step 3:

Shutdown your database immediate or normal by using below command.

               shutdown normal
 

Step 4 :

mount your database by using below command

            startup mount



step 5:

Now execute below commands

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
System altered.





Step 6:

SQL> ALTER DATABASE OPEN;
Database altered.



Step 7:

Now change the character set by using below command.

ALTER DATABASE CHARACTER SET AL32UTF8;



If you facing this error '' ORA-12712: new character set must be a superset of old character set''
use below command to overcome from this issue.

ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;




If you facing below error while executing above command then follow below steps:

ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-30556: functional index is defined on the column to be modified

Execute this command first then followed by character set command.

SQL> alter system set "_system_trig_enabled"=FALSE

System altered.

SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE AL32UTF8;

Database altered.

Step 9:

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

SQL> STARTUP;





Now query your Database to confirm the change in CHARACTER SET.

Use below query :

select * from database_properties where PROPERTY_NAME in ('NLS_CHARACTERSET',  'NLS_NCHAR_CHARACTERSET');



Your character set has changed successfully.
Please let me know if you facing any other issues when you implementing this.

Regards,
Naga