Hi - I am looking for a solution where I can store the Multi Byte Character's under the WE8ISO8859P1 Database.
Below are the DB NLS_PARAMETERS
NLS_CHARACTERSET = WE8ISO8859P1
NLS_NCHAR_CHARACTERSET = AL32UTF8
NLS_LENGTH_SEMANTICS = BYTE
Size of DB = 2 TB.
DB Version = 18.104.22.168
Currently there is a need to store the Chinese Characters under NAME and ADDRESS Columns only. Below are the description of the columns.
Column Name DataType
What are my option's over here without considering the Migration WE8ISO8859P1 DB to AL32UTF8 ?
1. Can I increase the size of the Column i.e make it n x 4. e.g NAME will be 480 Byte and ADDRESS will be 400 Byte.? What are pros and cons ?
2. Convert the existing Column from VARCHAR2 to NVARCHAR2 with the Same Size ? i.e NVARCHAR2(120 BYTE) ?
3. Add the extension to an table with new columns - NVARCHAR2. e.g NAME - NVARCHAR2(120 CHAR) and ADDRESS (100 - CHAR) ?
4. Database got Clobs,Blobs, Long etc. got Varied Data, Is it a good idea to Migrate to AL32UTF8 with Minimal Downtime ?
Please suggest the best alternatives. Thanks.
NLS_NCHAR_CHARACTERSET can either be AL16UTF16 or UTF8. So mostly your DB would have UTF8.
You can definitely insert Unicode characters into N-type columns. Size of the N-type column will depend on the characters you plan to store in them.
If you use N-types, do make sure you use the (N'...') syntax when coding it so that Literals are denoted as being in the national character set by prepending letter 'N'.
Although you can use them, N-types are not very well supported in 3the party client/programming environments, you may need to adapt a lot of code to use N-types properly and there are some limitations.
While at first using N-types for a (few) columns seems like a good idea to avoid the conversion of a whole database , in many cases the end conclusion is that changing the NLS_CHARACTERSET is simply the easiest and fastest way to support more languages in an Oracle database.
So, It depends on how much of your data will be unicode which you would store in N-type characters.
If you do have access to My Oracle Support you can check Note 276914.1 :The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g , 11g and 12c, For more details.
With respect to your Downtime, The actual conversion (CSALTER or in case using DMU) shouldn't take too much time, if you have run CSSCAN on your DB and made sure you have taken care of all your truncation, convertible and lossy data (if any).
It would be best for you to run CSSCAN initially to gauge how much convertible/lossy/truncation data you need to take care.
$ CSSCAN FROMCHAR=WE8ISO8859P1 TOCHAR=AL32UTF8 LOG=P1TOAl32UTF8 ARRAY=1000000 PROCESS=2 CAPTURE=Y FULL=Y
Pl clarify your question - WE8ISO8859P1 is a single-byte characterset - it cannot store multi-byte characters.
You cannot store Chinese characters using this characterset. What is the reason for not converting to AL32UTF8 ?
Thanks Suntrupth. I am going to run CSSCAN as a part of initial investigation and will take it from there. One more thing, I was exploring was to expand the size of column. Let's say name = varchar2(120 Byte) , I will expand name column size to Varchar2(480 Byte). Basically this will store English Character upto 480 characters and if it's 4 byte character set ( Japanese/Chinese), will hold up-to 120 byte ?
Will these solution feasible ? or One need to have AL32UTF8 to store the multi byte character set.
Thanks once again....
Srini - Thanks for the feedback.
To answer you question - What is the reason for not converting to AL32UTF8 ?
I haven't ran the CSSCAN as a part of initial investigation, so not sure how much is truncation, convertible and lossy data. Another thing is Database is real time and can't afford huge downtime. Before presenting the solution to Management for DB migration to new character set, I am evaluating the pros and cons of alternative.
Some recommendations from Oracle:
0. Do not store Chinese in WE8ISO8859P1!
1. Do not generally use NCHAR data types for reasons mentioned already by Suntrupth. Even the Oracle Database itself does not fully support them. For example, you cannot create an Oracle Text index on NCHAR columns. There are also serious limitations in XDB.
2. If the need to store non-English text is very limited, like just to two columns, and your test migration to AL32UTF8 shows that the downtime is not acceptable, you can consider using NCHAR-type columns. You need to confirm that your application can be modified to access NCHAR-type columns without data loss. Specific configuration/coding is usually required to assure this. You must also confirm that all RDBMS features that you want to use with the columns are supported with NCHAR data types.
3. Do not use CSSCAN/CSALTER with 22.214.171.124. Use Database Migration Assistant for Unicode (DMU).
4. For mission-critical environments, a possible (though not yet well tested) solution is to use Oracle Streams to minimize the downtime. The idea is to create a physical copy of the production database, set up Streams logical replication between original and the copy, stop the replication (changes accumulate in archive log files), migrate the copy to AL32UTF8, re-establish replication to catch up with the accumulated changes (Streams will convert the changes to AL32UTF8 before applying them), and once the copy catches up with production, fail over production to the copy.
It is always advised to change your DB characterset to AL32UTF8 as I see you would be planning for future insertion of Japanese / Chinese characters.
N-Type hasn't been designed for huge amount of unicode data insertions as stated it has several limitations.
Sergiusz has shown you couple more limitations which make N-types not the best fit.
Most applications do not work well with N-type columns.
Thanks to Sergiusz who has pointed out the Streams option.
If you do not have too much data that needs conversion, your best bet would be to move to AL32UTF8.
Basically this will store English Character upto 480 characters and if it's 4 byte character set ( Japanese/Chinese), will hold up-to 120 byte ?
---> You will not be able to store Japanese/chinese in a varchar2 column in a WE8ISO8859P1 DB.