背景 本文主要熟悉下如何用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 |
[Oracle数据库] oracle数据库字符集characterset迁移及变更之csscan及csalter.plb系列三
最新推荐文章于 2021-04-12 18:22:59 发布
本文详细介绍了如何在Oracle数据库中进行字符集迁移,包括使用csscan工具进行扫描和csalter.plb脚本进行变更的步骤,是数据库管理员进行字符集升级的重要参考。
摘要由CSDN通过智能技术生成