This is a follow-up question to a previous thread I had posted. We're in the process of migrating to a new character set. Now, I am a programmer and we have DBA's who will be performing the work but I'd like to get a little bit more information about the process. Our current character set is WE8ISO8859P1 and uses BYTE character semantics. However, we are migrating to AL32UTF8. A new database was created containing the AL32UTF8 character set using BYTE semantics. The DBA ran a script on some tables that checked to see if there would be any issues (I believe it was CSALTER). After running the script, it said that some of the rows in our table would be truncated. That table contains a VARCHAR2(BYTE) column that's set to 4000 BYTES. The rows that resulted with potential issues all contain special characters. My question is, how can we export/import the tables in our database without having information truncated? Why is it saying that it would be truncated? My first thought was that since we changed the character set, it may be storing some of the characters from the old database table different than when it's stored into the new database with the character set using up more storage.
The reason for the truncation is quite simple. Some characters (accented ones, for example) in the WE8ISO8859P1 character set are stored as a single byte, but in AL32UTF8 they end up being stored as multiple bytes. As a result of conversion, a 4000 character string may end up actually requiring more than 4000 bytes.
By way of example, this query shows you that the Euro symbol (0x80 in WE8ISO8859P1) becomes 2 bytes in AL32UTF8:
To list all characters that will be affected by the change, you can use the following query:
Unfortunately the maximum length a
You could first create empty tables by importing only metadata (data pump can do that). Then you can run a script than changes BYTE columns to CHAR before importing data. This way data will not be truncated since effective maximum length will be the same as in old database.