[Oracle数据库] oracle数据库字符集characterset迁移及变更之csscan及csalter.plb系列三

背景 本文主要熟悉下如何用csscan以及csalter脚本变更数据库字符集，进一步理解如何调整数据库字符集的知识。 之前的相关文章链接： oracle10g_csscan_更变数据库字符集及国家字符集的工具测试 http://blog.itpub.net/9240380/viewspace-614070/ csscan: error while loading shared libraries: libclntsh.so.10.1  http://blog.itpub.net/9240380/viewspace-766685/ 分析思路  结论 1,csscan结合csalter.plb可以调整数据库的数据库字符集 2,运行完csscan后，会在当前目录产生3个文件： [oracle@seconary admin]$ ll -lrht scan*http://www.kmnk03.com/hxpfk/tf/344.html -rw-r--r-- 1 oracle oinstall 9.2K Nov 30 01:45 scan.txt ---需要关注的文件 -rw-r--r-- 1 oracle oinstall 87K Nov 30 01:45 scan.out ---这个就是类似LOG之类的文件 -rw-r--r-- 1 oracle oinstall 5.0K Nov 30 01:45 scan.err --需要关注的文件 具体关于这几个文件，请见下述测试 只要把上述2个关注文件里面的错误解决完毕，方可继续运行csalter.plb,否则会报如下错误 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. 3，csscan的用户和exp同理，也分为数据库级，用户级，表级，为了方便运行，可以采用命令行模式运行： csscan userid=\'sys/system as sysdba\' full=y tochar=AL32UTF8 array=1024000 process=10 4,具体分析思路为： [oracle@seconary admin]$ more scan.txt Data Dictionary Tables: http://www.kmnk03.com/hxpfk/tf/345.html USER.TABLE|COLUMN Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- XML CSX Dictionary Tables: USER.TABLE|COLUMN Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- Application Data: USER.TABLE|COLUMN Convertible Truncation Lossy -------------------------------------------------- ---------------- ---------------- ---------------- -------------------------------------------------- ---------------- ---------------- ---------------- 确认上述文件中列Convertible的内容为0或空，否则在运行csalter.plb会上述第2提到的错误 因为这步我没有理解到位，消耗了1个小时左右的时间 4，后来是查阅MOS文章：oracle_更改数据库字符集 (NLS_CHARACTERSET) ，受到启发，用上述第4提示的涉及Convertible的表或相关数据，把这些数据EXP出来 然后再删除这些数据，接着重新运行csscan,直到cscsan.txt中的Convertible为空，最后再运行csalter.plb，就成功了，然后导入EXP的数据 不过记得一定要重启数据库 这步可以说是我认为最有价值的地方 5，再引申一下，其实从csalter.plb运行输出日志也会有对应的提示，如下： Checking data validity...http://www.kmnk03.com/hxpfk/tf/346.html Unrecognized convertible data found in scanner result ---看到没 convertible，Unrecognized，这里就很重要 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.  所以说一定要深入理解输出日志的报错，太重要了，太重要了 6,关于csscan运行产生的3个日志，理解还不够，还要进一步理解，结合实战案例，解决问题 分析思路 测试 SQL> select parameter,value from nls_database_parameters where parameter='NLS_CHARACTERSET'; PARAMETER VALUE ------------------------------ -------------------------------------------------- NLS_CHARACTERSET ZHS16GBK http://www.kmnk03.com/hxpfk/tx/347.html SQL> show user USER is "SCOTT" SQL> select a,dump(a) from t_charset where rownum=1; A DUMP(A) ---------- -------------------------------------------------- 我们 Typ=96 Len=10: 230,136,145,228,187,172,32,32,32,32 ---查看csscan用法 [oracle@seconary admin]$ csscan -help Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Nov 30 01:32:25 2015 Copyright (c) 1982, 2009, 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\"  http://www.kmnk03.com/hxpfk/tx/348.html 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 --获取数据的ARRAY大小  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 http://www.kmnk01.com/hxpfk/2015/tf_1220/322.html 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@seconary admin]$  ---提示CSS-00107: Character set migration utility schema not installed好像一个什么用户示例数据没安装 [oracle@seconary admin]$ csscan userid=\'sys/system as sysdba\' full=y Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Nov 30 01:37:02 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options CSS-00107: Character set migration utility schema not installed Scanner terminated unsuccessfully. 根据我之前的文章： http://blog.itpub.net/9240380/viewspace-614070/ http://www.kmnk01.com/hxpfk/2015/tf_1220/323.html 可解决上述的问题 [oracle@seconary admin]$ sqlplus '/as sysdba' SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 30 01:39:05 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options SQL> @$ORACLE_HOME/rdbms/admin/csminst.sql 中间内容略 View created. View created. View created. Grant succeeded. Grant succeeded. --重新运行CSSCAN即可http://www.kmnk01.com/hxpfk/2015/tf_1220/324.html [oracle@seconary admin]$ csscan userid=\'sys/system as sysdba\' full=y Character Set Scanner v2.2 : Release 11.2.0.1.0 - Production on Mon Nov 30 01:40:19 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Current database character set is ZHS16GBK. Enter new database character set name: > AL32UTF8 --输入目标字符集 Enter array fetch buffer size: 1024000 > 可见与csscan命令行的参数可以对应起来，这里是交互式方式，道理同exp Enter number of scan processes to utilize(1..64): 1 > 10 Enumerating tables to scan... . process 1 scanning SYS.SOURCE$[AAAADfAABAAAAXYAAA] ---可见会扫描所有用户的表 . process 7 scanning SYS.SOURCE$[AAAADfAABAAAR+AAAA] . process 3 scanning SYS.PARAMETER$[AAAAH3AABAAAAzgAAA] . process 3 scanning SYS.OBJ$[AAAAASAABAAAADwAAA] . process 7 scann