Changing the NLS_CHARACTERSET in Oracle 11.2.0.X

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 sysdba
SQL> set TERMOUT ON
SQL> set ECHO ON
SQL> spool csminst.log
SQL> drop user csmig cascade;
drop user csmig cascade
*
ERROR at line 1:
ORA-01918: user ‘CSMIG’ does not exist

SQL> @?/rdbms/admin/csminst.sql
SQL> rem
SQL> rem $Header: csmig/sql/csminst.sql /st_csmig_11.2.0/1 2010/12/21 22:06:34 nli Exp $ csminst.sql
SQL> rem
SQL> rem Copyright (c) 1988, 2010, Oracle and/or its affiliates.
SQL> rem All rights reserved.
SQL> rem
SQL> rem NAME
SQL> rem csminst.sql
SQL> rem DESCRIPTION
SQL> rem Create tables for Database Character Set Migration Utility
SQL> rem NOTE
SQL> rem This script must be run while connected as SYS
SQL> rem MODIFIED
SQL> rem nli 12/20/10 – Backport ssubrama_bug-9215865 from main
SQL> rem ssubrama 04/14/10 – bug 9433479 add SYSMAN as dictionary user
SQL> rem ssubrama 09/04/08 – bug 7047837 fix create user
SQL> rem nli 07/25/08 – fix bug 7256242, add the ORADATA user
SQL> rem ssubrama 06/09/08 – bug 7047837 cleanup csmig schema for security
SQL> rem xpeng 10/18/07 – fix bug 6460895
SQL> rem ssubrama 01/09/07 – bug 5738695 add copyright information
SQL> rem nli 08/02/06 – bug 5372557: CSX support. Add a CNVTYPE column into CSMIG.CSM$COLUMNS
SQL> rem ywu 07/14/04 – up version
SQL> rem fayang 04/23/04 – add column UNNESTED in CSM$TABLES
SQL> rem fayang 04/06/04 – add SCNCOL# in EXTABLES and add a view for EXTABLES
SQL> rem ywu 02/19/04 – fix bug 3434808, delete hard code passwd.
SQL> rem ywu 07/29/03 – grant privilege to system for external table
SQL> rem ywu 05/30/03 – add a table for data dictionary
SQL> rem ywu 05/09/03 – add led parameter to csm$errors
SQL> rem ywu 10/22/02 – add another error for codepoint exceed
SQL> rem ywu 08/30/02 – up version
SQL> rem ywu 07/01/02 – add size information
SQL> rem ywu 07/02/02 – add resumable
SQL> rem plinsley 01/24/02 – add include/exclude
SQL> rem plinsley 04/02/01 – up version
SQL> rem plinsley 03/23/01 – #1509940
SQL> rem plinsley 03/26/01 – update views
SQL> rem plinsley 12/15/00 – remove order by from view
SQL> rem plinsley 11/03/00 – Add converter process column
SQL> rem plinsley 10/04/00 – split tables that cross files
SQL> rem plinsley 09/21/00 – Long ROWIDs in id
SQL> rem plinsley 08/09/00 – Adding constraint dependency handling
SQL> rem mtozawa 06/29/00 – add csmv$ views
SQL> rem mtozawa 06/27/00 – add browid to csm$columns
SQL> rem mtozawa 06/02/00 – specify the storage clause for csm$errors
SQL> rem mtozawa 05/26/00 – bug1314547:optimize split mechanism, add blocks
SQL> rem mtozawa 05/19/00 – Change table names from SCN$* to CSM$*
SQL> rem mtozawa 03/08/00 – add SPLIT support
SQL> rem mtozawa 12/21/99 – add property column to SCN$TABLES for IOT
SQL> rem mtozawa 11/05/99 – remove storage params from SCN$ERRORS
SQL> rem mtozawa 11/04/99 – add maxsiz to SCN$COLUMNS
SQL> rem mtozawa 09/26/99 – Creation
SQL> rem
SQL>
SQL>
SQL> rem *****************************************************************
SQL> rem The user CSMIG owns tables and procedures of Database Scanner
SQL> rem *****************************************************************
SQL> WHENEVER SQLERROR EXIT
SQL>
SQL> create user csmig identified by csmig password expire account lock
2 /

