| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Change Character sets
Java drivers seem to require US7ASCII.
I'd love to leave it alone.
"Goulet, Dick"
<DGoulet To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
@vicr.com> cc:
Sent by: Subject: RE: Change Character sets
ml-errors
10/22/2003 03:29
PM
Please respond
to ORACLE-L
Thomas,
Well I can't help you on that score. I do remember a discussion on going from US7ACSII to WE8ISO8859P1. And if my memory servers me correctly that is a one way trip. I believe you may well have to re-export the data & rebuild the database. Is there a specific reason why you need to do that? Personally I'd leave well enough alone.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
Sent: Wednesday, October 22, 2003 3:09 PM
To: Multiple recipients of list ORACLE-L
But I want to do it the other way round. No, there are no '?'s. The data is all US7ASCII. Yes, you're right, the import was done into a database with the wrong character set. I know about the export/import route but I believe that there is some utility that will do the conversion from WE8ISO8859P1 to US7ASCII as long as all the data in the database is compatible with US7ASCII (as is the case). I just don't remember what it is or where to find it. I remember seeing a discussion of this (I believe it was here) but searching the ORACLE-L archive, GOOGLE, and the Oracle documentation has not turned up anything. Maybe it's just wishful thinking but I'm hoping that someone would have a better memory and could point me in the right direction.
SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;
PARAMETER VALUE ------------------------------ ---------------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET WE8ISO8859P1 NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_LENGTH_SEMANTICS BYTE NLS_NCHAR_CONV_EXCP FALSE NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 9.2.0.3.0
SQL> ALTER DATABASE CHARACTER SET US7ASCII;
ALTER DATABASE CHARACTER SET US7ASCII
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
"Goulet, Dick"
<DGoulet To: Multiple recipients
of list ORACLE-L <ORACLE-L_at_fatcity.com>
@vicr.com> cc:
Sent by: Subject: RE: Change
Character sets
ml-errors
10/22/2003 02:09
PM
Please respond
to ORACLE-L
Sounds to me like someone exported their old database into a new one and now have characters showing up as '?' when there was something meaningful before. BTDT. The problem is that your NCHAR/NLS_LANG setting did not match the database character set. This can happen at two points in the process. Here is what happen to me, see if it matches your case:
Original database created with US7ASCII and then changed to
WE8ISO8859P1.
Client workstation was set up as WE8ISO8859P1 from WAY back,
prior to database character set change.
Export was taken using NLS_LANG=American_america.US7ASCII.
Import doen using NLS_LANG=American_america.WE8ISO8859P1
Lots of data started showing up with '?''s in the middle of
words, etc...
Solution:
Take a new export of the affected tables from the original database using the right NLS_LANG setting & use that to replace them in the new database.
Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA
-----Original Message-----
Sent: Wednesday, October 22, 2003 1:44 PM
To: Multiple recipients of list ORACLE-L
Actually, WE8ISO8859P1 *is* a superset of US7ASCII, which is how we were able to do the same ALTER DATABASE (in 8.1.7.2 at the time) you mentioned.
Check out
http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=11
9164.1&p_database_id=NOT
(pasting the link together) for a list of valid supersets. Your combo is
sixth in the list.
Rich
Rich Jesse System/Database Administrator rjesse_at_qtiworld.com Quad/Tech Inc, Sussex, WI USA > -----Original Message----- > From: Thomas Day [mailto:tday6_at_csc.com] > Sent: Wednesday, October 22, 2003 12:34 PM > To: Multiple recipients of list ORACLE-L > Subject: Change Character sets > > > > I have a database that was populated using an export from an US7ASCII > Oracle database. The current database is WE8ISO8859P1 which is not a > superset of US7ASCII. So I can't change the characterset > using the ALTER > DATABASE as the manual suggests. The alternative is to export the > database, drop the current instance, create a new instance > with the correct > characterset and import the old database. > > But I know that there is no data in the database that was not > supported in > the US7ASCII database. Is there a shortcut? The UPDATE> sys.PROPS$ trick
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich INET: Rich.Jesse_at_qtiworld.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Wed Oct 22 2003 - 14:44:42 CDT
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: DGoulet_at_vicr.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: tday6_at_csc.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: DGoulet_at_vicr.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thomas Day INET: tday6_at_csc.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
 |
 |