CODEWITHSUNDEEP
sqloracle-database
Pravin Satav
Pravin Satav

Reputation: 702

ascii to unicode Database changes

We are migrating legacy application ascii to unicode, as part of this activity we need to change datatypes from varchar2 to nvarchar2 so that it can accept different character. I was trying testing this activity for one the table and one column (using alter table script) and it took more than 15 mins as table is holding data (13,81,67,254 rows). There are around 200 tables(each table having around 25 columns having varchar2 datatype) present and to complete this activity it will take lot of time and we cant afford so much down time. Is there any way to perform this quicker? DB - Oracle 10 g

Editing Question - Is there any way we can avoid conversion of datatypes from varchar2 to nvarchar2? Curretnly varchar2 range defined in BYTES e.g. - varchar2(3 BYTE).

Database Details -

Upvotes: 1

Views: 765

Answers (2)

dan b
dan b

Reputation: 1172

I recommend using the latest Oracle Data Migration Assistant for Unicode (DMU). Here is a link:

http://www.oracle.com/technetwork/database/database-technologies/globalization/dmu/overview/index.html

But also it's good to have an understanding of Unicode (which isn't that complicated). Take a look at Wiki for a better understanding of Unicode.

I agree with those who have commented that it makes the most sense to use a default unicode based character set so that you can use varchar and you won't need nvarchar.

One issue with conversion is size. Suppose you have a declaration of varchar2(3). This declaration means you can store up to 3 bytes which for non-unicode equates to 3 characters because each character only takes up one byte. But in unicode a character can take up more then one byte. You could change the declaration to varchar2(3 CHAR) and then it would work for unicode. You could also change the default for the database for varchar2 to be CHAR rather then BYTE. In either case you still have the maximum bytes issue to contend with. The maximum number of bytes for varchar2 is 4000 (unless you are upgrade to Oracle 12c in which case you can change the maximum amount to 32767 bytes). In any case if you have a declaration of varchar2(4000 char) you may or may not be able to insert 4000 unicode characters. You can only insert that many if all the characters are represented with one byte in you unicode encoding (for example if they are ascii characters). In AL32UTF all characters take up 1-4 bytes. So that means if you use a declaration of varchar2(1000 CHAR) you are guaranteed to be able to have up to 1000 characters, even if all the characters have 4 byte encodings.

I also recommend changing the default nls_length_semantics to CHAR. That way varchar2(n) will mean up to n characters can be stored, irregardless of the number of bytes that the n characters require (as long as it is less then 4000 bytes).

Upvotes: 1

avk
avk

Reputation: 871

Try

alter table modify <column_name> varchar2(4 char)

This should be doable on a live system without anyone noticing. 4 char should be enough to store Unicode asian characters.

Upvotes: 1

Related Questions