Changing Character Set in Oracle 11g
What is the character set of the Oracle DB (NLS_CHARACTERSET)?
A: NLS_CHARACTERSET in Oracle database character sets which can be stored in the database using the data types CHAR, VARCHAR2, LONG, and CLOB. That is, it does not define the language, but the range of characters that can be used.
You can change the character set using the csscan utilities and csalter (Database Character Set Scanner utilities), which are provided by Oracle. Its uses are recommended, as in versions 10g and 11g should not be used ALTER DATABASE CHARACTER SET command to make this change.
The amendment is in two stages:
1. date scanning (csscan): The scanning time is the previous identication of potential problems in changing the character set and data truncation. In this step, for example, it can be identified tables that need to have increased the size of the columns or even the characters that are not in the register of the new character set.
2. date conversion (csalter): The date conversion is the conversion of data to the new character set.
Note: Before performing the conversion date, you should conduct a full backup of BD, as there may be a data corruption.
Changing Charaset using Set Database Character Set Scanner Utilities (csscan / csalter)
– Backup FULL database (if something goes wrong, you should restore the database and redo the procedure;
– Check which character set is being used in the environment
SQL> SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter = ‘NLS_CHARACTERSET’;
As an example let’s assume that this query returned “WE8ISO8859P1” as Character Set current, this will be changed to the WE8MSWIN1252. So you should start the scanning date, excutando “csscan” to the verification of the change impact between these two Character sets.
Note: If this fix is not installed will not be able to run the “csscan” then you should first apply it.
Connect with SQLPLUS with user “SYS” and run the script csminst.sql
SQL> @? /Rdbms/admin/csminst.sql
Then run the “csscan” comand
$ csscan
Character Set Scanner v2.2: Release 11.2.0.3.0 – Production on Wed Feb 27 18:35:08 2013
Copyright (c) 1982, 2011 Oracle and / or its affiliates. All rights reserved.
Username: Password system:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
(1) Full database, (2) User (3) Table, (4) Column: 1> 1
Current database character set is WE8ISO8859P1. Enter new database character set name:> WE8MSWIN125
For the next questions you can press ENTER to use the default value, then starts the scan in the BD tables.
Enumerating tables to scan …
. 1 scanning process SYS.SOURCE $ [AAAABIAABAAAAIRAAA]
…
Creating Database Scan Summary Report …
Creating Individual Exception Report …
Scanner terminated successfully.
They will create three files in the user folder OS: scan.out, scan.err and scan.txt. In them we find information on the results of the csscan. After csscan have validated the change, you must be run csalter to perform data conversion. The script “csalter.plb” is in the “$ ORACLE_HOME/rdbms/admin” and must be performed with the SYS user.
The following will be shown step-by-step completion:
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict; ORACLE instance started.
Total System Global Area 456146944 bytes
Fixed Size 1345604 bytes
Variable Size 369100732 bytes
Database Buffers 79691776 bytes
6008832 bytes Redo Buffers
Database mounted.
Database opened.
SQL> @? / Rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary. Please Ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y / N)? Y
old 6: if (UPPER (‘& conf’) <> ‘Y’) then
6 new: if (UPPER (‘Y’) <> ‘Y’) then
Checking validility date …
begin converting system objects
PL / SQL procedure successfully completed.
Alter the database character set …
Csalter operation completed, please restart database
PL / SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
SQL>
After the end of the process, the triggers, the indexes, the contraints entres others, should be altered to their original status in case they become necessary amendments thereto.
Make one last “shutdown” and “startup” of the database.
Check with the query view NLS_DATABASE_PARAMETERS, the current NLS_CHARACTERSET.
SQL> SELECT value FROM NLS_DATABASE_PARAMETERS WHERE parameter = ‘NLS_CHARACTERSET’;
VALUE
————–
WE8MSWIN125