联系:手机/微信(+86 17813235971) QQ(107644445)
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
现在有个需求,需要数据库字符集从ZHS16GBK修改为AL32UTF8,因为他们没有子集的关系,所以在转换前,需要先检测库中的数据内容是否全库可以转换为AL32UTF8字符集,检测使用oracle提供的csscan工具实现
一、csscan使用
1.安装csscan相关数据字典
SQL> @?/rdbms/admin/csminst.sql
2.csscan使用说明
[oracle@node1 ~]$ csscan help=y Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:30:42 2011 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.
3.使用csscan检测当前数据库
[oracle@node1 ~]$ csscan userid="'"sys/xifenfei as sysdba"'" full=y \ fromchar=ZHS16GBK tochar=AL32UTF8 log=/tmp/check.log capture=y array=1000000 process=4 Character Set Scanner v2.2 : Release 11.2.0.3.0 - Production on Wed Dec 21 12:36:37 2011 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, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options Enumerating tables to scan... . process 1 scanning SYS.SOURCE$[AAAADgAABAAAAXgAAA] . process 2 scanning XFF.T1[AAATjjAAEAAAC7AAAA] . process 3 scanning TEST.T_XFF[AAAT+6AAEAAAAIQAAA] . process 2 scanning SYS.METHOD$[AAAAHvAABAAAA0QAAA] . process 4 scanning SYS.ATTRIBUTE$[AAAAHvAABAAAA0QAAA] ………………………………………… . process 4 scanning CTXSYS.DR$INDEX_CDI_COLUMN . process 2 scanning CTXSYS.DR$SDATA_UPDATE . process 1 scanning EXFSYS.RLM$RULESETSTCODE . process 3 scanning EXFSYS.RLM$RULESET Creating Database Scan Summary Report... Creating Individual Exception Report... Scanner terminated successfully.
4.查看csscan检查日志
[oracle@node1 ~]$ ll /tmp/check.log.* -rw-r--r-- 1 oracle oinstall 14526 12-21 12:37 /tmp/check.log.err -rw-r--r-- 1 oracle oinstall 100235 12-21 12:37 /tmp/check.log.out -rw-r--r-- 1 oracle oinstall 8265 12-21 12:37 /tmp/check.log.txt --err是错误日志 --out是执行过程 --txt是执行结果汇总 [oracle@node1 tmp]$ cat /tmp/check.log.txt Database Scan Summary Report Time Started : 2011-12-21 12:36:37 Time Completed: 2011-12-21 12:37:13 Process ID Time Started Time Completed ---------- -------------------- -------------------- 1 2011-12-21 12:36:43 2011-12-21 12:37:12 2 2011-12-21 12:36:43 2011-12-21 12:37:12 3 2011-12-21 12:36:43 2011-12-21 12:37:12 4 2011-12-21 12:36:43 2011-12-21 12:37:12 ---------- -------------------- -------------------- [Database Size] Tablespace Used Free Total Expansion ------------------------- --------------- --------------- --------------- --------------- SYSTEM 723.63M 6.38M 730.00M .00K SYSAUX 686.56M 103.44M 790.00M .00K UNDOTBS1 18.31M 126.69M 145.00M .00K TEMP .00K .00K .00K .00K USERS 138.69M 22.56M 161.25M .00K EXAMPLE 310.13M 35.50M 345.63M .00K ------------------------- --------------- --------------- --------------- --------------- Total 1,877.31M 294.56M 2,171.88M .00K [Database Scan Parameters] Parameter Value ------------------------------ ------------------------------------------------ CSSCAN Version v2.1 Instance Name ora11g 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 4 Capture convertible data? YES ------------------------------ ------------------------------------------------ [Scan Summary] All character type data in the data dictionary are convertible to the new character set All character type application data remain the same in the new character set [Data Dictionary Conversion Summary] Data Dictionary Tables: Datatype Changeless Convertible Truncation Lossy --------------------- ---------------- ---------------- ---------------- ---------------- VARCHAR2 6,061,403 152 0 0 CHAR 4,988 0 0 0 LONG 252,530 0 0 0 VARRAY 50,812 0 0 0 --------------------- ---------------- ---------------- ---------------- ---------------- Total 6,369,733 152 0 0 Total in percentage 99.998% 0.002% 0.000% 0.000% The data dictionary can not be safely migrated using the CSALTER script XML CSX Dictionary Tables: Datatype Changeless Convertible Truncation Lossy --------------------- ---------------- ---------------- ---------------- ---------------- VARCHAR2 711 0 0 0 CHAR 0 0 0 0 LONG 0 0 0 0 VARRAY 0 0 0 0 --------------------- ---------------- ---------------- ---------------- ---------------- Total 711 0 0 0 Total in percentage 100.000% 0.000% 0.000% 0.000% [Application Data Conversion Summary] Datatype Changeless Convertible Truncation Lossy --------------------- ---------------- ---------------- ---------------- ---------------- VARCHAR2 8,142,707 0 0 0 CHAR 63,085 0 0 0 LONG 0 0 0 0 VARRAY 583 0 0 0 --------------------- ---------------- ---------------- ---------------- ---------------- Total 8,206,375 0 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 -------------------------------------------------- ---------------- ---------------- ---------------- CTXSYS.DR$INDEX_VALUE 76 0 0 CTXSYS.DR$STOPWORD 76 0 0 -------------------------------------------------- ---------------- ---------------- ---------------- XML CSX Dictionary Tables: USER.TABLE Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- Application Data: USER.TABLE Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- [Distribution of Convertible, Truncated and Lossy Data by Column] Data Dictionary Tables: USER.TABLE|COLUMN Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- CTXSYS.DR$INDEX_VALUE|IXV_VALUE 76 0 0 CTXSYS.DR$STOPWORD|SPW_WORD 76 0 0 -------------------------------------------------- ---------------- ---------------- ---------------- XML CSX Dictionary Tables: USER.TABLE|COLUMN Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- Application Data: USER.TABLE|COLUMN Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- [Indexes to be Rebuilt] USER.INDEX on USER.TABLE(COLUMN) ----------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------- --因为检测结果没有Truncation(截断数据)/Lossy(丢失数据)都不存在记录,所以不用查看错误日志 --如果发现不为0,需要检查err日志,然后先处理丢这些记录,然后再转换
二、修改数据库字符集
[oracle@node1 tmp]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.3.0 Production on Wed Dec 21 12:59:55 2011 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, Oracle Label Security, OLAP, Data Mining, Oracle Database Vault and Real Application Testing options SQL> select value$ from props$ where name='NLS_CHARACTERSET'; VALUE$ ------------------------------------------------------ ZHS16GBK SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2230072 bytes Variable Size 1493174472 bytes Database Buffers 637534208 bytes Redo Buffers 4947968 bytes Database mounted. SQL> alter system enable restricted session; System altered. SQL> alter system set job_queue_processes=0 scope=memory; System altered. SQL> alter system set aq_tm_processes=0 scope=memory; System altered. SQL> alter database open; Database altered. SQL> alter database character set internal_use AL32UTF8; Database altered. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup; ORACLE instance started. Total System Global Area 2137886720 bytes Fixed Size 2230072 bytes Variable Size 1493174472 bytes Database Buffers 637534208 bytes Redo Buffers 4947968 bytes Database mounted. Database opened. SQL> select value$ from props$ where name='NLS_CHARACTERSET'; VALUE$ -------------------------------------------------------- AL32UTF8