关于这个问题,网络上有很多修改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的修改字符集文章(
)

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
----------------------------------------
在过程里就是吧条件用参数传入

到此,字符集修改完成。这个与上面修改属于同一类型。只不过更加简单明了而已.

===============================================