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');
