关于这个问题,网络上有很多修改Oracle字符集的方法,但是真正能够操作或适用的并不多,下面就转载一个相当有用的
一、常规方法修改数据库字符集
当前数据库字符集:
SQL> select name,value$ from props$ where name like '%NLS%';
NAME VALUE$
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
......
20 rows selected.
SQL> conn zwfha/admin
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
HRMS_EMPINFO_COMPANY TABLE
HRMS_EMPINFO_DEPARTMENT TABLE
HRMS_EMPINFO_DIRECTOR TABLE
HRMS_EMPINFO_EDUCATION TABLE
HRMS_EMPINFO_EMPLOYEE TABLE
HRMS_EMPINFO_EXPERIENCE TABLE
HRMS_EMPINFO_FAMILY TABLE
HRMS_EMPINFO_HONOR TABLE
HRMS_EMPINFO_JOB TABLE
HRMS_EMPINFO_STATUS TABLE
HRMS_EMPINFO_TEAM TABLE
11 rows selected.
关闭数据库，打开到mount状态，准备修改数据库字符集(修改前最好备份一下)
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> startup mount
ORACLE instance started.
Total System Global Area 117440512 bytes
Fixed Size 787728 bytes
Variable Size 91224816 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL>
SQL> alter system enable restricted session;
System altered.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- --------------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
SQL>
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 zhs16gbk;
alter database character set zhs16gbk
*
ERROR at line 1:
ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists
----
alter信息：
Wed Jan 16 13:54:36 2008
SYS.METASTYLESHEET (STYLESHEET) - CLOB populated
ORA-12716 signalled during: alter database character set zhs16gbk...
----
这里参考eygle的修改字符集文章(http://www.eygle.com/special/NLS_CHARACTER_SET_03.htm)
SQL> truncate table Metastylesheet;
Table truncated.
SQL> alter database character set zhs16gbk;
alter database character set zhs16gbk
*
ERROR at line 1:
ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists
----
alert信息：
Wed Jan 16 13:57:07 2008
alter database character set zhs16gbk
Wed Jan 16 13:57:07 2008
SYS.RULE$ (CONDITION) - CLOB populated
ORA-12716 signalled during: alter database character set zhs16gbk...
----
看来9.2跟10的差别还是很大嘞。。要小心啊。
使用internal_convert来修改
SQL> alter database character set internal_convert zhs16gbk;
Database altered.
alert里面可以看到，ORACLE会自动转换含有CLOB字段的表
----
alert信息：
Wed Jan 16 14:06:12 2008
alter database character set internal_convert zhs16gbk
Wed Jan 16 14:06:15 2008
Private_strands 7 at log switch
Thread 1 advanced to log sequence 38
Current log# 2 seq# 38 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG
Wed Jan 16 14:06:16 2008
Updating character set in controlfile to ZHS16GBK
Synchronizing connection with database character set information
Wed Jan 16 14:06:16 2008
Published database character set on system events channel
Wed Jan 16 14:06:16 2008
All processes have switched to database character set
SYS.WRI$_DBU_HWM_METADATA (LOGIC) - CLOB representation altered
SYS.WRI$_DBU_FEATURE_METADATA (INST_CHK_LOGIC) - CLOB representation altered
SYS.WRI$_DBU_FEATURE_METADATA (USG_DET_LOGIC) - CLOB representation altered
SYS.WRI$_DBU_FEATURE_USAGE (FEATURE_INFO) - CLOB representation altered
SYS.SCHEDULER$_EVENT_LOG (ADDITIONAL_INFO) - CLOB representation altered
SYS.RULE$ (CONDITION) - CLOB representation altered
Refreshing type attributes with new character set information
Completed: alter database character set internal_convert zhs1
----
因为前面清空了SYS.METASTYLESHEET表，需要重新创建
9.2通过@?/rdbms/admin/catmet.sql创建;
10g中没有catmet.sql这个脚本，通过运行catmeta.sql脚本来重建
@?/rdbms/admin/catmeta.sql
(注意这个地方有待商榷，不确定，最好不要使用这种方法修改)
shutdown
startup
至此，修改正常。
二、使用internal_convert修改数据库字符集
SQL> startup mount
ORACLE instance started.
Total System Global Area 117440512 bytes
Fixed Size 787728 bytes
Variable Size 91224816 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> show parameter processes
NAME TYPE VALUE
------------------------------------ ----------- --------------------------
aq_tm_processes integer 10
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150
打开数据库到restricted状态
SQL> alter system enable restricted session;
System altered.
SQL> alter database open;
Database altered.
查看当前数据库字符集:
SQL> col name for a30
SQL> col value$ for a30
SQL> select name,value$ from props$ where name like '%NLS%';
NAME VALUE$
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET US7ASCII
......
20 rows selected.
这里为了方便区分trace文件名，把trace文件加上标识
SQL> alter session set tracefile_identifier='cs';
Session altered.
SQL> alter session set sql_trace=true;
Session altered.
SQL> alter database character set internal_convert zhs16gbk;
--SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;--跳过超集检查
Database altered.
alert信息:
~~~~~~~~~~~~~~~~~~~~~
Wed Jan 16 14:49:47 2008
alter database character set internal_convert zhs16gbk
Wed Jan 16 14:49:50 2008
Private_strands 7 at log switch
Thread 1 advanced to log sequence 38
Current log# 2 seq# 38 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG
Wed Jan 16 14:49:52 2008
Updating character set in controlfile to ZHS16GBK
Synchronizing connection with database character set information
Wed Jan 16 14:49:52 2008
Published database character set on system events channel
SYS.WRI$_DBU_HWM_METADATA (LOGIC) - CLOB representation altered
SYS.WRI$_DBU_FEATURE_METADATA (INST_CHK_LOGIC) - CLOB representation altered
SYS.WRI$_DBU_FEATURE_METADATA (USG_DET_LOGIC) - CLOB representation altered
SYS.WRI$_DBU_FEATURE_USAGE (FEATURE_INFO) - CLOB representation altered
SYS.SCHEDULER$_EVENT_LOG (ADDITIONAL_INFO) - CLOB representation altered
SYS.RULE$ (CONDITION) - CLOB representation altered
SYS.METASTYLESHEET (STYLESHEET) - CLOB representation altered
Refreshing type attributes with new character set information
alert中的信息与上面的相同，ORACLE在内部转换CLOB字段相关的表为新字符集，这个在trace文件中可以很清楚得看到。
这里就不贴trace信息了。有兴趣自己trace一下。
查看修改后的字符集：
SQL> select name,value$ from props$ where name like '%NLS%';
NAME VALUE$
------------------------------ ------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET ZHS16GBK
......
SQL> alter system disable restricted session;
System altered.
D:\>set NLS_LANG=american_america.ZHS16GBK
D:\>
D:\>sqlplus zwfha/admin
SQL> select name from HRMS_EMPINFO_JOB where rownum<3;
NAME
----------------------------------------
总裁
总监
SQL> create table t(name varchar2(200));
Table created.
SQL>
SQL> insert into t values('在过程里就是吧条件用参数传入');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t;
NAME
----------------------------------------
在过程里就是吧条件用参数传入
到此，字符集修改完成。这个与上面修改属于同一类型。只不过更加简单明了而已.
===============================================
