修改Oracle数据库字符集

修改Oracle数据库字符集

前两天安装Oracle数据库时忘记设置字符集了。等安装完进行下一步测试时才发现字符集不对。

参考内容：

如何修改Oracle数据库字符集

http://www.linuxidc.com/Linux/2012-07/65501.htm




通常情况下,字符集是在安装的时候选定好的,需要修改数据库的字符集Oracle建议的做法是重建数据库(EXP/EXPDP导出后再导入),
虽然Oracle官方文档也有说如何修改字符集,但这不是Orale推荐的方法.下面是Oracle官方文档中提到的如何修改字符集的方法.
 
1.查看当前数据库字符集
select * from nls_database_parameters
where parameter='NLS_CHARACTERSET';
-----------------------------------
PARAMETER         VALUE
NLS_CHARACTERSET WE8ISO8859P1
 
这里的字符集WE8ISO8859P1是不支持中文的
 
2.将字符集修改为中文字符集ZHS16GBK
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.
Total System Global Area  734003200 bytes
Fixed Size                  1221564 bytes
Variable Size             264244292 bytes
Database Buffers          465567744 bytes
Redo Buffers                2969600 bytes
Database mounted.
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.
SQL> ALTER DATABASE OPEN;
Database altered.
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set


SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
Database altered.
SQL> SHUTDOWN IMMEDIATE;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  734003200 bytes
Fixed Size                  1221564 bytes
Variable Size             264244292 bytes
Database Buffers          465567744 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.
 
这样将数据库的字符集由原来的WE8ISO8859P1修改成了ZHS16GBK,对原来的字符集有中文的显示的还是乱码,但对新入库的中文字符集就能正常显示.
 
-- The End --

本人测试结果如下：

Linux 下修改Oracle 的字符集：WE8ISO8859P1 修改为 ZHS16GBK


#登录数据库
[oracle@localhost data]$ sqlplus /nolog


SQL*Plus: Release 10.2.0.1.0 - Production on Mon Sep 14 18:53:39 2015


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

SQL> startup;  
ORACLE instance started.


Total System Global Area  608174080 bytes
Fixed Size                  1220820 bytes
Variable Size             171970348 bytes
Database Buffers          427819008 bytes
Redo Buffers                7163904 bytes
Database mounted.
Database opened.

#当前字符集是WE8ISO8859P1

SQL> select * from nls_database_parameters；


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

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

#启动mount状态
SQL> startup mount;
ORACLE instance started.


Total System Global Area  608174080 bytes
Fixed Size                  1220820 bytes
Variable Size             171970348 bytes
Database Buffers          427819008 bytes
Redo Buffers                7163904 bytes
Database mounted.


SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;


System altered.


SQL> ALTER DATABASE OPEN;


Database altered.


SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set



#修改成功
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;


Database altered.


SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> startup
ORACLE instance started.


Total System Global Area  608174080 bytes
Fixed Size                  1220820 bytes
Variable Size             171970348 bytes
Database Buffers          427819008 bytes
Redo Buffers                7163904 bytes
Database mounted.
Database opened.


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


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


SQL> QUIT

---END

小结：

以后安装时最好先选好字符集，避免出现修改字符集的情况出现。

毕竟当导入数据后才发现字符集不对，后续操作很麻烦的。需要删除已导入的数据库，修改字符集后重新再导入。

在创建数据库的过程中，可以将数据库的字符集设置为Unicode UTF-8，防止出现乱码。