A-A+
修改字符集（ZHS16GBK到UTF-8）
修改字符集（ZHS16GBK到UTF-8）
数据库版本
SQL> set line 400
SQL> select * from gV$version;
INST_ID BANNER
---------- --------------------------------------------------------------------------------
1 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
1 PL/SQL Release 11.2.0.3.0 - Production
1 CORE 11.2.0.3.0 Production
1 TNS for Linux: Version 11.2.0.3.0 - Production
1 NLSRTL Version 11.2.0.3.0 - Production
2 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
2 PL/SQL Release 11.2.0.3.0 - Production
2 CORE 11.2.0.3.0 Production
2 TNS for Linux: Version 11.2.0.3.0 - Production
2 NLSRTL Version 11.2.0.3.0 - Production
10 rows selected.
数据库字符集
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.ZHS16GBK
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 ZHS16GBK
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.3.0
20 rows selected.
数据库用户环境变量
[oracle@11grac1 ~]$ env | grep NLS_LANG NLS_LANG=AMERICAN_AMERICA.zhs16gbk
OS字符集设置
[root@11grac1 ~]# cat /etc/sysconfig/i18n LANG="zh_CN.UTF-8"
测试数据
SQL> conn test/test
Connected.
SQL> create table yallonking (id number,varchar_col1 varchar2(40),nvarchar_col2 nvarchar2(40));
Table created.
SQL> desc yallonking;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
VAR_COL1 VARCHAR2(40)
NVAR_COL2 NVARCHAR2(40)
SQL> select * from yallonking;
ID VARCHAR_COL1 NVARCHAR_COL2
---------- ---------------------------------------- --------------------------------------------------------------------------------
1 我是中文字符 我是中文字符
2 i am english char i am english nvarchar
3 我是中文分号； 我是中文分号；
4 i am english char ; i am english char ;
5 我是中文数字123 我是中文数字123
6 i am english number 123 i am english number 123
6 rows selected.
下边测试将数据库字符集转化为UTF-8
[oracle@11grac1 ~]$ csscan system/oracle FULL=Y FROMCHAR=ZHS16GBK TOCHAR=UTF8 LOG=dbcheck CAPTURE=N ARRAY=1000000 PROCESS=2 Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Mon Jun 17 18:42:38 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options CSS-00107: Character set migration utility schema not installed Scanner terminated unsuccessfully.
安装csscan
[oracle@11grac1 ~]$ exit exit SQL> show user USER is "TEST" SQL> conn /as sysdba Connected. SQL> @?/rdbms/admin/csminst.sql 省略部分输出 Grant succeeded. Grant succeeded. Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
语法：
[oracle@11grac1 ~]$ csscan help =y
Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Mon Jun 17 19:56:41 2013
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
You can let Scanner prompt you for parameters by entering the CSSCAN
command followed by your username/password:
Example: CSSCAN \"SYSTEM/MANAGER AS SYSDBA\"
Or, you can control how Scanner runs by entering the CSSCAN command
followed by various parameters. To specify parameters, you use keywords:
Example:
CSSCAN \"SYSTEM/MANAGER AS SYSDBA\" FULL=y TOCHAR=utf8 ARRAY=1024000 PROCESS=3
Keyword Default Prompt Description
---------- ------- ------ -------------------------------------------------
USERID yes username/password
FULL N yes scan entire database
USER yes owner of tables to be scanned
TABLE yes list of tables to scan
COLUMN yes list of columns to scan
EXCLUDE list of tables to exclude from scan
TOCHAR yes new database character set name
FROMCHAR current database character set name
TONCHAR new national character set name
FROMNCHAR current national character set name
ARRAY 1024000 yes size of array fetch buffer
PROCESS 1 yes number of concurrent scan process
MAXBLOCKS split table if block size exceed MAXBLOCKS
CAPTURE N capture convertible data
SUPPRESS maximum number of exceptions logged for each table
FEEDBACK report progress every N rows
BOUNDARIES list of column size boundaries for summary report
LASTRPT N generate report of the last database scan
LOG scan base file name of report files
PARFILE parameter file name
PRESERVE N preserve existing scan results
LCSD N no enable language and character set detection
LCSDDATA LOSSY no define the scope of the detection
HELP N show help screen (this screen)
QUERY N select clause to scan subset of tables or columns
---------- ------- ------ -------------------------------------------------
Scanner terminated successfully.
[oracle@11grac1 ~]$ csscan \"sys/oracle as sysdba\" FULL=Y Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Mon Jun 17 20:35:34 2013 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options Current database character set is ZHS16GBK. Enter new database character set name: > UTF8 Enter array fetch buffer size: 1024000 > 1024000 Enter number of scan processes to utilize(1..): 1 > 10 Enumerating tables to scan... . process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA] . process 5 scanning SYS.SOURCE$[AAAADgAABAAASyAAAA] ... ... . process 4 scanning CTXSYS.DR$INDEX_CDI_COLUMN . process 3 scanning CTXSYS.DR$SECTION_ATTRIBUTE . process 9 scanning EXFSYS.RLM$RULESETSTCODE . process 8 scanning EXFSYS.RLM$RULESET Creating Database Scan Summary Report... Creating Individual Exception Report... Scanner terminated successfully.
[oracle@11grac1 ~]$ ls scan.err scan.out scan.txt [oracle@11grac1 ~]$ more scan.err Database Scan Individual Exception Report [Database Scan Parameters] Parameter Value ------------------------------ ------------------------------------------------ CSSCAN Version v2.1 Instance Name racdb1 Database Version 11.2.0.3.0 Scan type Full database Scan CHAR data? YES Database character set ZHS16GBK FROMCHAR ZHS16GBK TOCHAR UTF8 Scan NCHAR data? NO Array fetch buffer size 1024000 Number of processes 10 Capture convertible data? NO ------------------------------ ------------------------------------------------ [Data Dictionary individual exceptions] [Application data individual exceptions]
注意：该文件不能有报错。
[oracle@11grac1 ~]$ more scan.txt
Database Scan Summary Report
Time Started : 2013-06-17 20:36:47
Time Completed: 2013-06-17 20:41:07
Process ID Time Started Time Completed
---------- -------------------- --------------------
1 2013-06-17 20:38:46 2013-06-17 20:41:04
2 2013-06-17 20:38:56 2013-06-17 20:41:04
3 2013-06-17 20:39:00 2013-06-17 20:41:04
4 2013-06-17 20:38:58 2013-06-17 20:41:04
5 2013-06-17 20:38:58 2013-06-17 20:41:04
6 2013-06-17 20:38:56 2013-06-17 20:41:04
7 2013-06-17 20:38:56 2013-06-17 20:41:04
8 2013-06-17 20:38:57 2013-06-17 20:41:04
9 2013-06-17 20:38:57 2013-06-17 20:41:04
10 2013-06-17 20:38:57 2013-06-17 20:41:04
---------- -------------------- --------------------
[Database Size]
Tablespace Used Free Total Expansion
------------------------- --------------- --------------- --------------- ---------------
SYSTEM 706.81M 3.19M 710.00M .00K
SYSAUX 558.56M 31.44M 590.00M .00K
UNDOTBS1 16.13M 58.88M 75.00M .00K
TEMP .00K .00K .00K .00K
USERS 1.31M 3.69M 5.00M .00K
UNDOTBS2 20.25M 4.75M 25.00M .00K
OGG 2.38M 47.63M 50.00M .00K
TEST 1.13M 48.88M 50.00M .00K
------------------------- --------------- --------------- --------------- ---------------
Total 1,306.56M 198.44M 1,505.00M .00K
[Database Scan Parameters]
Parameter Value
------------------------------ ------------------------------------------------
CSSCAN Version v2.1
Instance Name racdb1
Database Version 11.2.0.3.0
Scan type Full database
Scan CHAR data? YES
Database character set ZHS16GBK
FROMCHAR ZHS16GBK
TOCHAR UTF8
Scan NCHAR data? NO
Array fetch buffer size 1024000
Number of processes 10
Capture convertible data? NO
------------------------------ ------------------------------------------------
[Scan Summary]
All character type data in the data dictionary remain the same in the new character set
All character type application data are convertible to the new character set
[Data Dictionary Conversion Summary]
Data Dictionary Tables:
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 4,110,195 0 0 0
CHAR 3,062 0 0 0
LONG 249,066 0 0 0
VARRAY 49,807 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 4,412,130 0 0 0
Total in percentage 100.000% 0.000% 0.000% 0.000%
The data dictionary can be safely migrated using the CSALTER script
XML CSX Dictionary Tables:
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 702 0 0 0
CHAR 0 0 0 0
LONG 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 702 0 0 0
Total in percentage 100.000% 0.000% 0.000% 0.000%
[Application Data Conversion Summary]
Datatype Changeless Convertible Truncation Lossy
--------------------- ---------------- ---------------- ---------------- ----------------
VARCHAR2 2,551,951 3 0 0
CHAR 394 0 0 0
LONG 0 0 0 0
VARRAY 0 0 0 0
--------------------- ---------------- ---------------- ---------------- ----------------
Total 2,552,345 3 0 0
Total in percentage 100.000% 0.000% 0.000% 0.000%
[Distribution of Convertible, Truncated and Lossy Data by Table]
Data Dictionary Tables:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
TEST.YALLONKING 3 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Distribution of Convertible, Truncated and Lossy Data by Column]
Data Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
XML CSX Dictionary Tables:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
-------------------------------------------------- ---------------- ---------------- ----------------
Application Data:
USER.TABLE|COLUMN Convertible Truncation Lossy
-------------------------------------------------- ---------------- ---------------- ----------------
TEST.YALLONKING|VARCHAR_COL1 3 0 0
-------------------------------------------------- ---------------- ---------------- ----------------
[Indexes to be Rebuilt]
USER.INDEX on USER.TABLE(COLUMN)
-----------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------
只需要关心输出的文件dbcheck.txt中[Scan Summary]部分的描述，以及[Data Dictionary Conversion Summary]部分的推荐方法。
当然dbcheck.err文件中不能有报错信息。
此处可以直接修改数据库字符集
In 10g and 11g the "ALTER DATABASE CHARACTER SET" command is NOT to be used any more but Csscan/Csalter is the only supported way to change a database characterset.
关闭所有实例
启动一个实例到mount
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup restrict
ORACLE instance started.
Total System Global Area 839282688 bytes
Fixed Size 2233000 bytes
Variable Size 629149016 bytes
Database Buffers 205520896 bytes
Redo Buffers 2379776 bytes
Database mounted.
Database opened.
SQL> @?/rdbms/admin/csalter.plb
0 rows created.
Function created.
Function created.
Procedure created.
This script will update the content of the Oracle Data Dictionary.
Please ensure you have a full backup before initiating this procedure.
Would you like to proceed (Y/N)?Y
old 6: if (UPPER('&conf') <> 'Y') then
new 6: if (UPPER('Y') <> 'Y') then
Checking data validity...
Unrecognized convertible data found in scanner result
PL/SQL procedure successfully completed.
Checking or Converting phase did not finish successfully
No database (national) character set will be altered
CSALTER finished unsuccessfully.
PL/SQL procedure successfully completed.
0 rows deleted.
Function dropped.
Function dropped.
Procedure dropped.
此处发现报错：Unrecognized convertible data found in scanner result
查了文档发下如下解释
If you run Csalter without these conditions met then you will see messages like " Unrecognized convertible data found in scanner result " in the Csalter output.
Before you can run Csalter you need
* to have a 'clean' FULL=Y csscan result, a 'clean' scan means that there is no convertible (except Data Dictionary CLOB data which may be convertible and if so it will be handled by Csalter), truncation or lossy data in the database.
* to have that FULL=Y run been completed in the 7 days prior to running Csalter. So you can only run Csalter in the 7 days following the 'Clean' FULL=Y scan.
* to be sure the session running Csalter is the ONLY session connected to the database, otherwise Csalter will give this warning 'Sorry only one session is allowed to run this script'.
The Csalter script itself takes no arguments, if above conditions are met then Csalter will change the characterset to the one specified in the TOCHAR (or TONCHAR) of csscan.
可能和第一条有关，下边采用sql语句的方法来修改
关闭实例，并启动所有实例查看修改结果。
SQL> SHUTDOWN IMMEDIATE Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP MOUNT; ORACLE instance started. Total System Global Area 839282688 bytes Fixed Size 2233000 bytes Variable Size 574623064 bytes Database Buffers 260046848 bytes Redo Buffers 2379776 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 character set UTF8; alter database character set UTF8 * ERROR at line 1: ORA-12712: new character set must be a superset of old character set SQL> alter database character set INTERNAL_USE UTF8; alter database character set INTERNAL_USE UTF8 * ERROR at line 1: ORA-12720: operation requires database is in EXCLUSIVE mode SQL> alter system set CLUSTER_DATABASE=false scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount exclusive ORACLE instance started. Total System Global Area 839282688 bytes Fixed Size 2233000 bytes Variable Size 574623064 bytes Database Buffers 260046848 bytes Redo Buffers 2379776 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 character set UTF8; alter database character set UTF8 * ERROR at line 1: ORA-12712: new character set must be a superset of old character set SQL> alter database character set INTERNAL_USE UTF8; Database altered. SQL> alter system set CLUSTER_DATABASE=TRUE scope=spfile; System altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.
启动所有实例
SQL> startup ORACLE instance started. Total System Global Area 839282688 bytes Fixed Size 2233000 bytes Variable Size 574623064 bytes Database Buffers 260046848 bytes Redo Buffers 2379776 bytes Database mounted. Database opened.
查看修改后数据库的字符集
SQL> set line 400
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------------------------------
AMERICAN_AMERICA.UTF8
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_CSMIG_SCHEMA_VERSION 5
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
PARAMETER VALUE
------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_TIME_FORMAT HH.MI.SSXFF AM
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.3.0
21 rows selected.
[oracle@11grac1 ~]$ env | grep NLS_LANG
NLS_LANG=AMERICAN_AMERICA.zhs16gbk
[oracle@11grac1 ~]$ env | grep LANG
NLS_LANG=AMERICAN_AMERICA.zhs16gbk
LANG=zh_CN.UTF-8
[oracle@11grac1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 22:11:35 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> set line 400
SQL> select * from test.yallonking;
ID VARCHAR_COL1 NVARCHAR_COL2
---------- -------------------------------------------------------------------------------- --------------------------------------------------------------------------------
1 ？？？？？?? 我是中文字符
2 i am english char i am english nvarchar
3 ？？？？?？？? 我是中文分号；
4 i am english char ; i am english char ;
5 ？？？？？？123 我是中文数字123
6 i am english number 123 i am english number 123
6 rows selected.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
[oracle@11grac1 ~]$ export NLS_LANG=AMERICAN_AMERICA.UTF8
[oracle@11grac1 ~]$ sqlplus "/as sysdba"
SQL*Plus: Release 11.2.0.3.0 Production on Mon Jun 17 22:12:27 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL> set line 400
SQL> select * from test.yallonking;
ID VARCHAR_COL1 NVARCHAR_COL2
---------- ---------------------------------------- ------------------------------------------------------------------------------------------------------------------------
1 我是中文字符 鎴戞槸涓枃瀛楃
2 i am english char i am english nvarchar
3 我是中文分号； 鎴戞槸涓枃鍒嗗彿锛?
4 i am english char ; i am english char ;
5 我是中文数字123 鎴戞槸涓枃鏁板瓧123
6 i am english number 123 i am english number 123
6 rows selected.
此处发现数据库国际字符集在客户端的配置上出现问题
但是在将NLS_NCHAR_CHARACTERSET用同样的方法修改为UTF8后依然是有乱码。
通过修改环境变量 就可以使 varchar和nvarchar 分别显示正确
但是 二者不能一起显示正常
很晚了，先到这以后继续。
tips：
From 9i onwards the NLS_NCHAR_CHARACTERSET can have only 2 values: UTF8 or AL16UTF16.
UTF8：可变长度单字节编码模式（Unicode字符集）
ALUTF8：可变长度多字节编码模式（Unicode字符集）
AL16UTF16:固定长度多字节编码模式（一种2字节的Unicode字符集）
其实从ZHS16GBK修改为AL32UTF8也是可以的。
如下：
[Database Scan Parameters] Parameter Value ------------------------------ ------------------------------------------------ CSSCAN Version v2.1 Instance Name racdb1 Database Version 11.2.0.3.0 Scan type Full database Scan CHAR data? YES Database character set ZHS16GBK FROMCHAR ZHS16GBK TOCHAR AL32UTF8 Scan NCHAR data? NO Array fetch buffer size 1000000 Number of processes 2 Capture convertible data? NO ------------------------------ ------------------------------------------------ [Scan Summary] All character type data in the data dictionary remain the same in the new character set All character type application data are convertible to the new character set
