RE: Need to convert database characterset from WE8ISO8859P1 TO AL32UTF8

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 26 Jan 2007 13:29:20 -0500
Message-ID: <FBEIIHEAOIFCBBNIIFOGGELACLAA.mwf@rsiz.com>

When the columns requiring any change at all are in a very small number of tables, that provides the option of comprehensive testing on just those tables (especially manageable if the total size of the objects is amenable to copying the relevant objects so you can review the results of testing from the easiest way to more difficult ways.

The csscan as I see the partial output below seems safe. (That doesn't mean you should skip testing...).

Without rechecking the documentation, I believe "convertible" means that the expressed length in the new character set will fit in the column as currently defined, while "truncation" means you would need to make the column wider before you convert to prevent truncation, while "lossy" means you have values in your current character set that cannot be expressed in the new character set. I'm confident someone on the list will object if I mis-remember or something has changed. I'm not aware of any characters in WE8ISO8859P1 that cannot be represented in AL32UTF8, but that is also pretty easy to test except for the field separator character and the actual null characters which may find difficult to insert into character (CHAR, VARCHAR2, LONG, CLOB) columns for testing.

Let's say you have a small number of tables that contain trouble. If you can take some small downtime and csalter won't handle all of them, you might find success by exporting the offenders, importing them into a correctly set character set new little database, then testing them by importing them into a converted clone of your existing database under a new schema so you can do exhaustive comparison of any differences easily on a row by row basis. Then if you are happy, for the real conversion you export those objects, rename or drop the originals, convert, and import. Being very safe can be tedious and boring, but then I've always preferred to be boring. BORING is good.

Regards,

mwf

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Sandeep Dubey
Sent: Friday, January 26, 2007 12:53 PM
To: oracle-l_at_freelists.org
Subject: Need to convert database characterset from WE8ISO8859P1 TO AL32UTF8

Hi all

Platofrm: Oracle 10.2 on Linux 2.4

I need to convert database characterset from WE8ISO8859P1 TO AL32UTF8. Following is a part of csscan output. Some application data is convertible. I have blob data in the application. It is nowhere reported whether it is covertinble or changless in the csscan output scan.txt file.

Can I safely convert the database using csalter script? If yes, what will happen to application data in Convertible column?

Or I must use export import? Problem is our production database export and import will take over 24 hours and we can not take that much down time.

partial output from scan.txt file----------

The data dictionary can be safely migrated using the CSALTER script

[Application Data Conversion Summary]

Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ----------------

---------------- VARCHAR2 3,059,688 7,312 0 0 CHAR 244,835 0 0 0 LONG 0 0 0 0 CLOB 0 0 0 0 VARRAY 0 0 0 0
--------------------- ---------------- ----------------
---------------- Total 3,304,523 7,312 0 0 Total in percentage 99.779% 0.221% 0.000% 0.000%

[Distribution of Convertible, Truncated and Lossy Data by Table]

USER.TABLE                                 Convertible       Truncation

Lossy
-------------------------------------------------- ----------------

---------------- APMS_MASTER.CUSTODIAN_INFOS 3 0 0 APMS_MASTER.DRUG_DESCRIPTIONS 1,270 0 0 ...............

Thanks

Sandeep
--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Fri Jan 26 2007 - 12:29:20 CST