CHANGE CHARACTER SET IN ORACLE 11G

Basically there are three methods by which you can change the characterset of a database and the method will vary a bit depending on the Oracle database version.

a) Conventional Export and Import
b) if the database is 8i or 9i via the ALTER DATABASE CHARACTERSET command
c) For 10g and upwards, we need to use the csalter.

Kindly note that whether we can use Export/Import method to change the characterset or not will depend on the particular output obtained after running csscan (Character Scanner Utility). So even if we are planning to use Export/Import method, we should install and run the csscan utility regardless. 

Current character set AR8ISO8859P6

New character set WE8IS08859P1

Step1: Check existing character

SQL> select * from nls_database_parameters;

PARAMETER VALUE
—————————— ————————————
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS.
NLS_CHARACTERSET AR8ISO8859P6
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM

21 rows selected.

Step 2: Check whether csscan ustility is installed or not

[oracle@redhat1 admin]$CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.1.0 – Production on Tue May 18 11
:43 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: SYS AS SYSDBA

Password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

CSS-00107: Character set migration utility schema not installed

So CSSCAN utility does not installed on your system, so we want to install the CSSCAN utility. Go to csminst.sql script

Step 3: Intsall csscan utility

SQL> @/app/oracle/product/11.2.0/dbhome_1/RDBMS/ADMIN/csminst.sql

Synonym created.
View created.
View created.
View created.
View created.
Grant succeeded.
Grant succeeded.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Step 4: Run csscan to change character set

[oracle@redhat1 admin]$CSSCAN
Character Set Scanner v2.2 : Release 11.2.0.1.0 – Production on Tue May 18 11:38
:37 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
Username: SYS AS SYSDBA

Password:

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

(1)Full database, (2)User, (3)Table, (4)Column: 1 > 1

Current database character set is AR8ISO8859P6.

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 1024000 >

Enter number of scan processes to utilize(1..64): 1 > 64

. process 51 scanning EXFSYS.RLM$EVENTSTRUCT
. process 63 scanning EXFSYS.RLM$RULESETSTCODE
. process 59 scanning EXFSYS.RLM$RULESETPRIVS
. process 27 scanning EXFSYS.RLM$INCRRRSCHACT
. process 62 scanning EXFSYS.RLM$ORDERCLSALS
. process 21 scanning EXFSYS.RLM$RSPRIMEVENTS
. process 31 scanning EXFSYS.RLM$VALIDPRIVS
. process 10 scanning EXFSYS.RLM$DMLEVTTRIGS
. process 60 scanning EXFSYS.RLM$COLLGRPBYSPEC
. process 32 scanning EXFSYS.RLM$PRIMEVTTYPEMAP
. process 5 scanning EXFSYS.RLM$EQUALSPEC

Creating Database Scan Summary Report…

Creating Individual Exception Report…

Scanner terminated successfully.

[oracle@redhat1 admin]$

Step 5: STartup database into restricted mode

SQL> SHUTDOWN IMMEDIATE

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP RESTRICT

ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 301990352 bytes
Database Buffers 226492416 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.

Step 6: Run the csalter.plb script

SQL> @/app/oracle/product/11.2.0/dbhome_1/RDBMS/ADMIN/csalter.plb

0 rows created.
Function created.
Function created.
Procedure created.

This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER(‘&conf’) <> ‘Y’) then
new 6: if (UPPER(‘Y’) <> ‘Y’) then
Checking data validity…
begin converting system objects

PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.

Step 7: Restart the database to reflect new character

SQL> SHUTDOWN IMMEDIATE

Database closed.
Database dismounted.
ORACLE instance shut down.

 
SQL> STARTUP

ORACLE instance started.

Total System Global Area 535662592 bytes
Fixed Size 1375792 bytes
Variable Size 301990352 bytes
Database Buffers 226492416 bytes
Redo Buffers 5804032 bytes
Database mounted.
Database opened.

Step 8: Check the current character set.

SQL> select * from nls_database_parameters where parameter=’NLS_CHARACTERSET';

PARAMETER VALUE
—————————— —————————————-
NLS_CHARACTERSET WE8ISO8859P1

SQL>

And it’s done :)

I hope this article helped you. Your suggestions/feedback are most welcome.

Keep learning... Have a great day!!!