User created.

SQL>
SQL> WHENEVER SQLERROR CONTINUE
SQL>
SQL> grant select on sys.obj$ to csmig
2 /

Grant succeeded.

SQL> grant select on sys.col$ to csmig
2 /

Grant succeeded.

SQL> grant select on sys.icol$ to csmig
2 /

Grant succeeded.

SQL> grant select on sys.ind$ to csmig
2 /

Grant succeeded.

SQL> grant select on sys.cdef$ to csmig
2 /

Grant succeeded.

SQL> grant select on sys.con$ to csmig
2 /

Grant succeeded.

SQL> grant select on sys.trigger$ to csmig
2 /

Grant succeeded.

SQL> rem *****************************************************************
SQL> rem DBA MUST ASSIGN PROPER TABLESPACE TO CSMIG
SQL> rem *****************************************************************
SQL> alter user csmig default tablespace SYSTEM quota unlimited on SYSTEM
2 /

User altered.

SQL>
SQL> rem *****************************************************************
SQL> rem Add version information for csm$* schema
SQL> rem The schema version must be bumped up as csm$* schema get updated.
SQL> rem VERSION HISTORY:
SQL> rem 1 … 8.1.7
SQL> rem *****************************************************************
SQL> insert into sys.props$
2 select ‘NLS_CSMIG_SCHEMA_VERSION’, ‘x’,
3 ‘Character set migration utiltiy schema version #’
4 from dual
5 where not exists
6 (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$PARAMETERS
SQL> rem Each background process will read scan parameters from here.
SQL> rem *****************************************************************
SQL> create table csmig.csm$parameters
2 ( name varchar2(30) not null, /* paraneter name */
3 value varchar2(80) not null /* parameter value */
4 )
5 /

Table created.

SQL> drop public synonym csm$parameters
2 /
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$parameters
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem Database Scanner saves the query string in CSM$QUERY
SQL> rem *****************************************************************
SQL> create table csmig.csm$query
2 (
3 value clob not null /* query value */
4 )
5 /

Table created.

SQL> drop public synonym csm$query
2 /
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$query
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem Database Scanner enumerate all tables need to be scanned
SQL> rem Each background process will pick up a row from here for table to scan
SQL> rem *****************************************************************
SQL> create table csmig.csm$tables
2 ( usr# number not null, /* user id of the table owner */
3 obj# number not null, /* object id of the table */
4 minrowid rowid, /* Minimum rowid of the split range of the table */
5 maxrowid rowid, /* Maximum rowid of the split range of the table */
6 property number, /* table property */
7 blocks number, /* number of blocks used by this table */
8 files number, /* number of files used by this table */
9 who number, /* internal thread id who scanned the table */
10 whoconv number, /* internal thread id who converted the table */
11 lngconv number, /* internal thread id who converted long col */
12 scnstart date, /* time table scan started */
13 scnend date, /* time table scan completed */
14 scncols number, /* number of columns to be scanned */
15 scnrows number, /* number of rows scanned */
16 cnvstart date, /* time table convert started */
17 cnvend date, /* time table convert completed */
18 lngstart date, /* time table convert long started */
19 lngend date, /* time table convert long completed */
20 cnvcols number, /* number of columns to be converted */
21 cnvrows number, /* number of rows to be converted */
22 lngrows number, /* number of rows of long to be converted */
23 addsize number,
24 lastupd rowid, /* ROWID lastupdated */
25 pstcvrows number, /* how many rows have been converted */
26 lastupdlg rowid, /* ROWID lastupdated */
27 pstcvrowslg number, /* how many rows have been converted */
28 unnested number /* if this table is unnested or not */
29 )
30 /

Table created.

SQL> drop public synonym csm$tables
2 /
drop public synonym csm$tables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$tables for csmig.csm$tables
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSM$COLUMNS contains statistic information of column data
SQL> rem *****************************************************************
SQL> create table csmig.csm$columns
2 ( usr# number not null, /* user id of the table owner */
3 obj# number not null, /* object id of the table */
4 browid rowid, /* rowid of the row in csm$tables */
5 col# number not null, /* column id */
6 intcol# number not null, /* internal column id (for ADT) */
7 dty# number not null, /* column data type */
8 frm# number not null, /* character set form */
9 numrows number not null, /* number of rows in this table */
10 nulcnt number not null, /* number of null cell data */
11 cnvcnt number not null, /* number of cell data that need to convert */
12 cnvtype number default 0 not null, /* convert type */
13 /* 1 = data in data dictionary, and can be converted by csconv*/
14 errcnt number not null, /* number of cell data that has exception */
15 sizerr number not null, /* number of cell data that exceed column size */
16 cnverr number not null, /* number of cell data that undergo lossy conv.*/
17 maxsiz number not null, /* max post conversion data size */
18 chrsiz number not null, /* truncation due to char semantics */
19 cnvsuc number, /* cells converted successfully */
20 cnvtrn number, /* cells converted with truncation */
21 cnvlos number, /* cells converted with lossy result */
22 cnvfai number /* cells failed to convert */
23 )
24 /

Table created.

SQL> drop public synonym csm$columns
2 /
drop public synonym csm$columns
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$columns for csmig.csm$columns
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSM$EXTABLES contains exception tables
SQL> rem *****************************************************************
SQL> create table csmig.csm$extables
2 ( usr# number not null, /* user id of the table owner */
3 obj# number not null, /* object id of the table */
4 col# number, /* column id */
5 intcol# number, /* internal column id (for ADT) */
6 dty# number, /* column data type */
7 frm# number, /* character set form */
8 property number default 0 not null, /* property of row */
9 scncol# number /* column id to be scaned */
10 )
11 /

Table created.

SQL> drop public synonym csm$extables
2 /
drop public synonym csm$extables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$extables for csmig.csm$extables
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSM$ERRORS contains individual exception information
SQL> rem *****************************************************************
SQL> create table csmig.csm$errors
2 ( err# number not null, /* exception type */
3 usr# number not null, /* user id of the object/data owner */
4 obj# number not null, /* object id */
5 col# number, /* column id / position */
6 intcol# number, /* internal column id (for ADT) */
7 typ# number, /* column data type / object type */
8 frm# number, /* character set form */
9 cnvsize number, /* post conversion data size */
10 id$ varchar2(1000), /* rowid / name to identify object */
11 csidleds number, /* number of charset id from led */
12 csidled1 number, /* first charset id from led */
13 csidled2 number, /* second charset id from led */
14 csidled3 number, /* third charset id from led */
15 langidleds number, /* number of language id from led */
16 langidled1 number, /* first language id from led */
17 langidled2 number, /* second language id from led */
18 langidled3 number /* third language id from led */
19 )
20 pctfree 0 pctused 99
21 storage(next 100K maxextents unlimited pctincrease 0)
22 /

Table created.

SQL> drop public synonym csm$errors
2 /
drop public synonym csm$errors
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$errors for csmig.csm$errors
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSM$LANGID contains summary information
SQL> rem *****************************************************************
SQL> create table csmig.csm$langid
2 ( obj# number not null, /* object id */
3 langid number, /* language id from led */
4 count number
5 )
6 /

Table created.

SQL> drop public synonym csm$langid
2 /
drop public synonym csm$langid
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$langid for csmig.csm$langid
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSM$CHARSETID contains summary information
SQL> rem *****************************************************************
SQL> create table csmig.csm$charsetid
2 ( obj# number not null, /* object id */
3 csid number, /* language id from led */
4 count number
5 )
6 /

Table created.

SQL> drop public synonym csm$charsetid
2 /
drop public synonym csm$charsetid
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$charsetid for csmig.csm$charsetid
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSM$INDEXES lists indexes to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$indexes
2 ( obj# number not null, /* object id of the index */
3 operation varchar2(1) /* alter or drop index */
4 )
5 /

Table created.

SQL> drop public synonym csm$indexes
2 /
drop public synonym csm$indexes
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$indexes for csmig.csm$indexes
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSM$CONSTRAINTS lists constraints to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$constraints
2 (
3 rid number not null, /* root constraint id */
4 lvl number, /* constraint level */
5 con# number not null /* internal constraint number */
6 )
7 /

Table created.

SQL> drop public synonym csm$constraints
2 /
drop public synonym csm$constraints
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$constraints for csmig.csm$constraints
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSM$TRIGGERS lists triggers to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$triggers
2 ( obj# number not null /* object id of the trigger */
3 )
4 /

Table created.

SQL> drop public synonym csm$triggers
2 /
drop public synonym csm$triggers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$triggers for csmig.csm$triggers
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSM$DICTUSERS lists triggers to be disabled
SQL> rem *****************************************************************
SQL> create table csmig.csm$dictusers
2 ( user# number not null, /* usre id for all data dictionary */
3 username varchar2(30)
4 )
5 /

Table created.

SQL> drop public synonym csm$dictusers
2 /
drop public synonym csm$dictusers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csm$dictusers for csmig.csm$dictusers
2 /

Synonym created.

SQL> insert into csmig.csm$dictusers
2 select distinct u.user_id, u.username from all_users u, sys.ku_noexp_view k
3 where (k.OBJ_TYPE=’USER’ and k.name=u.username) or (u.username in (‘SYSTEM’, ‘ORDDATA’, ‘SYSMAN’))
4 /

18 rows created.

SQL> rem
SQL> rem define CSMV$ views
SQL> rem
SQL> rem *****************************************************************
SQL> rem CSMV$TABLES lists tables (to be) scanned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$tables
2 (owner_id, owner_name, table_id, table_name, MIN_ROWID, MAX_ROWID,
3 BLOCKS, SCAN_COLUMNS, SCAN_ROWS, SCAN_START, SCAN_END)
4 as
5 select c.usr#, u.username, c.obj#, o.name,
6 rowidtochar(c.minrowid), rowidtochar(c.maxrowid),
7 c.blocks, c.scncols, c.scnrows,
8 to_char(c.scnstart,’hh24:mi:ss’), to_char(c.scnend,’hh24:mi:ss’)
9 from csm$tables c, all_users u, sys.obj$ o
10 where c.usr#=u.user_id and c.obj#=o.obj#
11 /

View created.

SQL> drop public synonym csmv$tables
2 /
drop public synonym csmv$tables
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$tables for csmig.csmv$tables
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSMV$COLUMNS lists columns scanned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$columns
2 (owner_id, owner_name, table_id, table_name, column_id, column_intid,
3 column_name, column_type, total_rows, null_rows, conv_rows, error_rows,
4 exceed_size_rows, data_loss_rows, cs_exceed_size_rows, max_post_convert_size)
5 as
6 select c.usr#, u.username, c.obj#, o.name, c.col#, c.intcol#, co.name,
7 decode(c.frm#, 2, ‘N’, ”) ||
8 decode(c.dty#, 1, ‘VARCHAR2’, 8, ‘LONG’, 96, ‘CHAR’, 112, ‘CLOB’,”),
9 c.numrows, c.nulcnt, c.cnvcnt, c.errcnt, c.sizerr, c.cnverr, c.chrsiz, c.maxsiz
10 from csm$columns c, all_users u, sys.obj$ o, sys.col$ co
11 where c.usr#=u.user_id and c.obj#=o.obj# and c.obj#=co.obj#
12 and c.col#=co.col# and c.intcol#=co.intcol#
13 /

View created.

SQL> drop public synonym csmv$columns
2 /
drop public synonym csmv$columns
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$columns for csmig.csmv$columns
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSMV$ERRORS lists exceptional data cell information
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$errors
2 (owner_id, owner_name, table_id, table_name,
3 column_id, column_intid, column_name, data_rowid,
4 column_type, error_type)
5 as
6 select e.usr#, u.username, e.obj#, o.name,
7 e.col#, e.intcol#, c.name, e.id$,
8 decode(e.frm#, 2, ‘N’, ”) ||
9 decode(e.typ#, 1, ‘VARCHAR2’, 8, ‘LONG’, 96, ‘CHAR’, 112, ‘CLOB’),
10 decode(e.err#, 0, ‘CONVERTIBLE’, 1, ‘EXCEED_SIZE’, 2, ‘DATA_LOSS’,
11 3, ‘CS_EXCEED_SIZE’)
12 from csm$errors e, all_users u, sys.obj$ o, sys.col$ c
13 where e.usr#=u.user_id and e.obj#=o.obj#
14 and e.obj#=c.obj# and e.col#=e.col# and e.intcol#=c.intcol#
15 /

View created.

SQL> drop public synonym csmv$errors
2 /
drop public synonym csmv$errors
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$errors for csmig.csmv$errors
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSMV$INDEXES lists all indexes to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$indexes
2 (index_owner_id, index_owner_name, index_id, index_name,
3 index_status#, index_status,
4 table_owner_id, table_owner_name, table_id, table_name,
5 column_id, column_intid, column_name)
6 as
7 select iu.user_id, iu.username, io.obj#, io.name, id.flags,
8 decode(bitand(id.flags,1), 1, ‘UNUSABLE’, ‘VALID’),
9 bu.user_id, bu.username, bo.obj#, bo.name,
10 cl.col#, cl.intcol#, cl.name
11 from csm$indexes ci, sys.icol$ ic, sys.ind$ id, all_users iu,
12 sys.obj$ io, all_users bu, sys.obj$ bo, sys.col$ cl
13 where ci.obj#=ic.obj# and ci.obj#=id.obj#
14 and ci.obj#=io.obj# and io.owner#=iu.user_id
15 and ic.bo# =bo.obj# and bo.owner#=bu.user_id
16 and ic.bo#=cl.obj# and ic.col#=cl.col# and ic.intcol#=cl.intcol#
17 /

View created.

SQL> drop public synonym csmv$indexes
2 /
drop public synonym csmv$indexes
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$indexes for csmig.csmv$indexes
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSMV$CONSTRAINTS lists all constraints to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$constraints
2 (owner_id, owner_name, constraint_id, constraint_name,
3 constraint_type#, constraint_type, table_id, table_name,
4 constraint_rid, constraint_level)
5 as
6 select c.owner#, u.username, c.con#, c.name, cd.type#,
7 decode(cd.type#, 1, ‘CHECK’, 2, ‘PRIMARY_KEY’, 3, ‘UNIQUE’,
8 4, ‘REFERENTIAL’, ‘UNKNOWN’),
9 o.obj#, o.name, cc.rid, cc.lvl
10 from csm$constraints cc, sys.cdef$ cd, sys.con$ c, all_users u, sys.obj$ o
11 where cc.con#=cd.con# and cc.con#=c.con#
12 and c.owner#=u.user_id and cd.obj#=o.obj#
13 /

View created.

SQL> drop public synonym csmv$constraints
2 /
drop public synonym csmv$constraints
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$constraints for csmig.csmv$constraints
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSMV$TRIGGERS lists all triggers to be disabled
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$triggers
2 (trigger_owner_id, trigger_owner_name, trigger_id, trigger_name,
3 table_owner_id, table_owner_name, table_id, table_name)
4 as
5 select ru.user_id, ru.username, tr.obj#, ro.name, bu.user_id,
6 bu.username, tr.baseobject, bo.name
7 from csm$triggers ct, sys.trigger$ tr, all_users ru, sys.obj$ ro,
8 all_users bu, sys.obj$ bo
9 where ct.obj#=tr.obj# and ct.obj#=ro.obj# and ro.owner#=ru.user_id
10 and tr.baseobject=bo.obj# and bu.user_id=bo.owner#
11 /

View created.

SQL> drop public synonym csmv$triggers
2 /
drop public synonym csmv$triggers
*
ERROR at line 1:
ORA-01432: public synonym to be dropped does not exist

SQL> create public synonym csmv$triggers for csmig.csmv$triggers
2 /

Synonym created.

SQL> rem *****************************************************************
SQL> rem CSMV$EXTABLES lists all distinct objects to be scaned
SQL> rem *****************************************************************
SQL> create or replace view csmig.csmv$extables
2 (obj#, usr#, property)
3 as
4 select distinct(obj#), usr#, property
5 from csm$extables where property=0;

View created.

SQL> /

View created.

SQL> rem *****************************************************************
SQL> rem SYS.CSMV$KTFBUE wraps the fixed table sys.x$ktfbue in data
SQL> rem dictionary for users only with DBA privilege to access
SQL> rem *****************************************************************
SQL> create or replace view sys.csmv$ktfbue as select * from sys.x$ktfbue;

View created.

SQL> /

View created.

SQL> grant select on sys.csmv$ktfbue to dba;

Grant succeeded.

SQL> /

Grant succeeded.

SQL> exit;

—

Check the csminst.log for errors. The Csmig user is by default locked

SQL> select username,account_status from dba_users where username=’CSMIG’;

USERNAME ACCOUNT_STATUS
—————————— ——————————–
CSMIG EXPIRED & LOCKED

Note: Installing Csscan in Oracle RDBMS 10.1.0.x , 10.2.0.x and 11.1.0.x is different.

=================

Check whether Csscan is working properly or not
====================================

Set the oracle_sid and then run Csscan with this one table scan “test” run:

[oracle@prod ~]$ csscan TABLE='(SYS.SQL_VERSION$)’ FROMCHAR=WE8MSWIN1252 TOCHAR=AL32UTF8 LOG=instchkc CAPTURE=N PROCESS=1 ARRAY=1024000

Character Set Scanner v2.2 : Release 11.2.0.4.0 – Production on Sun Mar 22 11:21:43 2026

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Username: system

Password:

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

Enumerating table to scan…

. process 1 scanning SYS.SQL_VERSION$[AAAACVAABAAAASgAAA]

Creating Database Scan Summary Report…

Creating Individual Exception Report…

Scanner terminated successfully.

===

If you have ” Scanner terminated successfully.” message then simply delete the generated instchkc.txt and instchkc.out and Csscan is now ready for usage.

===

Csscan will create 3 files :
dbcheck.out a log of the output of csscan
dbcheck.txt a Database Scan Summary Report
dbcheck.err contains the rowid’s of the Lossy rows reported in dbcheck.txt (if any).

===

Make sure no data losss in Lossy column of instchkc.txt file.

[oracle@prod ~]$ cat instchkc.txt
Database Scan Summary Report

Time Started : 2026-03-22 11:21:48
Time Completed: 2026-03-22 11:21:49

Process ID Time Started Time Completed
———- ——————– ——————–
1 2026-03-22 11:21:48 2026-03-22 11:21:48
———- ——————– ——————–

[Database Size]

Tablespace Used Free Total Expansion
————————- ————— ————— ————— —————
SYSTEM 745.50M 4.50M 750.00M .00K
SYSAUX 472.19M 27.81M 500.00M .00K
UNDOTBS1 41.69M 8.31M 50.00M .00K
TEMP .00K .00K .00K .00K
USERS 4.06M 960.00K 5.00M .00K
EXAMPLE 309.88M 35.75M 345.63M .00K
TEST_TTS 1.06M 98.94M 100.00M .00K
————————- ————— ————— ————— —————
Total 1,574.38M 176.25M 1,750.63M .00K

[Database Scan Parameters]

Parameter Value
—————————— ————————————————
CSSCAN Version v2.1
Instance Name prod11g
Database Version 11.2.0.4.0
Scan type Selective tables
Scan CHAR data? YES
Database character set WE8MSWIN1252
FROMCHAR WE8MSWIN1252
TOCHAR AL32UTF8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 1
Capture convertible data? NO
—————————— ————————————————

[Scan Summary]

All character type application data remain the same in the new character set

[Data Dictionary Conversion Summary]

Data Dictionary Tables:

Datatype Changeless Convertible Truncation Lossy
——————— —————- —————- —————- —————-
VARCHAR2 4 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
——————— —————- —————- —————- —————-
Total 4 0 0 0
Total in percentage 100.000% 0.000% 0.000% 0.000%

XML CSX Dictionary Tables:

Datatype Changeless Convertible Truncation Lossy
——————— —————- —————- —————- —————-
VARCHAR2 0 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
——————— —————- —————- —————- —————-
Total 0 0 0 0
Total in percentage 0.000% 0.000% 0.000% 0.000%

[Application Data Conversion Summary]

Datatype Changeless Convertible Truncation Lossy
——————— —————- —————- —————- —————-
VARCHAR2 0 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
CLOB 0 0 0 0
VARRAY 0 0 0 0
——————— —————- —————- —————- —————-
Total 0 0 0 0
Total in percentage 0.000% 0.000% 0.000% 0.000%

[Distribution of Convertible, Truncated and Lossy Data by Table]