1.Shutdown database
2.Backup the database
3. startup
4. run csscan -
C:\Documents and Settings\lingappk>csscan system/oracle full=y tochar=AL32UTF8 array=1024000 process=10
Character Set Scanner v2.0 : Release 10.1.0.4.0 - Production on Wed Nov 11 03:10:14 2009
Copyright (c) 1982, 2004, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.1.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
CSS-00107: Character set migration utility schema not installed
Scanner terminated unsuccessfully.
Error occured due to csmig schema is not present in the datbase. Create csmig by running default script available in oracle home folder
5.
SQL > @%oracle_home%\rdbms\admin\csminst.sql
(This script will drop csmig schema if exist and recreate will all the required privileges)
6.
csscan ”/sys as sysdba\” LOG=charconv.log FULL=Y CAPTURE=Y TOCHAR=AL32UTF8 ARRAY=1024000 PROCESS=10
OR
csscan "/sys as sysdb \" FULL=Y
(provide values when prompted)
. process 2 scanning SYS.TYPED_VIEW$[AAAAA/AABAAAAHRAAA]
. process 7 scanning SYS.UGROUP$[AAAAA5AABAAAAGpAAA]
. process 1 scanning SYS.USER$[AAAAAKAABAAAABZAAA]
. process 6 scanning SYS.FILE$[AAAAARAABAAAABxAAA]
Creating Database Scan Summary Report...
Creating Individual Exception Report...
Scanner terminated successfully.
7.
Run following script for the convertion
SQL> shutdown immediate
SQL> startup restrict
SQL> @%oracle_home%\rdbms\admin\csalter.plb
OR
SQL> shutdown immediate
SQL> startup mount
SQL> alter system enable restricted session;
SQL> alter system set JOB_QUEUE_PROCESSES=0 scope=memory;
SQL> alter system set AQ_TM_PROCESS=0 scope=memory;
SQL> alter database open;
SQL> alter database character set AL32UTF8;
alter database character set AL32UTF8*ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
if u want to change the characterset then the new characterset should be a superset of the older one otherwise create a new database with different characterset
SQL> SELECT * FROM nls_database_parameters WHERE parameter like '%CHARACTERSET';
PARAMETER VALUE
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16
