13 Replies Latest reply: Mar 23, 2012 7:36 AM by Srini Chavali-Oracle RSS

    change NATIONAL CHARACTER SET

    user522961
      Hi,
      on 11G R2 we have a database on NATIONAL CHARACTER SET UTF8.
      Is there any way to change it to NATIONAL CHARACTER SET AL16UTF16 ?

      Thank you.
        • 1. Re: change NATIONAL CHARACTER SET
          Fran
          yes, you can. For example:
          ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;

          You can see a post with more information here:
          Change NLS_NCHAR_CHARACTERSET

          HTH



          Moderator Comment:

          INTERNAL_USE means "use restricted to Oracle". It means, do not use unless instructed by Oracle Support!

          -- Sergiusz
          • 2. Re: change NATIONAL CHARACTER SET
            878302
            To change the database character set, perform the following steps:

            Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.
            Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.
            Complete the following statements:
            STARTUP MOUNT;
            ALTER SYSTEM ENABLE RESTRICTED SESSION;
            ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
            ALTER SYSTEM SET AQ_TM_PROCESSES=0;
            ALTER DATABASE OPEN;
            ALTER DATABASE CHARACTER SET new_character_set;
            SHUTDOWN IMMEDIATE; -- or SHUTDOWN NORMAL;
            STARTUP;
            • 3. Re: change NATIONAL CHARACTER SET
              Lubiez Jean-Valentin
              Hello,


              I suggest you to read the following Notes of MOS before changing the National Character Set:

              - *The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g and 11g [ID 276914.1]*

              The point *17)* is interesting:
              17) How to go from an UTF8 NLS_NCHAR_CHARTERSET to AL16UTF16?
              Hope this help.
              Best regards,
              Jean-Valentin
              • 4. Re: change NATIONAL CHARACTER SET
                user522961
                thanks to all.
                It sounds magic. Let me try. I will inform you about the result.
                • 5. Re: change NATIONAL CHARACTER SET
                  Srini Chavali-Oracle
                  Do NOT use the methods indicated above - they are NOT supported and WILL corrupt your database beyond repair.

                  The correct and supported methods are documented

                  http://docs.oracle.com/cd/E11882_01/server.112/e10729/ch11charsetmig.htm#CEGDHJFF
                  http://www.oracle.com/technetwork/database/globalization/dmu/overview/index.html

                  The National Character Set ( NLS_NCHAR_CHARACTERSET ) in Oracle 9i, 10g and 11g [ID 276914.1]     


                  HTH
                  Srini
                  • 6. Re: change NATIONAL CHARACTER SET
                    user522961
                    Hi again,
                    unfortunately I receive :
                    SQL> ALTER DATABASE CHARACTER SET AL16UTF16;
                    ALTER DATABASE CHARACTER SET AL16UTF16
                    *
                    ORA-12712: new character set must be a superset of old character set.
                    Any idea ?
                    • 7. Re: change NATIONAL CHARACTER SET
                      Sergiusz Wolicki-Oracle
                      To change the national character set to AL16UTF16, you have to:

                      1. Export or unload content of all NCHAR and NVARCHAR2 columns.
                      2. Nullify or drop all NCHAR and NVARCHAR2 columns.
                      3. Run CSSCAN TONCHAR=AL16UTF16
                      4. Run CSALTER.PLB
                      5. Reload the saved content of NCHAR and NVARCHAR2 columns.

                      See documentation links mentioned above. Do not use ALTER DATABASE commands mentioned above. These commands are unsupported and may have disastrous effects if used incorrectly.

                      On the other hand, Oracle recommendation is that you create your database with the AL32UTF8 database character set, and move all your national character set data to normal VARCHAR2 and CHAR columns. Then, forget about the national character set.



                      -- Sergiusz
                      • 8. Re: change NATIONAL CHARACTER SET
                        Srini Chavali-Oracle
                        As I stated above, you cannot use the ALTER command to change either NATIONAL or regular CHARACTER SET - were my comments not clear enough ?

                        See the links Jean and I posted above on how to change the characterset.

                        Srini
                        • 9. Re: change NATIONAL CHARACTER SET
                          user522961
                          Thanks to all.
                          Syntax was bad. The correct one is :
                          ALTER DATABASE NATIONAL CHARACTER SET AL16UTF16

                          And it worked.
                          Srini, that note (276914.1) does not say that you can not use ALTER (or I did not see). It says if you have table columns bigger then 1000 CHAR or NVARCHAR2 column bigger then 2000 CHAR change them.

                          I had only two :
                          select distinct OWNER, TABLE_NAME, COLUMN_NAME, CHAR_LENGTH from DBA_TAB_COLUMNS where DATA_TYPE='NVARCHAR2' and CHAR_LENGTH > 200
                          
                          OWNER                          TABLE_NAME
                          ------------------------------ ------------------------------
                          COLUMN_NAME                    CHAR_LENGTH
                          ------------------------------ -----------
                          SYS                            DBA_COMMON_AUDIT_TRAIL
                          SQL_BIND                              4000
                          
                          SYS                            DBA_COMMON_AUDIT_TRAIL
                          SQL_TEXT                              4000
                          then as said in that note I ran \rdbms\admin\catfga.sql.
                          Thank to all.
                          • 10. Re: change NATIONAL CHARACTER SET
                            Sergiusz Wolicki-Oracle
                            ## Srini, that note (276914.1) does not say that you can not use ALTER (or I did not see).

                            No, it does not say it directly, but if you look into SQL Reference 11gR2, you will not find the command. It means, it is undocumented and thus unsupported. The note says:

                            "( using alter database is in a 10g system for the national characterset not really a problem, but csalter is the official way)". The note is correct here. You should use csalter.plb for a supported method, though ALTER DATABASE NATIONAL CHARACTER SET will (unofficially) work as well, provided you do not try any INTERNAL clauses.


                            -- Sergiusz
                            • 11. Re: change NATIONAL CHARACTER SET
                              user522961
                              thank you Sergiusz.

                              What is csalter.plb ?

                              Regards.
                              • 12. Re: change NATIONAL CHARACTER SET
                                Sergiusz Wolicki-Oracle
                                $ORACLE_HOME/rdbms/admin/csalter.plb, a script to run in SQL*Plus, connected as SYSDBA.



                                -- Sergiusz
                                • 13. Re: change NATIONAL CHARACTER SET
                                  Srini Chavali-Oracle
                                  user522961 wrote:
                                  thank you Sergiusz.

                                  What is csalter.plb ?

                                  Regards.
                                  Documented in the links I posted above ;-)

                                  Srini