RE: Database Character set related question

Hi Ashoke,  

I do not think that you can change character set from US7ASCII (8-bit character set) to UTF8 (variable length character set) without exp/imp. But even if you do this with exp/imp, some characters might be lost - since your database is 7-bit by definition.  

I would suggest you to change character set to some 8-bit character set (e.g. WE8MSWIN1252 or WE8ISO8859P1) and national character set to UTF8 (or other unicode character set). You can only try to guess what character clients were typing in (French, German,..., place of ˆ symbol) and based on that you can choose appropriate character set.  

Here is a general procedure how to alter database character set.  

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION; ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252; -- you can try without INTERNAL_USE first.
ALTER DATABASE NATIONAL CHARACTER SET UTF8; SHUTDOWN IMMEDIATE; Note that INTERNAL_USE is undocumented Oracle feature and Oracle does not suggest to use it. However on Metalink you can find several articles that references to this option. E.g. Note:225938.1. ALTER DATABASE does not convert characters - it just changes database character set definition.  

Test on non-production environment before doing the change on production. And make a backup of database before altering character set.  

Let me know if you need more details. I might be able to help because I did similar procedure on number of production databases few years ago.  

Regards  

Mindaugas Navickas
Oracle Certified Professional  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Mandal, Ashoke
Sent: October 27, 2005 12:42 PM
To: oracle-l_at_freelists.org
Subject: Database Character set related question

Greetings All,

The database CHARACTER SET is US7ASCII. The data in the database contains 8 bits (> 127 ASCII ) values. We cannot display these values, instead we see "?" character via the sqlplus session. We want to upgrade this database to 9.2.0.4, but before we do that , we want to make sure that we don't lose any characters that are currently stored in the database.
 We are attempting to run CSSCAN to verify. We want to use UTF8 in the new 9i
database. How do we determine what the character set was used to put the data into the database.

It's definitely not US7ASCII!

Is there a object that we can look at to see what another session is currently using? It is difficult for us to get access to the user 's workstation to see there configuration.

Thanks,

Ashoke

--
http://www.freelists.org/webpage/oracle-l