I found it. Trying to explain it tripped those last few synapses. Not
sure if it should be shared since it can completely destroy your database.
But when you're certain about your data, it's a lifesaver.
Kids, don't try this at home -
Alter database character set INTERNAL_USE new_character_set
No restrictions on the new_character_set but if you're wrong about your
data you're one dead duck.
"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 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 <[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
--
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).
