ORACLE乱码问题解决

一、修改oracle 字符集
由于Oracle 默认安装了SIMPLIFIED CHINESE_CHINA.WE8ISO8859P1字符集，不修改中文会出现乱码现象，输入如下命令：
[oracle@localhost ~]$sqlplus ‘/as sydba’
SQL>shutdown immediate
SQL>Startup restrict
SQL>select userenv('language') from dual;
SIMPLIFIED CHINESE_CHINA.WE8ISO8859P1
SQL>UPDATE sys.PROPS$ SET value$='ZHS16GBK' WHERE NAME='NLS_CHARACTERSET';
SQL>shutdown immediate
SQL>startup;
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
---------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
这样把WE8ISO8859P1改变成'ZHS16GBK' 。
如果执行了修改字符后，重启oracle数据库仍然是WE8ISO8859P1字符集，请重启linux系统即可。

二、执行第一点中的修改后，数据库报错ora-06553:pls-553:character set name is not recognized

确认字符集是否修改的不彻底。

SELECT DISTINCT (NLS_CHARSET_NAME(CHARSETID)) CHARACTERSET,
DECODE(TYPE#,
1,
DECODE(CHARSETFORM,
1,
'VARCHAR2',
2,
'NVARCHAR2',
'UNKOWN'),
9,
DECODE(CHARSETFORM,
1,
'VARCHAR',
2,
'NCHAR VARYING',
'UNKOWN'),
96,
DECODE(CHARSETFORM, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112,
DECODE(CHARSETFORM, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
FROM SYS.COL$
WHERE CHARSETFORM IN (1, 2)
AND TYPE# IN (1, 9, 96, 112);

CHARACTERSET TYPES_USED_IN
-------------------- -------------
AL16UTF16 NCHAR
AL16UTF16 NCLOB
AL16UTF16 NVARCHAR2
UTF8 CHAR
UTF8 VARCHAR2
WE8ISO8859P1 CHAR
WE8ISO8859P1 CLOB
WE8ISO8859P1 VARCHAR2

确实在数据库的列属性中仍然存在着多个字符集的设定，报错的原因。下面解决这个问题。

1.SHUTDOWN IMMEDIATE;
2.STARTUP MOUNT;
3.ALTER SYSTEM ENABLE RESTRICTED SESSION;
4.ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
5.ALTER SYSTEM SET AQ_TM_PROCESSES=0;
6.ALTER DATABASE OPEN;
9.COL VALUE NEW_VALUE CHARSET
10.SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
11.COL VALUE NEW_VALUE NCHARSET
12.SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';
14.--INTERNAL_USE是没有写在文档中的参数，用以强制完成字符集一致化
15.ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
16.ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
19.SHUTDOWN IMMEDIATE;
20.STARTUP;
21.-- 再次启动数据库一遍
22.SHUTDOWN IMMEDIATE;
23.STARTUP;

按照上面的步骤，一步一步来，一步不要少，完成以后，就OK了。