8 Replies Latest reply on Aug 3, 2014 9:35 PM by rp0428

    How to convert database character set from WE8MSWIN1252 to UTF8?

    suresh a

      Hi Experts,

       

      How to convert database character set from WE8MSWIN1252 to UTF8 using exports and imports?

      Database version is 11.2.0.3 and O.S is O.E.L 5.8.

      Please give your expert advise for me which will be help to troubleshoot  my problems.

       

       

      Regards,

      Suresh.

        • 1. Re: How to convert database character set from WE8MSWIN1252 to UTF8?

          suresh adabala wrote:

           

          Hi Experts,

           

          How to convert database character set from WE8MSWIN1252 to UTF8 using exports and imports?

          Database version is 11.2.0.3 and O.S is O.E.L 5.8.

          Please give your expert advise for me which will be help to troubleshoot  my problems.

           

           

          Regards,

          Suresh.

          expdp

          use dbca to create new database with UTF8

          impdp

          • 2. Re: How to convert database character set from WE8MSWIN1252 to UTF8?
            Girish Sharma

            Suresh,

             

            First can you tell us why you are going to change the character set of database, is there any specific need or any technical issue ?

            Changing of character set by export/import is simple and straight forward, but it may be cause of loss/corruption in data.  Before going ahead and ensure that there will not be any data loss, Oracle has given us csscan utility.

             

            Csscan utility help us to identify if data is Changeless (Data to be stored in same way in new Characterset), Convertible (Data have different code points in new Characterset and needs to be converted), Truncation (Data will be truncated in new Characterset , so column needs to be modified) and Lossy (Data is not understood by new characterset and will be lost on Conversion).

             

            In case only Changeless and Convertible data is there, you can go ahead and use export/import.

             

            If only changeless data is there, then you need to simply use “Alter database ” statement or csalter (in 10g) to change the characterset.

             

            In case you have lossy data, then you would be required to identify what the data is, correct it and then change the characterset.

             

            Regards

            Girish Sharma

            • 3. Re: How to convert database character set from WE8MSWIN1252 to UTF8?
              A.Ebenezer

              Hi,

              Oracle recommends migrating database to Unicode (UTF8) through DMU utility (Database Migration Assistant for Unicode). It provides inline conversion and offers GUI to visualize how the steps are progressing and steps for cleansing data. Refer below link for further reference.

              http://docs.oracle.com/cd/E51608_01/doc/doc.20/e48475/ch1overview.htm#DUMAG101

              • 4. Re: How to convert database character set from WE8MSWIN1252 to UTF8?
                suresh a

                Hi Girish Sharma,

                I already run csscan utility. I got three files like .err,.out,.err files.

                Some part of the .txt file is like below

                 

                [Data Dictionary Conversion Summary]

                Data Dictionary Tables:

                Datatype                    Changeless      Convertible       Truncation            Lossy

                --------------------- ---------------- ---------------- ---------------- ----------------

                VARCHAR2                     3,788,758           0                0                    0

                CHAR                             2,864                0                 0                   0

                LONG                           250,936                0                0                   0

                CLOB                            50,801            2,545                0                  0

                VARRAY                          50,812                0                0                 0

                --------------------- ---------------- ---------------- ---------------- ----------------

                Total                        4,144,171            2,545                0                0

                Total in percentage             99.939%           0.061%           0.000%           0.000%

                The data dictionary can be safely migrated using the CSALTER script

                Data Dictionary Tables:

                USER.TABLE                                              Convertible       Truncation            Lossy

                -------------------------------------------------- ---------------- ---------------- ----------------

                MDSYS.SDO_COORD_OP_PARAM_VALS             200                0                0

                MDSYS.SDO_GEOR_XMLSCHEMA_TABLE             1                0                0

                MDSYS.SDO_STYLES_TABLE                                78                0                0

                MDSYS.SDO_XML_SCHEMAS                                5                0                0

                SYS.EXTERNAL_TAB$                                             1                0                0

                 

                 

                 

                Please briefly explain this one.

                 

                Regards,

                Suresh.

                • 5. Re: How to convert database character set from WE8MSWIN1252 to UTF8?
                  top.gun

                  Easy:

                  1) Export using datapump

                  2) create a fresh database that uses the new characterset.

                  3) Import using datapump

                   

                  Note - Oracle recommends the AL32UTF8 character set instead of UTF8.

                  • 6. Re: How to convert database character set from WE8MSWIN1252 to UTF8?
                    suresh a

                    Hi,

                     

                    Thank you for your response.

                    Any extra keywords are needed while doing export and import(for character set conversion)?

                    Is there any data loss in this method?

                    Please explain briefly.

                     

                     

                    Regards,

                    Suresh.

                    • 7. Re: How to convert database character set from WE8MSWIN1252 to UTF8?
                      Zoltan Kecskemethy

                      If you have MOS access there are plenty of docs out there in this topic let me list some:

                       

                      Changing the Database Character Set - a short overview.   Note:225912.1

                      https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=225912.1

                       

                      Changing the NLS_CHARACTERSET to AL32UTF8 / UTF8 (Unicode) Doc ID: 260192.1

                      https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=260192.1

                       

                      AL32UTF8 / UTF8 (Unicode) Database Character Set Implications Doc ID: 788156.1

                      https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=788156.1

                       

                      Csscan output explained Doc ID: 444701.1 (this one is pretty good for you last question)

                      https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=444701.1

                       

                      Solving Convertible or Lossy data in Data Dictionary objects when changing the NLS_CHARACTERSET Doc ID: 258904.1

                      https://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=258904.1

                       

                      Why do you want to hassle with manual conversion (csscan, exp-imp etc) when using DMU could save you a lot of time and effort?

                       

                      BTW there are two forums covering these kind of issues better (you should know of)

                      Globalization Support

                      Database Migration Assistant for Unicode

                       

                      HTH, Zoltan

                      • 8. Re: How to convert database character set from WE8MSWIN1252 to UTF8?
                        rp0428
                        Please briefly explain this one.

                        You first - please briefly explain the answer to the question Girish ask you:

                        First can you tell us why you are going to change the character set of database, is there any specific need or any technical issue ?

                        Post your justification for changing the character set of the database.

                         

                        Have you actually test your applications with the new character set to identify any issues or problems? If not, why not?

                         

                        Are there any external processes that manipulate the database data? You have a lot of CLOBs - how does that data get into the database? Are you using any client apps such as Java/JDBC? What is the character set used on those clients? The driver may have to perform conversions from client character set to server character set for strings and clob data.