oracle 修改数据库的字符集编码为UTF-8

1、查看数据库字符集 ？
数据库服务器字符集select * from nls_database_parameters，其来源于props$，是表示数据库的字符集。
客户端字符集环境select * from nls_instance_parameters,其来源于v$parameter，
表示客户端的字符集的设置，可能是参数文件，环境变量或者是注册表
会 话字符集环境 select * from nls_session_parameters，其来源于v$nls_parameters，表示会话自己的设置，可能是会话的环境变量或者是alter session完成，如果会话没有特殊的设置，将与nls_instance_parameters一致。
客户端的字符集要求与服务器一致，才能正确显示数据库的非Ascii字符。如果多个设置存在的时候，alter session>环境变量>注册表>参数文件
字符集要求一致，但是语言设置却可以不同，语言设置建议用英文。如字符集是zhs16gbk，则nls_lang可以是American_America.zhs16gbk。

 

 

2、修改Oracle的字符集，如下例把字符集GB2312修改为UTF-8
步骤：
1.在SQL*PLUS 中，以DBA登录
conn 用户名 as sysdba
2.执行转换语句：
SHUTDOWN IMMEDIATE;
STARTUP MOUNT EXCLUSIVE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE NATIONAL CHARACTER SET UTF8;
SHUTDOWN immediate;
startup;

注意：如果没有大对象，在使用过程中进行语言转换没有什么影响，（切记设定的字符集必须是ORACLE支持，不然不能start）

按上面的做法就可以,但是可能会出现‘ORA-12717: Cannot ALTER DATABASE NATIONAL CHARACTER SET when
NCLOB data exists’ 这样的提示信息

要解决这个问题有两种方法

一个是,利用INTERNAL_USE 关键字修改区域设置,
还有一个是利用re-create,但是re-create有点复杂,所以请用internal_use,

SHUTDOWN IMMEDIATE;
STARTUP MOUNT EXCLUSIVE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;
SHUTDOWN immediate;
startup;

如果按上面的做法做,National charset的区域设置就没有问题 。

 

 

 

oracle11GR2上建立一个新用户的过程,同时更改字符集.

 

SQL> create user mygmccrm identified by abc123; 

User created. 

SQL> grant create session to mygmccrm; 

Grant succeeded. 

SQL> grant create table to mygmccrm; 

Grant succeeded. 

SQL> grant unlimited tablespace to mygmccrm; 

Grant succeeded. 


SQL> grant connect to mygmccrm with admin option; 

Grant succeeded. 


SQL> select * from nls_database_parameters; 

PARAMETER VALUE 
------------------------------ ---------------------------------------- 
NLS_LANGUAGE AMERICAN 
NLS_TERRITORY AMERICA 
NLS_CURRENCY $ 
NLS_ISO_CURRENCY AMERICA 
NLS_NUMERIC_CHARACTERS ., 
NLS_CHARACTERSET WE8MSWIN1252 
NLS_CALENDAR GREGORIAN 
NLS_DATE_FORMAT DD-MON-RR 
NLS_DATE_LANGUAGE AMERICAN 
NLS_SORT BINARY 
NLS_TIME_FORMAT HH.MI.SSXFF AM 

PARAMETER VALUE 
------------------------------ ---------------------------------------- 
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 
NLS_DUAL_CURRENCY $ 
NLS_COMP BINARY 
NLS_LENGTH_SEMANTICS BYTE 
NLS_NCHAR_CONV_EXCP FALSE 
NLS_NCHAR_CHARACTERSET AL16UTF16 
NLS_RDBMS_VERSION 11.2.0.1.0 

20 rows selected. 


SQL> select * from nls_database_parameters; 

PARAMETER VALUE 
------------------------------ ---------------------------------------- 
NLS_LANGUAGE AMERICAN 
NLS_TERRITORY AMERICA 
NLS_CURRENCY $ 
NLS_ISO_CURRENCY AMERICA 
NLS_NUMERIC_CHARACTERS ., 
NLS_CHARACTERSET WE8MSWIN1252 
NLS_CALENDAR GREGORIAN 
NLS_DATE_FORMAT DD-MON-RR 
NLS_DATE_LANGUAGE AMERICAN 
NLS_SORT BINARY 
NLS_TIME_FORMAT HH.MI.SSXFF AM 

PARAMETER VALUE 
------------------------------ ---------------------------------------- 
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM 
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR 
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR 
NLS_DUAL_CURRENCY $ 
NLS_COMP BINARY 
NLS_LENGTH_SEMANTICS BYTE 
NLS_NCHAR_CONV_EXCP FALSE 
NLS_NCHAR_CHARACTERSET UTF8 
NLS_RDBMS_VERSION 11.2.0.1.0 

20 rows selected. 


SQL> SHUTDOWN IMMEDIATE; 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 
SQL> STARTUP MOUNT EXCLUSIVE; 
ORACLE instance started. 

Total System Global Area 855982080 bytes 
Fixed Size 2218152 bytes 
Variable Size 616564568 bytes 
Database Buffers 230686720 bytes 
Redo Buffers 6512640 bytes 
Database mounted. 
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; 

System altered. 

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 NATIONAL CHARACTER SET UTF8; 
ALTER DATABASE NATIONAL CHARACTER SET UTF8 
* 
ERROR at line 1: 
ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR 
or NVARCHAR2 data exists 


SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8; 

Database altered. 

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


create public database link gm 
connect to app_mygmc identified by gm11gtest 
using 'gm'; 
SQL> grant dba to mygmccrm with admin option; 

Grant succeeded. 


UPDATE sys.PROPS$ SET value$='SIMPLIFIED CHINESE' WHERE NAME='NLS_LANGUAGE';

 

评论

1 楼 gc715409742 2011-06-27  

引用

122.SQL> ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE UTF8;   

为什么不是改成utf-8呢？
strut2中做添加，添加到oracle还是乱码