You might try ALTER DATABASE CHARACTER SET internal_use us7ascii; Make sure you know what you're doing. See Metalink Doc Id 100751.996
On Wed, 2003-10-22 at 15:29, Goulet, Dick wrote: > 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 <[EMAIL PROTECTED]> > @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 > [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA > > > > -----Original Message----- > > From: Thomas Day [mailto:[EMAIL PROTECTED] > > 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 > > is a no-go. > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Jesse, Rich > INET: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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 Richard Quintin, DBA Information Systems & Computing, DBMS Virginia Tech -- "The only factor becoming scarce in a world of abundance is human attention." -- Kevin Kelly -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Quintin, Richard INET: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
