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.
Comments
Tags: ALTER DATABASE CHARACTER, Change OracleDatabase Character Set WE8MSWIN1252 to AL32UTF8, character set, CHARACTER SET INTERNAL_USE AL32UTF8, Character Set WE8MSWIN1252 to AL32UTF8, database parameters, How to change Oracle database character set AL32UTF8, How to change Oracle database character set english to arabic, How to change Oracle database character set WE8MSWIN1252 to AL32UTF8, nls database parameters, Oracle APPs DBA, Oracle Database, oracle database character set, Oracle Database Character Set WE8MSWIN1252, Oracle Database Character Set WE8MSWIN1252 to AL32UTF8, Oracle database character set WE8MSWIN1252 to AL32UTF8.How to change Oracle database character set WE8MSWIN1252, Oracle database NLS LANGUAGE, Oracle DBA, oracle nls database parameters, oralce NLS LANGUAGE, shutdown immediate