RE: Need to convert database characterset from WE8ISO8859P1 TO AL32UTF8
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <dubey.sandeep@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 26 Jan 2007 13:29:20 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On
Behalf Of Sandeep Dubey
Sent: Friday, January 26, 2007 12:53 PM
To: oracle-l@xxxxxxxxxxxxx
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
