Oracle字符集

accttodo 8/15/2022 后端数据库Oracle

# 一、什么是Oracle字符集

Oracle字符集是一个字节数据的解释的符号集合，有大小之分，有相互的包容关系。

Oracle支持国家语言的体系结构允许使用本地化语言来存储，处理，检索数据。它使数据库工具，错误消息，排序次序，日期，时间，货币，数字，和日历自动适应本地化语言和平台。

影响Oracle数据库字符集最重要的参数是NLS_LANG。

格式如下：NLS_LANG = LANGUAGE_TERRITORY.CHARSET

由三个部分组成：语言LANGUAGE 、地域TERRITORY、字符集CHARSET，每个部分控制了NLS子集的特性。

LANGUAGE 指定服务器消息的语言。
TERRITORY 指定服务器的日期和数字格式。
CHARSET 指定字符集。例如：AMERICAN _ AMERICA. UTF8。

从NLS_LANG的组成可以看出，真正影响数据库字符集的是CHARSET部分。所以两个数据库之间的字符集只要CHARSET部分一样就可以相互导入导出数据，前面部分影响的只是提示信息是中文还是英文。

# 二、如何查询Oracle的字符集

因为字符集不同而使数据导入失败的问题涉及三方面的字符集：

Oracle Server端的字符集。
Oracle Client端的字符集。
Oracle DMP文件的字符集。

数据导入的，需要这三个字符集都一致导入后才会不出现乱码。

# 查询Oracle Server端的字符集

select userenv('language') from dual;

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

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

1
2
3
4
5

# 查询Oracle Client端的字符集

在Linux/UNIX平台下，就是环境变量NLS_LANG。
在windows平台下，注册表regedit里面的HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME0\NLS_LANG。

# 查询Oracle DMP文件的字符集

用Oracle的exp工具导出的DMP文件包含了字符集信息。

如果DMP文件不大，可以用编辑器以16进制方式打开，第2第3个字节的内容即为字符集信息编码，如0354。

如果DMP文件很大，可以在unix主机上用以下命令查询字符集信息编码，如0354。

cat xxx.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6;

$ cat tts_new2.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6;

0345

1
2
3

然后用以下SQL查出字符集信息编码对应的字符集信息：

select nls_charset_name(to_number('0354'，'xxxx')) from dual;

SQL> select nls_charset_name(to_number('0354'，'xxxx')) from dual;

ZHS16GBK

1
2
3

# 三、修改Oracle的字符集

Oracle的字符集有互相的包容关系。例如：US7ASCII是ZHS16GBK的子集，从US7ASCII到ZHS16GBK不会有数据解释上的问题，不会有数据丢失。在所有的字符集中UTF8应该是最大，因为它基于UNICODE，双字节保存字符，也因此在存储空间上占用更多。

数据库创建后，数据库的字符集理论上讲是不能改变的。根据Oracle的官方说明，字符集的转换是从子集到超集，反之不行。如果两种字符集之间根本没有子集和超集的关系，那么字符集的转换是不受Oracle支持的。在修改之前一定要确认两种字符集是否存在子集和超集的关系。一般来说，除非万不得已，不建议修改Oracle数据库Server端的字符集。

特别说明，最常用的两种字符集ZHS16CGB231280和ZHS16GBK之间不存在子集和超集关系，因此理论上讲这两种字符集之间的相互转换不受支持。

# 修改Oracle Server端字符集(不建议使用)

Oracle 8之前，可以用直接修改数据字典表props$来改变数据库的字符集。

Oracle 8之后，只改props$表并不完全，可能引起严重的后果。正确的修改方法如下:

sqlplus /as sysdba

先执行SHUTDOWN IMMEDIATE命令关闭数据库服务器，

然后执行以下命令:

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 INTERNAL_USE UTF8; //跳过超子集检测
SQL>ALTER DATABASE national CHARACTER SET INTERNAL UTF8;

1
2
3
4
5
6
7

这一行不起作用，执行后出错ORA-00933: SQL 命令未正确结束，不过执行上一行命令已经生效，其他文章里未提到本行。

SQL>SHUTDOWN IMMEDIATE;

SQL>STARTUP;

-- 以管理员身份登录sqlplus
sqlplus / as sysdba;

-- 关闭数据库
shutdown immediate;

-- 将数据库启动到mout模式
startup mount

-- 数据库受限模式，在这个模式下只有RESTRICTED SESSION 权限的人才可以登陆，一般用与数据库维护的时候使用。
ALTER SYSTEM ENABLE RESTRICTED SESSION;
-- 让JOB没有调度进程，不会run。防止有任务自动启动执行 （可以不设置）
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
-- 关闭队列监视器（可以不设置）
ALTER SYSTEM SET AQ_TM_PROCESSES=0;

-- 将数据库启动到open模式
alter database open;

-- 修改字符集（新字符集必须为旧字符集的超集，这时我们可以跳过超集的检查做更改）
ALTER DATABASE character set INTERNAL_USE ZHS16GBK;

-- 查看字符集
select * from v$nls_parameters;

-- 重启再查看
shutdown immediate;
startup

-- 修改客户端的环境变量
-- linux下修改环境变量（临时）
-- export NLS_LANG=“SIMPLIFIED Chinese_CHINA.ZHS16GBK”
-- 如果是windows，按照以下方法添加或者修改，在系统变量中添加如下信息：
-- 变量名：NLS_LANG
-- 变量值：SIMPLIFIED Chinese_CHINA.ZHS16GBK

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

# 修改Oracle DMP文件字符集

DMP文件包含了字符集信息，因此直接修改DMP文件的字符集信息内容就可以骗过Oracle的检查。理论上也仅是从子集到超集可以修改，但很多情况下在没有子集和超集关系的情况下也可以修改。

具体的修改方法比较多，最简单的就是直接用编辑器以16进制方式打开修改DMP文件的第2和第3个字节。

可以用以下SQL查出该种字符集对应的16进制代码，比如UTF8：

select to_char(nls_charset_id('UTF8')， 'xxxx') from dual;

SQL> select to_char(nls_charset_id('UTF8'), 'xxxx') from dual;

TO_CH
-----
  367

1
2
3
4
5

修改DMP文件的字符集为UTF8，将DMP文件的2、3字节修改为0367即可。

知识库

Choose mode