Changing the NLS_CHARACTERSET in Oracle 11.2.0.X oracledna Oracle Standalone Database, Uncategorized May 18, 2026 This Post is to demonstrate the changing NLS_CHARACTERSET in Oracle 11.2.0.X This can be achieved by Csscan, it is a SCAN tool that allows you to see the impact of a database character set change or assist you to correct a incorrect database NLS_CHARACTERSET setup. Csscan stores internal data in the database when running, the schema for the csscan tables is called CSMIG and is created using the csminst.sql script found in $ORACLE_HOME/rdbms/admin. Note: Before performing this activity, it is mandatory to take full backup of database. Without full backup of database, we should not perform this activity. 1. Install Csscan in Oracle RDBMS 11.2.0.x Csscan stores internal data in the database when running, the schema for the csscan tables is called CSMIG and is created using the csminst.sql script found in $ORACLE_HOME/rdbms/admin. The csminst.sql will create a CSMIG user. The default tablespace is SYSTEM, for biggest databases, it was adviced to create a separate tablespace and change the $ORACLE_HOME/rdbms/admin/csminst.sql script. Make a copy of the csminst.sql script [oracle@prod admin]$ cp csminst.sql csminst.sql_Original — connect to the database and perform below operations [oracle@prod admin]$ sqlplus / as sysdbaSQL> set TERMOUT ONSQL> set ECHO ONSQL> spool csminst.logSQL> drop user csmig cascade;drop user csmig cascade*ERROR at line 1:ORA-01918: user ‘CSMIG’ does not exist SQL> @?/rdbms/admin/csminst.sqlSQL> remSQL> rem $Header: csmig/sql/csminst.sql /st_csmig_11.2.0/1 2010/12/21 22:06:34 nli Exp $ csminst.sqlSQL> remSQL> rem Copyright (c) 1988, 2010, Oracle and/or its affiliates.SQL> rem All rights reserved.SQL> remSQL> rem NAMESQL> rem csminst.sqlSQL> rem DESCRIPTIONSQL> rem Create tables for Database Character Set Migration UtilitySQL> rem NOTESQL> rem This script must be run while connected as SYSSQL> rem MODIFIEDSQL> rem nli 12/20/10 – Backport ssubrama_bug-9215865 from mainSQL> rem ssubrama 04/14/10 – bug 9433479 add SYSMAN as dictionary userSQL> rem ssubrama 09/04/08 – bug 7047837 fix create userSQL> rem nli 07/25/08 – fix bug 7256242, add the ORADATA userSQL> rem ssubrama 06/09/08 – bug 7047837 cleanup csmig schema for securitySQL> rem xpeng 10/18/07 – fix bug 6460895SQL> rem ssubrama 01/09/07 – bug 5738695 add copyright informationSQL> rem nli 08/02/06 – bug 5372557: CSX support. Add a CNVTYPE column into CSMIG.CSM$COLUMNSSQL> rem ywu 07/14/04 – up versionSQL> rem fayang 04/23/04 – add column UNNESTED in CSM$TABLESSQL> rem fayang 04/06/04 – add SCNCOL# in EXTABLES and add a view for EXTABLESSQL> rem ywu 02/19/04 – fix bug 3434808, delete hard code passwd.SQL> rem ywu 07/29/03 – grant privilege to system for external tableSQL> rem ywu 05/30/03 – add a table for data dictionarySQL> rem ywu 05/09/03 – add led parameter to csm$errorsSQL> rem ywu 10/22/02 – add another error for codepoint exceedSQL> rem ywu 08/30/02 – up versionSQL> rem ywu 07/01/02 – add size informationSQL> rem ywu 07/02/02 – add resumableSQL> rem plinsley 01/24/02 – add include/excludeSQL> rem plinsley 04/02/01 – up versionSQL> rem plinsley 03/23/01 – #1509940SQL> rem plinsley 03/26/01 – update viewsSQL> rem plinsley 12/15/00 – remove order by from viewSQL> rem plinsley 11/03/00 – Add converter process columnSQL> rem plinsley 10/04/00 – split tables that cross filesSQL> rem plinsley 09/21/00 – Long ROWIDs in idSQL> rem plinsley 08/09/00 – Adding constraint dependency handlingSQL> rem mtozawa 06/29/00 – add csmv$ viewsSQL> rem mtozawa 06/27/00 – add browid to csm$columnsSQL> rem mtozawa 06/02/00 – specify the storage clause for csm$errorsSQL> rem mtozawa 05/26/00 – bug1314547:optimize split mechanism, add blocksSQL> rem mtozawa 05/19/00 – Change table names from SCN$* to CSM$*SQL> rem mtozawa 03/08/00 – add SPLIT supportSQL> rem mtozawa 12/21/99 – add property column to SCN$TABLES for IOTSQL> rem mtozawa 11/05/99 – remove storage params from SCN$ERRORSSQL> rem mtozawa 11/04/99 – add maxsiz to SCN$COLUMNSSQL> rem mtozawa 09/26/99 – CreationSQL> remSQL>SQL>SQL> rem *****************************************************************SQL> rem The user CSMIG owns tables and procedures of Database ScannerSQL> rem *****************************************************************SQL> WHENEVER SQLERROR EXITSQL>SQL> create user csmig identified by csmig password expire account lock2 / User created. SQL>SQL> WHENEVER SQLERROR CONTINUESQL>SQL> grant select on sys.obj$ to csmig2 / Grant succeeded. SQL> grant select on sys.col$ to csmig2 / Grant succeeded. SQL> grant select on sys.icol$ to csmig2 / Grant succeeded. SQL> grant select on sys.ind$ to csmig2 / Grant succeeded. SQL> grant select on sys.cdef$ to csmig2 / Grant succeeded. SQL> grant select on sys.con$ to csmig2 / Grant succeeded. SQL> grant select on sys.trigger$ to csmig2 / Grant succeeded. SQL> rem *****************************************************************SQL> rem DBA MUST ASSIGN PROPER TABLESPACE TO CSMIGSQL> rem *****************************************************************SQL> alter user csmig default tablespace SYSTEM quota unlimited on SYSTEM2 / User altered. SQL>SQL> rem *****************************************************************SQL> rem Add version information for csm$* schemaSQL> rem The schema version must be bumped up as csm$* schema get updated.SQL> rem VERSION HISTORY:SQL> rem 1 … 8.1.7SQL> rem *****************************************************************SQL> insert into sys.props$2 select ‘NLS_CSMIG_SCHEMA_VERSION’, ‘x’,3 ‘Character set migration utiltiy schema version #’4 from dual5 where not exists6 (select ‘x’ from sys.props$ where name = ‘NLS_CSMIG_SCHEMA_VERSION’)7 / 1 row created. SQL> update sys.props$ set value$ = 5 where name = ‘NLS_CSMIG_SCHEMA_VERSION’2 / 1 row updated. SQL> rem *****************************************************************SQL> rem Database Scanner leaves the last scan parameters in CSM$PARAMETERSSQL> rem Each background process will read scan parameters from here.SQL> rem *****************************************************************SQL> create table csmig.csm$parameters2 ( name varchar2(30) not null, /* paraneter name */3 value varchar2(80) not null /* parameter value */4 )5 / Table created. SQL> drop public synonym csm$parameters2 /drop public synonym csm$parameters*ERROR at line 1:ORA-01432: public synonym to be dropped does not exist SQL> create public synonym csm$parameters for csmig.csm$parameters2 / Synonym created. SQL> rem *****************************************************************SQL> rem Database Scanner saves the query string in CSM$QUERYSQL> rem *****************************************************************SQL> create table csmig.csm$query2 (3 value clob not null /* query value */4 )5 / Table created. SQL> drop public synonym csm$query2 /drop public synonym csm$query*ERROR at line 1:ORA-01432: public synonym to be dropped does not exist SQL> create public synonym csm$query for csmig.csm$query2 / Synonym created. SQL> rem *****************************************************************SQL> rem Database Scanner enumerate all tables need to be scannedSQL> rem Each background process will pick up a row from here for table to scanSQL> rem *****************************************************************SQL> create table csmig.csm$tables2 ( usr# number not null, /* user id