HOW TO CHANGE CHARACTER SET IN ORACLE

Install CSSCAN

login with SYS user

@?/rdbms/admin/csminst.sql

###############

csscan \"sys as sysdba\" full=y

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Current database character set is AL32UTF8.

Enter new database character set name: > WE8ISO8859P1

Enter array fetch buffer size: 1024000 >

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

Enumerating tables to scan...

The output goes into 3 files: scan.err, scan.out, scan.txt

Detailed description can be found in Oracle Support document "Csscan output explained [ID 444701.1]".

3. Backup database if it has not been done yet.

4. Dealing with Truncation and Lossy data

Check file scan.txt

With some lossy data, I had the following summary:

[Scan Summary]

All character type data in the data dictionary are convertible to the new character set

Some character type application data are not convertible to the new character set

There was no Truncation data in this DB.

Lossy data were found in one table (TOAD_PLAN_TABLE) and I’ve just dropped it.

More info about dealing with Lossy and Transaction data can be found in Oracle document " Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) [ID 260192.1]"

5. Dealing with Convertible data

"Convertible" Data Dictionary CLOB data is handled by CSALTER. No action required.

"Convertible" Application Data has to be exported and deleted!

When there are application Convertible data without Truncation/Lossy data, scan summary in scan.txt looks like:

[Scan Summary]

All character type data in the data dictionary are convertible to the new character set

All character type application data are convertible to the new character set

In this case there were two application tables with convertible data.

[Application Data Conversion Summary]

Datatype         Changeless  Convertible   Truncation  Lossy

---------- ---------------- ------------ ------------ ------

VARCHAR2     15,045,735,123          280            0      0

CHAR                      0            0            0      0

LONG                      0            0            0      0

CLOB                      0            0            0      0

VARRAY                    0            0            0      0

---------- ---------------- ------------ ------------ ------

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

USER.TABLE                Convertible  Truncation  Lossy

------------------------ ------------ ----------- ------

EDWSTG_HM.JOBS                     21           0      0

EDWSTG_HM.TRANSACTIONS            259           0      0

Export and truncate

expdp \"/ as sysdba\" DUMPFILE=t01_%u.dmp LOGFILE=t01.log tables=EDWSTG_HM.JOBS,EDWSTG_HM.TRANSACTIONS parallel=4

SQL> truncate table EDWSTG_HM.JOBS;

SQL> truncate table EDWSTG_HM.TRANSACTIONS;

6. Run CSSCAN again.

csscan \"sys as sysdba\" full=y

To run CSALTER the following output of CSSCAN (scan.txt) is required:

[Scan Summary]

All character type data in the data dictionary are convertible to the new character set

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

[Data Dictionary Conversion Summary]

…

…

The data dictionary can be safely migrated using the CSALTER script

7. Conversion

Shutdown listener and make sure nothing else connects to the database.

sqlplus / as sysdba

show parameter job_queue_processes

show parameter aq_tm_processes

-- assuming spfile is in use

alter system set job_queue_processes=0;

alter system set aq_tm_processes=0;

shutdown immediate;

startup restrict

spool convert.log

@?/rdbms/admin/csalter.plb

Restart the database and listener.

8. Post conversion steps

Restore altered parameters

alter system set job_queue_processes=10;

alter system set aq_tm_processes=1;

###############

-------------------------Change Oracle DB Character Set---------------------------------------------

If you want to change from WE8MSWIN1252 to AL32UTF8. Check the NLS parameters first.

SQL> SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

---------------------------------------Changing Commands------------------------------------------------

The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:

SQL> ALTER DATABASE [db_name] CHARACTER SET new_character_set;

(db_name is an optional)

For Example :

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

--------------------------------Steps to change the database character set------------------------------------------

To change the database character set, perform the following steps:

1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

2. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.

3. Startup Oracle database

SQL> startup mount;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

If you get the error ORA-12712, please update the following table.

"ORA-12712: new character set must be a superset of old character set"

SQL> update sys.props$ set VALUE$='AL32UTF8' where NAME='NLS_CHARACTERSET';

SQL> commit;

If you get the error ORA-12721, please login as DBA user.

"ORA-12721: operation cannot execute when other sessions are active"

4. shutdown immediate;  or shutdown normal;

5. startup oracle database

SQL> startup mount;

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

SQL> ALTER DATABASE OPEN;

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

SQL> shutdown immediate;

SQL> startup;

--------------------------------------Check the NLS parameters-------------------------------------------

SQL> SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');