Section C in metalink article 444701.1, in combination with the discussion at http://www.freelists.org/post/oracle-l/Replacement-of-US7ASCII-character-set-in-11g , helped me understand the problems I am having a lot more. ie. Why "fromchar=WE8ISO8859P1 tochar=WE8ISO8859P1" was causing lossy conversion errors even though that was the character set I was using. Probably whoever/whatever entered this data was using a different client that the DB is in.
I'm not to happy about the massive amount of work to correct such a problem, but at least I think I understand.
I think I've answered my own questions from my original post. But if anyone has any personal experience of any bright ideas I can use for my testing, I'd still be interested in hearing it.
I'm not sure why you are stressed out about this - export/import utilities automagically take care of any characterset conversions. Are you planning to use a different characterset for your 11gR2 database ?
It may be lack of understanding on my behalf. I thought there may be data I needed to manually change as it's not recored in the current character set correctly, I may be wrong.
Originally, I wanted the 11g DB to use the AL32UTF8 or a similar more 'advanced' character set than WE8ISO8859P1 so there would not be user-inputted characters that the character set didn't know.
I have found by opening the .err file from CSSCAN (from when I used "fromchar=WE8ISO8859P1 tochar=WE8ISO8859P1") in Notepad++ (using Windows-1252 char set) that 95% of the 'lossy conversions' are euro signs. So I believe using WE8MSWIN1252 as a character set would suffice.
I'm sure you know more than me about this, but what you said seems to contradict section C.2) of 444701.1. It reads:
Please let me know if I misunderstand.
So, correct me if I am wrong, I think the correct course of action is as follows?:
1)Create new 11gR2 DB with WE8MSWIN1252 char set. (Or AL32UTF8 might work too?)
2)Change the 8i database using csalter/Alter database character set.
3)Set NLS_LANG to WE8MSWIN1252 on exporting server, & export data using exp.
4)Set NLS_LANG to WE8MSWIN1252 on importing server, & import data using imp.
Unless this is wrong, I will test this within the next couple days to see where I get.
Thanks for your help,
Pl edit your reply to remove the contents of the MOS Doc - posting such content is a violation of your Support agreement :-)
I would suggest you try this -
1. Set the client NLS_LANG on the source database to match the source database characterset, then perform the export.
2. Set the client NLS_LANG on the target database to match the target database characterset, then perform the import -- and all will be well.
This process assumes that the target database characterset is the same, or a superset, of the source database characterset.
Oracle recommends the use of AL32UTF8 characerset - About Creating an Oracle Database