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
相关推荐
oraclemch 2020-11-06
Seandba 2020-08-16
dbasunny 2020-08-16
娜娜 2020-06-22
lklong 2020-11-22
shilukun 2020-10-10
周嘉笙 2020-11-09
iilegend 2020-10-19
EricRay 2020-10-16
zhuzhufxz 2020-09-16
dataminer 2020-08-17
bfcady 2020-08-16
Hody 2020-08-16
FightFourEggs 2020-08-16
数据库设计 2020-08-16
Omega 2020-08-16
zjyzz 2020-08-16
yanghuatong 2020-08-16