Oracle Character Sets

Oracle NLS Character Sets Version 11.2.0.3

General Information

Depending on the character sets involved this may result in data loss. Try this in test environments before proceeding in production.

I have received the following email from Gunther Vermeir <gunther.vermeir@oracle.com> from Oracle and have posted it here, unedited, so that you are all aware of the issue.

I saw your post in http://forums.oracle.com/forums/thread.jspa?messageID=4081969#4081969 linking to
http://www.morganslibrary.org/reference/character_sets.html

Kindly or remove the "internal_use" trick or post a decent warning that if this used without using csscan you will corrupt data on 10G certainly (and also on lower versions).
I explain in great detail in notes referenced in Note:225912.1 Changing the Database Character Set ( NLS_CHARACTERSET ) like Note:260192.1 Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) what steps you need to do to actually do a safe conversion

in the example you have given using WE8MSWIN1252 the chances of problems is minimal, but this is ONLY true for when going from US7ASCII or WE8ISO8859P1 system and a US/West European env. so this is actually the only exception. For ANY charter set conversion csscan should be used.

Regards,
Gunther

I'm not convinced that INTERNAL_USE is a "trick" but I am convinced that posting this note is reasonable so here it is.

Data Dictionary Objects

V$NLS_PARAMETERS

V$NLS_VALID_VALUES

Demo

Change Character Sets

conn / as sysdba

col parameter format a30
col value format a30

SELECT view_name
FROM dba_views
WHERE view_name LIKE '%NLS%';

SELECT *
FROM v$nls_parameters;

SHUTDOWN IMMEDIATE;
STARTUP RESTRICT;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE CHARACTER SET WE8MSWIN1252;

-- if the above fails:
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;
SHUTDOWN IMMEDIATE;
STARTUP;

SELECT *
FROM v$nls_parameters;

CSALTER

Depending on the character sets involved this may result in data loss. Try this in test environments before proceeding in production.

Character Set Alteration

conn / as sysdba

SQL> shutdown immediate;

SQL> startup restrict;

SQL>@?/rdbms/admin/csalter.plb

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
new 6: if (UPPER('Y') <> 'Y') then
Checking data validility...
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.

C:\>

conn / as sysdba

SQL> shutdown immediate;

SQL> startup;

conn csmig/oracle1

SELECT object_name, object_type
FROM user_objects
ORDER BY 2;

exec dbms_stats.gather_schema_stats(USER);

SELECT table_name, num_rows
FROM user_tables;

SELECT * FROM csm$parameters;

CSSCAN

CSSCAN [parameters]

Parameter	Default	Prompt	Description
ARRAY	1024000	yes	Array buffer size
BOUNDARIES	-	no	List of columns size boundaries for summary report
CAPTURE	N	no	Capture convertible data
COLUMN	-	no	List of columns to scan
EXCLUDE	-	no	List of tables to exclude
FEEDBACK	-	no	Report progress for every n rows
FROMCHAR	-	no	Current database character set name
FROMNCHAR	-	no	Current database national character set name
FULL	N	yes	Scan entire database
HELP	N	no	Show help screen
LASTRPT	N	no	Generate report of the previous database scan
LCSD	N	no	Enable language and character set detection
LCSDDATA	LOSSY	no	Define the scope of the language and character set detection
LOG	scan	no	Base file name for report log
MAXBLOCKS	-	no	The maximum number of blocks that can be in a table without the table being split
PARFILE	-	no	Parameter file name
PRESERVE	N	no	Preserve existing scan results
PROCESS	1	yes	Number of concurrent scan processes
QUERY	-	no	Query to apply to restrict output before scan
SUPPRESS	-	no	Maximum number of exceptions logged for each table
TABLE	-	yes	List of tables to scan
TOCHAR	-	yes	New character set name
TONCHAR	-	no	New national character set name
USER	-	yes	Owner of the tables to be scanned
USERID	-	yes	Username/password

Character Set Scan

C:\> csscan

Character Set Scanner v2.2: Release 11.1.0.7 - Production on Mon Jan 4 18:28:34 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Username: sys as sysdba

Password:

Connected to:
Oracle database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options.

CSS-00107: Character set migration utility schema not installed

Scanner terminated unsuccessfully.

conn / as sysdba

SQL>@?\rdbms\admin\csminst.sql
-- password supplied is "oracle1"

conn csmig/oracle1

SELECT object_name, object_type
FROM user_objects
ORDER BY 2;

SQL>exit;

C:\>CSSCAN \"sys/NoWay!@db10g AS SYSDBA\" FULL=Y

Character Set Scanner v2.2 : Release 11.1.0.7.0 - Production on Tue Jan 4 18:31:03 2010

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to:
Oracle database 11g Enterprise Edition Release 11.1.0.7.0 - Production
With the Partitioning, Oracle Label Security, OLAP, Data Mining,
Oracle Database Vault and Real Application Testing options.

Current database character set is WE8MSWIN1252.

Enter new database character set name: > WE8MSWIN1252

Enter array fetch buffer size: 1024000 >

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

Enumerating tables to scan...

. process 4 scanning SYS.WRH$_WAITSTAT[AAARFOAACAAABEoAAA]
.
. process 22 scanning GLOBAL.AW$GLOBAL[AAAS2gAAJAAAAH4AAA]

Creating Database Scan Summary Report...

Creating Individual Exception Report...

Scanner terminated successfully.