Oracle 修改字符集（AL32UTF8 转换成UTF8字符集）

1.首先检查环境

[84-32-2 ~]$ sqlplus  / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Jun 11 19:53:38 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

SQL> spool 2017_12_08.txt
SQL> set line 4000
SQL> select userenv(‘language‘) from dual;
USERENV(‘LANGUAGE‘)--------------------------------------------------------------------------------------------------------AMERICAN_AMERICA.AL32UTF8
 
SQL> select * from v$nls_parameters;
PARAMETER VALUE-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------NLS_LANGUAGE                                                                                                                     AMERICANNLS_TERRITORY                                                                                                                    AMERICANLS_CURRENCY                                                                                                                     $NLS_ISO_CURRENCY                                                                                                                 AMERICANLS_NUMERIC_CHARACTERS                                                                                                           .,NLS_CALENDAR                                                                                                                     GREGORIANNLS_DATE_FORMAT                                                                                                                  DD-MON-RRNLS_DATE_LANGUAGE                                                                                                                AMERICANNLS_CHARACTERSET                                                                                                                 AL32UTF8NLS_SORT                                                                                                                         BINARYNLS_TIME_FORMAT                                                                                                                  HH.MI.SSXFF AM
PARAMETER                                                                                                                        VALUE-------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------NLS_TIMESTAMP_FORMAT                                                                                                             DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT                                                                                                               HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT                                                                                                          DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY                                                                                                                $NLS_NCHAR_CHARACTERSET                                                                                                           AL16UTF16NLS_COMP                                                                                                                         BINARYNLS_LENGTH_SEMANTICS                                                                                                             BYTENLS_NCHAR_CONV_EXCP                                                                                                              FALSE
19 rows selected.
SQL> 







 
执行下面命令修改
 

SQL> shutdown immediate
数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startup mountORACLE 例程已经启动。
Total System Global Area 3373858816 bytesFixed Size                  2180424 bytesVariable Size            1946159800 bytesDatabase Buffers         1409286144 bytesRedo Buffers               16232448 bytes数据库装载完毕。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  utf8;
alter database character set  utf8
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set


SQL> alter database character set internal_use utf8;

Database altered.

SQL> alter database character set internal_convert utf8;

Database altered.

SQL> alter database character set internal_use utf8;

Database altered.

上面就是执行的时候容易遇到的问题，

SQL> alter database character set  utf8;

Database altered.

SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area 3373858816 bytes
Fixed Size                  2180424 bytes
Variable Size            1946159800 bytes
Database Buffers         1409286144 bytes
Redo Buffers               16232448 bytes
数据库装载完毕。
数据库已经打开。
SQL> select userenv(‘language‘) from dual;

USERENV(‘LANGUAGE‘)--------------------------------------------------------------------------------------------------------AMERICAN_AMERICA.UTF8







 
如何将Oracle的字符集由AMERICAN_AMERICA.AL32UTF8修改为SIMPLIFIED CHINESE_CHINA.AL32UTF8
参考：
版权声明：本文为CSDN博主「长林攻城狮」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/shao_yc/article/details/104524240
 
一直以为将Oracle的AMERICAN_AMERICA.AL32UTF8修改为SIMPLIFIED CHINESE_CHINA.AL32UTF8是Oracle客户端的事情，其实搞错了，我们该做的应该是去修改服务器端的环境变量的语言。
查看当前登录用户环境变量的语言
select userenv(‘language‘) from dual;

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

USERENV(‘LANGUAGE‘)--------------------------------------------------------------------------------------------------------AMERICAN_AMERICA.UTF8

SQL> exit

退出Oracle客户端，修改环境变量
vi .bash_profile

[84-32-2 ~]$ whoami
oracle
[-84-32-2 ~]$ vi .bash_profile添加以下语句
#export NLS_LANG=AMERICAN_AMERICA.ZHS16GBKexport NLS_LANG="SIMPLIFIED CHINESE_CHINA".AL32UTF8







输入命令使配置生效
source .bash_profile
重新登录Oracle客户端查看
select userenv(‘language‘) from dual;

[84-32-2 ~]$ sqlplus / as sysdba 

SQL*Plus: Release 11.2.0.4.0 Production on 星期四 6月 11 20:24:54 2020

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


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

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

USERENV(‘LANGUAGE‘)
--------------------------------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.UTF8

SQL>

需要补充说明的是：

UTF8和AL32UTF8为不同的字符集，在oracle的11.2版本中，UTF8已经不是推荐的一员了。详细说明可查看：https://blog.csdn.net/shao_yc/article/details/104524846

参考：

https://blog.csdn.net/shiyu1157758655/article/details/78748283

https://blog.csdn.net/u010457406/article/details/75646547