Oracle NLS Character Sets
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
According to the 12.1.0.1 docs:
"The Database Migration Assistant for Unicode (DMU) provides a streamlined end-to-end solution for migrating databases from legacy character sets to the Unicode character set. It is shipping with Oracle Database 12c Release 1 (12.1) and becomes the officially supported method for migration to the Unicode character set. The legacy Database Character Set Scanner (CSSCAN) and CSALTER utilities are removed from the database installation and have been desupported. The DMU also supports migrating selected prior database releases of 10.2, 11.1, and 11.2. More details are available at the OTN DMU page located at: [Click Here].

Also:
Depending on the character sets involved changing character sets 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.

Related Topics
Character Set Functions
UTL_GDK

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved