Converting from WE8MSWIN1252 to WE8ISO8859P1
Adam McDonagh
Is there anyway to change the char set on Oracle 10g from WE8MSWIN1252 back
to WE8ISO8859P1? I accidently changed from WE8ISO8859P1 to the wrong char
set, and now I'm not able to get it back using the ALTER DATABASE command as
it is not a superset... Is there any other way? Or am I going to have to
reinstall Oracle?
Thanks
Adamski
Marc Blum
wrote:
>Is there anyway to change the char set on Oracle 10g from WE8MSWIN1252 back
>to WE8ISO8859P1?
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8ISO8859P1;
this will skip the superset check
--
Marc Blum
mailto:blumN...@marcblum.de
http://www.marcblum.de
Sybrand Bakker
<ad...@ski2000.fsnet.co.uk> wrote:
> Or am I going to have to
>reinstall Oracle?
Of course not.
Export the affected database, create a new database with the correct
characterset, import the database.
Note: the 'hack' Marc Blum published will most likely not be supported
by OTS, so I strongly advise against doing that.
--
Sybrand Bakker, Senior Oracle DBA
Marc Blum
Dear Sybrand,
anyone with access to metalink will be able to find several documents describing
this command. So there's nothing wrong with repeating it here.
Marc Blum
<gooid...@sybrandb.verwijderdit.demon.nl> wrote:
>On Sat, 17 Apr 2004 12:40:04 +0100, "Adam McDonagh"
><ad...@ski2000.fsnet.co.uk> wrote:
>
>> Or am I going to have to
>>reinstall Oracle?
>
>Of course not.
>Export the affected database, create a new database with the correct
>characterset, import the database.
BTW: your advice surely does no good. As long as the DATABASE CHARACTERSET isn't
set to the correct value, any export will corrupt your data. Before doing any
export, you *have to* change the DATABASE CHARACTERSET to the right value, which
depends of the contents of your database. One example: client OS is XP/german,
NLS_LANG is <bla>.US7ASCII, DATABASE CHARACTERSET is US7ASCII => all settings
are wrong, but it works and the contents of the database is WE8MSWIN1252. An
export *will* corrupt your data, because a wrong characterset is documented in
the exportfile. Before doing an export you *have to* change the DATABASE
CHARACTERSET.
For further readings please refer to metalink.
Adam McDonagh
I managed to get my character set back to what it should be.
My problem is basically this...
I have a Win2k Pro server running Oracle 9i from which a database is
currently stored. I wish to transfer everything from this server onto a new
Linux PC that I have, running 10g.
The charset on Windows database is WE8MSWIN1252, and WE8ISO8859P1 on my
Linux box.
I have exported the database from the windows server to import onto my Linux
box. This all imports fine, however all the pound signs (£) are replaced
with hashes (#).
I have tried changing the Linux box to the MSWIN charset, but this didn't
work... What would be the correct procedure for me to be able to keep all
the poundsigns in my database? Do I need to change the windows box to the
ISO character set and then run an export... But if I do this, will the
poundsigns on the windows box still be ok?
Thanks for your patience
Adamski
Anurag Varma
"Adam McDonagh" <ad...@ski2000.fsnet.co.uk> wrote in message
news:408186e2$0$25243$cc9e...@news-text.dial.pipex.com...
Did you set your NLS_LANG correctly?
It should have been set to something like: American_America.WE8MSWIN1252 both on exp and imp.
Anurag
Marc Blum
wrote:
>I have a Win2k Pro server running Oracle 9i from which a database is
>currently stored. I wish to transfer everything from this server onto a new
>Linux PC that I have, running 10g.
>
>The charset on Windows database is WE8MSWIN1252, and WE8ISO8859P1 on my
>Linux box.
>
>I have exported the database from the windows server to import onto my Linux
>box. This all imports fine, however all the pound signs (£) are replaced
>with hashes (#).
>
>I have tried changing the Linux box to the MSWIN charset, but this didn't
>work... What would be the correct procedure for me to be able to keep all
>the poundsigns in my database? Do I need to change the windows box to the
>ISO character set and then run an export... But if I do this, will the
>poundsigns on the windows box still be ok?
One of the least understood concepts when dealing with NLS_LANG and db-charset
is:
The DATABASE CHARACTERSET is dictated by the codepage used by the client OS! It
must be a logical superset of the client characterset.
and
The codepage of the server OS is of no interest, as long as the server machine
isn't used for client apps.
For NLS_LANG keep in mind:
NLS_LANG is dictated by the setting of your client OS! You can't toggle
NLS_LANG. You have no choice! For example: my PC runs XP with german settings.
Therefore NLS_LANG must be GERMAN_GERMANY.WE8MSWIN1252. Nothing other!
In your case:
the migration from a W2k box to a Linux box doesn't affect the database
characterset used. Go this way:
Settings:
Check the client app OS for correct setting of NLS_LANG. For windows apps in UK
it's <bla>.WE8MSWIN1252.
Check the database characterset to be a superset of all used client
charactersets. If is was 1252 from beginning on, it's ok, don't touch it.
Migration:
Check the NLS_LANG of the w2k server box. exp.exe is now a client app. NLS_LANG
on the w2k server box must be <bla>.WE8MSWIN1252. Do the export.
Build a new database on the linux box. The database characterset must be a
superset of all client charactersets. When you're only dealing with windows
apps, 1252 is ok. Check the NLS_LANG of the linux box. imp.exe is now a client
app. NLS_LANG will be <bla>.WE8ISO8859P15. Transfer the dump to the linux box.
Now do the import. Alternatively you can do the import remotely from the w2k
server. Then you may ignore the NLS_LANG of the linux box.
hth
Sybrand Bakker
wrote:
>NLS_LANG is dictated by the setting of your client OS! You can't toggle
>NLS_LANG. You have no choice! For example: my PC runs XP with german settings.
>Therefore NLS_LANG must be GERMAN_GERMANY.WE8MSWIN1252. Nothing other!
I don't think that is true. I've seen it many times set on Dutch
systems to American_America.<etc> without problem.
Marc Blum
Dear Sybrand,
as long as all client machines have the same NLS_LANG setting and the database
has that characterset (the part after the dot) as DATABASE CHARACTERSET, you
have a *conceptually* wrong configuration, which works without problems,
because:
if and only if client charset and db charset are identical, the oracle client
layer does not perform *any* characterset conversion. Not even a one-on-one
mapping to ensure, that unknown codepoints are omitted. That way unknown
codepoints are stored and retrieved without corruption. You may choose US7ASCII
for both sides, as long as they are identical, it doesn't matter in any way.
Me too, I've seen many many oracle systems with WE8ISO8859P1 on both sides. When
the client app is windows based, it's wrong wrong wrong. The only correct
NLS_LANG-setting for german/dutch/british Windows is <bla>.WE8MSWIN1252.
Please check these metalink docs for clarification:
158577.1
179133.1
225938.1