12 Replies Latest reply: Mar 28, 2012 9:09 PM by 920348 RSS

    Character set change? Help!

    920348
      Hi,

      I am doing imports and the export client uses ZHS16GBK character set (simplified chinese) and my imports are done in
      US7ASCII which is not a super set of the aforementioned character set. I want to change to ZHS32GB18030 which is a super set
      of US7ASCII and ZHS16GBK. Is it possible to change my character set in sql plus?

      Also could this character conversion cause an IMP-00008 error (unrecognized statement in export file)?


      -Ashley
        • 1. Re: Character set change? Help!
          Srini Chavali-Oracle
          Pl post details of OS and database versions. Methods to change characterset of the database are documented - http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch11charsetmig.htm

          HTH
          Srini
          • 2. Re: Character set change? Help!
            920348
            I'm using Windows 7 and Oracle 10g release 2. I don't know what OS the export client is using but i'm pretty sure they are using Oracle 10g.

            I've seen the page that you have attached but I am a little confused by all the information, I'm pretty new to Oracle and really only know the basics. I don't understand how to actually do the change in sql plus. Can you give me any tips?


            Thanks,
            Ashley
            • 3. Re: Character set change? Help!
              Srini Chavali-Oracle
              You cannot change the characterset using sqlplus. The process to migrate to a new characterset is not trivial - the steps are documented, as I noted above. The easiest way to do so would be to perform a full database export, drop the database, re-create the database with the needed characterset, then perform a full import. How big is the database ? How much downtime can you afford ?

              http://docs.oracle.com/cd/B19306_01/server.102/b14225/ch11charsetmig.htm

              HTH
              Srini
              • 4. Re: Character set change? Help!
                920348
                The file I am importing is a couple GB. Are you aware of any errors that arise from not having a suitable character set? The only reason i'm looking into changing the character set is because I have a few errors that I can't figure out and this is something I would try to fix it.

                Some errors I have:
                ORA 942: table or view does not exit
                IMP-00008: unrecognized statement in export file

                Thank you for your help so far!

                Edited by: 917345 on Mar 27, 2012 11:04 AM
                • 5. Re: Character set change? Help!
                  Srini Chavali-Oracle
                  917345 wrote:
                  The file I am importing is a couple GB. Are you aware of any errors that arise from not having a suitable character set? The only reason i'm looking into changing the character set is because I have a few errors that I can't figure out and this is something I would try to fix it.

                  Some errors I have:
                  ORA 942: table or view does not exit
                  IMP-00008: unrecognized statement in export file
                  AFAIK, these errors are not related to characterset issues. Pl post the contents of the import log file where these errors occur.
                  Thank you for your help so far!

                  Edited by: 917345 on Mar 27, 2012 11:04 AM
                  HTH
                  Srini
                  • 6. Re: Character set change? Help!
                    920348
                    This one my logs. It has the error ORA 942, this is an error that I have found in most of my files that I am importing:

                    Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
                    With the Partitioning, OLAP, Data Mining and Real Application Testing options

                    Export file created by EXPORT:V10.01.00 via conventional path

                    Warning: the objects were exported by SMARTGPS2006, not by you

                    import done in US7ASCII character set and AL16UTF16 NCHAR character set
                    import server uses AL32UTF8 character set (possible charset conversion)
                    export client uses ZHS16GBK character set (possible charset conversion)
                    export server uses UTF8 NCHAR character set (possible ncharset conversion)
                    . importing SMARTGPS2006's objects into RPI
                    . . importing partition "MCC_ASYN_POS":"MCC_ASYN_POS200706" 0 rows imported
                    . . importing partition "MCC_ASYN_POS":"MCC_ASYN_POS200802" 0 rows imported
                    . . importing partition "MCC_ASYN_POS":"MCC_ASYN_POS200803" 0 rows imported
                    . . importing partition "MCC_ASYN_POS":"MCC_ASYN_POS200804" 0 rows imported
                    . . importing partition "MCC_ASYN_POS":"MCC_ASYN_POS200903" 22939070 rows imported
                    . . importing partition "MCC_ASYN_POS":"MCC_ASYN_POS200904" 0 rows imported
                    . . importing partition "MCC_ASYN_POS":"MCC_ASYN_POS200905" 0 rows imported
                    . . importing partition "MCC_ASYN_POS":"MCC_ASYN_POS200906" 0 rows imported
                    . . importing partition "MCC_ASYN_POS":"MCC_ASYN_POS200907" 0 rows imported
                    IMP-00017: following statement failed with ORACLE error 942:
                    "ALTER TABLE "MCC_ASYN_POS" ADD CONSTRAINT "MCC_ASYN_POS_OEMCODE_NEW" FOREIG"
                    "N KEY ("OEMCODE") REFERENCES "OEM" ("OEMCODE") ENABLE NOVALIDATE"
                    IMP-00003: ORACLE error 942 encountered
                    ORA-00942: table or view does not exist
                    About to enable constraints...
                    Import terminated successfully with warnings.

                    From doing research I have found that IMP-00008 is because the file is corrupt. Is this always the case?
                    Also, you said there errors would not be because of a character set conversion issue, does that mean I do need to change my character set?

                    Ashley
                    • 7. Re: Character set change? Help!
                      Srini Chavali-Oracle
                      Pl post the characterset of your database - select * from NLS_DATABASE_PARAMETERS.

                      The ORA-00942 error refers to the referential integrity constraint - does the table OEM exist on the target database ?

                      The IMP-00008 error is most likely due to a corrupted dmp file, as you have indicated.

                      HTH
                      Srini
                      • 8. Re: Character set change? Help!
                        920348
                        My language is American and the territory is America.
                        I have not been able to find OEM, i've tried select * from OEM and I get the same ORA 942
                        Is there a way to fix corrupt files?

                        Ashley
                        • 9. Re: Character set change? Help!
                          Sergiusz Wolicki-Oracle
                          In addition to answers that others have given, please, do not use ZHS32GB18030 as the database character set. It is not currently supported as the database character set. ZHS16GBK is a superset of US7ASCII and would be good enough. Moreover, your database is already AL32UTF8, which is a superset of ZHS16GBK anyway.

                          The reported issues should not normally be caused by the database character set. But one thing that you should definitely try is to set NLS_LANG to .ZHS16GBK before running Import.

                          Also, I do not know about any way to fix a corrupted dump file. You should first review My Oracle Support notes for known issues with IMP-00008. I remember there were bugs causing such corruption. Your export file was created with the pretty outdated Oracle 10.1. Only after finding the probable cause of the corruption and installing relevant patches or workarounds, you should rerun export and/or import, depending on when the bug is.


                          -- Sergiusz
                          • 10. Re: Character set change? Help!
                            920348
                            Okay so can I change the NLS LANG in sql plus?

                            Thank you for your help!

                            Ashley
                            • 11. Re: Character set change? Help!
                              orafad
                              No. If you are on Windows then set it right before running the import.

                              c:\> set nls_lang=.ZHS16GBK

                              c:\> imp ...
                              • 12. Re: Character set change? Help!
                                920348
                                Thank you!

                                Ashley