Oracle字符集修改教程-优快云博客

oracle修改字符集

查看oracle服务端编码：select * from sys.nls_database_parameters;
查看client编码：select * from sys.nls_session_parameters;

修改客户端编码：

翻开运转器进入注册表编辑
运转–》regedit
找到HKEY_LOCAL_MACHINE/SOFTWARE/ORACLE/KEY_OraDb10g_home1的变量NLS_LANG的值SIMPLIFIED CHINESE_CHINA.ZHS16GBK改动成以下的值SIMPLIFIED CHINESE_CHINA.AL32UTF8

服务端：

在你的listener配置文件中增加实例信息

SID_LIST_LISTENER =  
  (SID_LIST =  
    (
SID_DESC =  
       (SID_NAME = PLSExtProc)  
       (ORACLE_HOME = F:\oracle\product\10.1.0\Db_1)  
       (PROGRAM = extproc)  
     )  
    (
SID_DESC =  
      (SID_NAME = orcl)  
      (ORACLE_HOME = F:\oracle\product\10.1.0\Db_1)  
    )  
  )

修改oracle字符集

SQL> startup mount;
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes=0;
SQL> alter database open;  www.2cto.com  
SQL> alter database character set internal_use UTF8;
SQL> shutdown immediate;
SQL> startup

oracle查看字符集
select userenv('language') from dual;

Oracle10G,xe修改可以采用下面的方法

插入语:本人使用的oracle数据就是Oracle10G,因为误操作更改了Oracle数据库的编码,致使项目工程在启动时大量报错.同时使用PLSQL工具时,点击table时同样报错,出现很多问号.在使用sql developer工具时,同样无法看到表的内容.但在控制台查询时,却可以查到表的内容,但查询出来的中文内容却全是乱码,显示为问号.

通过select userenv('language') from dual;查看oracle字符集看到,字符集为US7SCII.进一步确认问题的发生,应该就是因为字符集的问题引起,遂尝试下列方式,最终修复问题.

connect system as sysdba ;
shutdown immediate ;
startup mount ;
alter system enable restricted session ; 
alter system set JOB_QUEUE_PROCESSES=0; 
alter system set AQ_TM_PROCESSES=0; 
alter database open ; 
alter database character set internal_use ZHS16GBK ; 
shutdown immediate ;
startup ;

老版本可以参考

STARTUP MOUNT; 
ALTER SYSTEM ENABLE RESTRICTED SESSION; 
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
ALTER SYSTEM SET AQ_TM_PROCESSES=0; 
ALTER DATABASE OPEN; 
ALTER DATABASE CHARACTER SET UTF8; 
ALTER DATABASE national CHARACTER SET UTF8; 
SHUTDOWN IMMEDIATE; 
STARTUP;  www.2cto.com  
Database character set (UTF8) and Client character set (ZHS16GBK) are different.
Character set conversion may cause unexpected results.

Note: you can set the client character set through the NLS_LANG environment variable or the NLS_LANG registry key in
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDb11g_home1.

1)C:\Documents and Settings\Administrator>set NLS_LANG=american_america.AL32UTF8
2)
C:\Documents and Settings\Administrator>set NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
SIMPLIFIED CHINESE_CHINA.ZHS16GBK

注册表  HKEY_LOCAL_MACHINE->software->oracle-》key_oradb11g_home1左侧的NLS_Lang

SIMPLIFIED CHINESE_CHINA.ZHS16GBK与SIMPLIFIED CHINESE_CHINA.AL32UTF8

原文链接:

http://www.2cto.com/database/201205/133453.html