Character Set Migration

Just yesterday, someone on LinkedIn has created discussion with following question:

Hello,

I want to convert Character Set of database from UTF8 to US7ASCII. (Degrade)

I have used CSSCAN utility, which says very less data loss.(Few MB's)

Please suggest best possible way for this migration. This is to be performed on Dev DB.

My response was following:

Other guys might be more helpful, since I’ve never had a "luck" of changing character set.

You haven't specified your database version (you always should), so I'll point you to Oracle Database Documentation for 10g Release 2, which seems to be quite specific on the subject:
http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch11charsetmig.htm#CEGDHJFF

Please google doc for your version.

My guts tell me that if DB is quite small, I would go for full Export and Import.

Michal

PS: Don't forget to backup you DB first

So I figured it would be good idea to actually try it by myself. I used full database export and import I thought would be a good solution (all is done on database version 11.2.0.4):

dbca - create database TESTDB - Character set: AL32UTF8, National Character Set: AL16UTF16

$ sqlplus / as sysdba

SQL> create user michal identified by michal;
SQL> grant dba to michal;
SQL> grant DATAPUMP_EXP_FULL_DATABASE to michal;
SQL> create directory DB_EXPORT as '/oradata/dump';
SQL> grant read,write on directory DB_EXPORT to michal; 

$ sqlplus michal/michal

SQL> create table my_test_table
(
 name varchar2(1024), 
 nname nvarchar2(1024)
)
tablespace users; 

SQL> insert into my_test_table values ('Michal Šimoník','Michal Šimoník');
SQL> insert into my_test_table values ('včelí úly','včelí úly');
SQL> insert into my_test_table values ('žvatlám šišlám','žvatlám šišlám');
SQL> commit;

$ sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/csminst.sql 
$ csscan full=y tochar=US7ASCII process=4
user: sys/Manager as sysdba 

$ view scan.err 

.... User : MICHAL Table : MY_TEST_TABLE Column: NAME Type : VARCHAR2(1024) Number of Exceptions : 3 Max Post Conversion Data Size: 14 ROWID Exception Type Size Cell Data(first 30 bytes) ------------------ ------------------ ----- ------------------------------ AAAVU3AAEAAAACsAAA lossy conversion Michal Šimoník AAAVU3AAEAAAACsAAB lossy conversion vcelí úly AAAVU3AAEAAAACsAAC lossy conversion žvatlám šišlám ------------------ ------------------ ----- ------------------------------

$ expdp michal/michal full=Y directory=DB_EXPORT dumpfile=testdb.dmp logfile=testdb.log

dbca - drop database TESTDB
dbca - create database TESTDB - Character set: US7ASCII, National Character Set: AL16UTF16

$ sqlplus / as sysdba

SQL> create user michal identified by michal;
SQL> grant dba to michal;
SQL> grant DATAPUMP_IMP_FULL_DATABASE to michal;
SQL> create directory DB_EXPORT as '/oradata/dump';
SQL> grant read,write on directory DB_EXPORT to michal;

$ impdp michal/michal full=Y directory=DB_EXPORT dumpfile=testdb.dmp logfile=testdb_imp.log
$ sqlplus michal/michal

SQL> select * from my_test_table;

NAME               NNAME ------------------ ---------------- Michal Simonik     Michal Šimoník   vceli uly          včelí úly        zvatlam sislam     žvatlám šišlám  

Results seemed as expected. But still, it's a small test. I would expect difficulties with things like advanced queuing, replications, etc.

Note:
As pointed out in the discussion, I would also encourage you to read through Oracle Document Changing the NLS_CHARACTERSET From AL32UTF8 / UTF8 (Unicode) to another NLS_CHARACTERSET in 8i, 9i , 10g and 11g (Doc ID 1283764.1) on Oracle Support.