database character sets utf8

From: David Heitholt <dheitholt_at_agency.com>
Date: 16 Aug 2001 14:27:45 -0700
Message-ID: <44d2315d.0108161327.31794bcc@posting.google.com>

I have a question about database character sets.

I need to change an Oracle 8.1.7 database character set from US7ASCII to UTF8. The db runs on Solaris 2.8.

My question is: "How to I find out from the database/data dictionary and dynamic views what the database's character set is?"

My info source for this topic is the Oracle Generic Doumentation CD for 8.1.7 , in particular:
Oracle8i National Language Support Guide

         Release 2 (8.1.6)

This sentence seemed to answer my question as to where to query to get the database's character set:

"NLS_DATABASE_PARAMETERS shows the current NLS parameters of the
database, including the database character set."

So I do this query:
SQL> select * from nls_database_parameters Where parameter = 'NLS_CHARACTERSET';

PARAMETER                      VALUE
------------------------------
----------------------------------------
NLS_CHARACTERSET               US7ASCII

From this I infer that the database's character set is US7ASCII. The info in Oracle Documentation below makes me believe that I can change the database character set from US7ASCII to UTF8:
"For instance, the following migration scenarios can take advantage of
the ALTER DATABASE CHARACTER SET statement because US7ASCII is a strict subset of WE8ISO8859P1, ZHS16GBK, and UTF8:

Table 3-8 Sample Migration Scenarios
Current Character Set|New Character Set|New Character Set

                                        is Strict Superset? 
 US7ASCII            WE8ISO8859P1             Yes 
 US7ASCII            ZHS16GBK                Yes 
 US7ASCII            UTF8                    Yes

So I try the commands to change the character set from US7ASCII to UTF8, but they fail with this error:
SVRMGR> ALTER DATABASE CHARACTER SET UTF8; ALTER DATABASE CHARACTER SET UTF8

                                 *

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

This leads me to believe that either my database's character set is not US7ASCII, or the conversion is not possible as described by the Oracle documentation.

I find the create script for the database and find that the database was created with explicit instructions to set the character set as WE8ISO8859P1. I think that the database's character set is WE8ISO8859P1, but I am troubled that the data dictionary views/ dynamic views do not confirm this. Any help, explanations, experiences would be appreciated. Thanks in advance. Received on Thu Aug 16 2001 - 16:27:45 CDT