RE: Change Character sets

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 <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
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: rquintin_at_vt.edu

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