修改Oracle字符集

http://www.cnblogs.com/zjiizj/archive/2010/03/29/1699597.html

Oracle 10G 修改字符集

关于这个问题,网络上有很多修改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
----------------------------------------
在过程里就是吧条件用参数传入

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

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

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

http://www.google.com.hk/search?hl=en&source=hp&q=%E4%BF%AE%E6%94%B9+oracle%E5%AD%97%E7%AC%A6%E9%9B%86&aq=f&oq=&aqi=

http://database.51cto.com/art/201004/194092.htm

我们大家都知道Oracle字符集是具有互相包容性的。例如，us7ascii是zhs16gbk的子集,us7ascii到zhs16gbk不会有相关的数据解释上的一些问题,是不会出现数据丢失的。在所有的字符集中utf8应该是最大,因为它基于unicode,双字节保存字符(也因此在存储空间上占用更多)。

一旦数据库创建后，数据库的字符集理论上讲是不能改变的。因此，在设计和安装之初考虑使用哪一种字符集十分重要。根据Oracle的官方说明，字符集的转换是从子集到超集受支持,反之不行。如果两种字符集之间根本没有子集和超集的关系，那么Oracle字符集的转换是不受Oracle支持的。

对数据库server而言，错误的修改字符集将会导致很多不可测的后果，可能会严重影响数据库的正常运行，所以在修改之前一定要确认两种字符集是否存在子集和超集的关系。一般来说，除非万不得已，我们不建议修改Oracle数据库server端的字符集。

特别说明，我们最常用的两种字符集ZHS16GBK和ZHS16CGB231280之间不存在子集和超集关系，因此理论上讲这两种Oracle字符集之间的相互转换不受支持。

修改server端字符集(不建议使用)

在Oracle 8之前，可以用直接修改数据字典表props$来改变数据库的Oracle字符集。但Oracle8之后，至少有三张系统表记录了数据库字符集的信息，只改props$表并不完全，可能引起严重的后果。正确的修改方法如下:

$sqlplus /nolog
SQL>conn / as sysdba; 　

若此时数据库服务器已启动，则先执行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 ZHS16GBK;
SQL>ALTER DATABASE national CHARACTER SET ZHS16GBK;
SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP


注意：如果没有大对象，在使用过程中进行语言转换没有什么影响，（切记设定的Oracle字符集必须是Oracle支持，不然不能start） 按上面的做法就可以,但是可能会出现‘ORA-12717: Cannot ALTER DATABASE NATIONAL CHARACTER SET when NCLOB data exists’ 这样的提示信息

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

一个是,利用INTERNAL_USE 关键字修改区域设置,

还有一个是利用re-create,但是re-create有点复杂,所以请用internal_use,

SQL>SHUTDOWN IMMEDIATE;
SQL>STARTUP MOUNT EXCLUSIVE;
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 NATIONAL CHARACTER SET INTERNAL_USE UTF8;
SQL>SHUTDOWN immediate;
SQL>startup;


如果按上面的做法做,National charset的区域设置就没有问题上述的相关内容就是对修改Oracle字符集的描述，希望会给你带来一些帮助在此方面。