Oracle 11g R2 RAC数据库中文乱码解决

最新推荐文章于 2026-08-26 10:13:31 发布

原创最新推荐文章于 2026-08-26 10:13:31 发布 · 2.5k 阅读

1 ·

本内容遵循CC 4.0 BY-SA版权协议

收录于

Basic

本文详细介绍了如何在Oracle 11g RAC环境下更改数据库字符集以解决中文乱码问题，并提供了具体的SQL命令及步骤。此外，还介绍了如何设置客户端环境变量解决SQL Plus中的中文显示问题。

1、解决数据库中文乱码：

更改字符集步骤(WE8ISO8859P1 --> ZHS16GBK) ：

[oracle@rac121 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期五 8月 31 10:56:55 2012

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

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

SQL> shutdown immediate --这儿是RAC,所以需要关闭所有节点
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.3462E+10 bytes
Fixed Size                  2241104 bytes
Variable Size            7281315248 bytes
Database Buffers         6174015488 bytes
Redo Buffers                4485120 bytes
Database mounted.

SQL> alter system enable restricted session;

System altered.

SQL> show parameter job_queue

NAME                              TYPE          VALUE
-----------------------     -----------  ----------
job_queue_processes         integer        1000
SQL> show parameter aq_tm

NAME                              TYPE          VALUE
-----------------------     -----------  ----------
aq_tm_processes              integer        10

SQL> alter system set job_queue_processes=0;

System altered.

SQL> alter system set aq_tm_processes=0;

System altered.

SQL> alter database open;

Database altered.

SQL> Alter DATABASE character set INTERNAL_USE ZHS16GBK;
Alter DATABASE character set INTERNAL_USE ZHS16GBK
*
ERROR at line 1:
ORA-12720: 操作要求数据库处于 EXCLUSIVE 模式下 --由于是RAC环境，所以要修改参数cluster_database

SQL> alter system set cluster_database=false scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1.3462E+10 bytes
Fixed Size                  2241104 bytes
Variable Size            7281315248 bytes
Database Buffers         6174015488 bytes
Redo Buffers                4485120 bytes
Database mounted.
SQL> alter system enable restricted session;

System altered.

SQL> alter database open;

Database altered.

SQL> Alter DATABASE character set INTERNAL_USE ZHS16GBK;

Database altered.

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

USERENV('LANGUAGE')
----------------------------------------------------
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

SQL> alter system set job_queue_processes=1000;

System altered.

SQL> alter system set aq_tm_processes=10;

System altered.

SQL> alter system set cluster_database=true scope=spfile;

System altered.

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


Total System Global Area 1.3462E+10 bytes
Fixed Size                  2241104 bytes
Variable Size            7281315248 bytes
Database Buffers         6174015488 bytes
Redo Buffers                4485120 bytes
Database mounted.
Database opened.
SQL> show parameter cluster_database

NAME                                    TYPE                   VALUE
-----------------                    -------------        ------------------
cluster_database                     boolean                TRUE
cluster_database_instances       integer                 2
SQL> --到此可以向数据库中插入中文

2、SQLPLUS中文乱码：

[oracle@rac122 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Aug 31 11:20:21 2012

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

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

SQL> select * from terry.t2;

VNAME
--------------------
??
?????

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@rac122 ~]$ export NLS_LANG="SIMPLIFIED CHINESE_CHINA.ZHS16GBK"
[oracle@rac122 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on 星期五 8月 31 11:20:55 2012

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

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

SQL> select * from terry.t2;

VNAME
--------------------
呵呵
一二三国四

SQL>