修改字符集(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