Oracle修改字符集

修改 AL32UTF8 > ZHS16GBK
查看字符集

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.AL32UTF8

SQL>  select * from V$NLS_PARAMETERS;

PARAMETER                      VALUE                              CON_ID
------------------------------ ------------------------------ ----------
NLS_LANGUAGE                   AMERICAN                                0
NLS_TERRITORY                  AMERICA                                 0
NLS_CURRENCY                   $                                       0
NLS_ISO_CURRENCY               AMERICA                                 0
NLS_NUMERIC_CHARACTERS         .,                                      0
NLS_CALENDAR                   GREGORIAN                               0
NLS_DATE_FORMAT                DD-MON-RR                               0
NLS_DATE_LANGUAGE              AMERICAN                                0
**NLS_CHARACTERSET               AL32UTF8                                0** 
NLS_SORT                       BINARY                                  0
NLS_TIME_FORMAT                HH.MI.SSXFF AM                          0

PARAMETER                      VALUE                              CON_ID
------------------------------ ------------------------------ ----------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM                0
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR                      0
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR            0
NLS_DUAL_CURRENCY              $                                       0
NLS_NCHAR_CHARACTERSET         AL16UTF16                               0
NLS_COMP                       BINARY                                  0
NLS_LENGTH_SEMANTICS           BYTE                                    0
NLS_NCHAR_CONV_EXCP            FALSE                                   0

修改 NLS_CHARACTERSET 字符集

NLS_CHARACTERSET               AL32UTF8 > ZHS16GBK

关库启动到mount阶段

SQL> startup mount
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

开库修改字符集

SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;（AL32UTF8）
修改报错，使用INTERNAL_USE 参数可以跳过超集的检查
SQL> ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;
Database altered.

确认

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK