Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Change Character sets

RE: Change Character sets

From: Thomas Day <tday6_at_csc.com>
Date: Thu, 23 Oct 2003 05:09:25 -0800
Message-ID: <F001.005D418D.20031023050925@fatcity.com> 






Thanks.  But since it was an import from an US7ASCII database I am
completely confident that there'll be no problems.  But that's about the
only circumstances in which I'd try something like that.




                                                                                                                                       
                      "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 05:24                                                                                                 
                      PM                                                                                                               
                      Please respond                                                                                                   
                      to ORACLE-L                                                                                                      
                                                                                                                                       
                                                                                                                                       








Well, let me know when the shotgun goes off.  I'll send the black lab out
to find you!! *-)



Dick Goulet


Senior Oracle DBA


Oracle Certified 8i DBA



-----Original Message-----


Sent: Wednesday, October 22, 2003 4:20 PM
To: Multiple recipients of list ORACLE-L





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 <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


> is a no-go.

--
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-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).
--
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).


Received on Thu Oct 23 2003 - 08:09:25 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US