oracle 修改字符编码

大家好，欢迎来到IT知识分享网。

1. 描述问题

在安装oracle时设置的编码是ZHS16GBK , 安装完成之后，创建表时，发现COMMENT字段的信息都是乱码。这时需要修改oracle的字符编码

2. 解决问题

第一步： 查看oracle的字符编码

SQL>  select * from v$nls_parameters;

PARAMETER                     VALUE
------------------------ -----------------------------
NLS_LANGUAGE                SIMPLIFIED CHINESE
NLS_TERRITORY                CHINA
NLS_CURRENCY                RMB
NLS_ISO_CURRENCY            CHINA
NLS_NUMERIC_CHARACTERS      .,
NLS_CALENDAR                GREGORIAN
NLS_DATE_FORMAT             DD-MON-RR
NLS_DATE_LANGUAGE           SIMPLIFIED CHINESE
NLS_CHARACTERSET            ZHS16GBK
NLS_SORT                       BINARY
NLS_TIME_FORMAT              HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT        DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT            HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT     DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY            RMB
NLS_NCHAR_CHARACTERSET      AL16UTF16
NLS_COMP                        BINARY
NLS_LENGTH_SEMANTICS         BYTE
NLS_NCHAR_CONV_EXCP          FALSE

经过查新发现 NLS_CHARACTERSET ZHS16GBK 需要修改为AL32UTF8编码

第二步： 修改编码
按照如下步骤以此执行

[root@HM311~]# sqlplus /nolog 
SQL> connect sys/oracle as sysdba
SQL> startup
SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes=0;
SQL> alter database open;
SQL> alter database character set internal_use AL32UTF8;
SQL> shutdown immediate;
SQL> startup

执行完成之后再次 select * from v$nls_parameters;发现编码已经修改