Oracle字符编码

最近在做一个日本项目,使用的工具为Toad.需要对oracle数据库进行字符编码修正.总结如下:

数据库查询发现字符为乱码,正常情况下有服务器及客户端编码两部分原因导致。于是逐一分析

1. 针对数据库编码查询:(诊断问题原因)
SELECT * FROM V$NLS_PARAMETERS WHERE PARAMETER = 'NLS_CHARACTERSET';

2. 设定Oracle客户端NLS编码
(1)SET NLS_LANG="American_America.JA16SJIS"
(2)环境变量: NLS_LANG=American_America.JA16SJIS 或者采用标准的NLS日文字库:
日语 JAPANESE_JAPAN.JA16EUC (Unix 平台)
JAPANESE_JAPAN.JA16SJIS (NT 平台)
此外,简体中文为 SIMPLIFIED CHINESE_CHINA.ZHS16GBK

3. 设定Toad内部显示编码:
Options->Data/Visual->选择Font为MS PGothic Script为Japanese

4. 发现用DBA身份登陆时报异常: ORA-28000: the account is locked
解决办法:
$sqlplus / as sysdba;
SQL>alter user system identified by oracle account unlock;
SQL>conn system/oracle;

5. 以sysdba方式进入,尝试管理DB:
$sqlplus / as sysdba
SQL>conn system/oracle;
SQL>SHUTDOWN IMMEDIATE;
ORA-01031:
insufficient privileges

6. 换成nolog方式进入,成功获得权限.
进一步修改编码,步骤如下:
$sqlplus /nolog
SQL>conn / as sysdba;
SQL>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 JA16SJIS;
ORA-12712:
new character set must be a superset of old character set.
[i]Action: Specify a superset character set.[/i]
SQL>ALTER DATABASE national CHARACTER SET JA16SJIS;
ORA-12714:
invalid national character set specified.
[i]Cause: Only UTF8 and AL16UTF16 are allowed to be used as the national character set.
Action: Ensure that the specified national character set is valid.[/i]

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

由于目标字符集不是原字符集的超类,因此此路不通..

7. 尝试直接修改系统配置表:
SQL>update props$ set value$='JA16SJIS' where name='NLS_CHARACTERSET';
SQL>update props$ set value$='JA16SJIS' where name='NLS_NCHAR_CHARACTERSET';
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

8. 成功修改字符编码,,并且以前的乱码也成功被解析成正常编码了.但部分程序出现异常:
ORA-12714:
invalid national character set specified.
[i]Cause: Only UTF8 and AL16UTF16 are allowed to be used as the national character set.
Action: Ensure that the specified national character set is valid.[/i]

9. 强制完成字符集一致化:
SQL>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>COL VALUE NEW_VALUE CHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
SQL>COL VALUE NEW_VALUE NCHARSET
SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

SQL>ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
SQL>ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP;

程序运行正常。NICE!