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.
Step 8:
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.