sqlloader笔记.docxVIP

SQL Loader前言：当你需要从A数据库把数据转换到B数据库时，通常会使用到insert语句、exp加imp 语句、建立dblink等传统方法。这些方法的优点在于不需要太多的准备操作，可以随时随地的快捷迁移数据，但缺点也非常明显，就是慢。而在不同数据库的复制迁移方法里面，sql loader技术几乎是最快捷的一种。在对比测试中，使用传统数据迁移方法一次性迁移几十上百万条数据时，会花费非常多的时间（电脑基本卡死），但使用sql loader 来迁移，只需十秒左右。我在无极限项目组工作时，由于客户不会提供敏感数据给我们，所以我们只能写出一个迁移数据的脚本给客户，让客户自己动手迁移，考虑一番之后使用的就是SQL Loader。Sql loader 的缺点就是，爆出的bug都是毕竟难以理解和解决的，需要一定的经验。技术组成：Sql loader 主要由两部分组成：编译控制文件 Ctl 和数据文件Csv。Ctl文件是存储我们编译控制代码的文件，由txt另存为即可。Csv文件是存储导入数据的文件，由excel另存为即可。工作流程：得到源数据库导出的excel文件，对其中的日期等作格式修改。另存为csv文件，修改编码格式为UTF8.确认数据库中的表字段，excel表中的行字段，编写ctl。到数据库上编译ctl，检查数据导入是否成功。详细步骤：一、生成CSV：原excel：我们需要对excel作部分格式修改，比如把NULL直接替换成空，把日期格式替换成date的 yyyy-mm-ddhh:mm:ss修改后的excel：修改excel后留意一下excel表字段的顺序，比如先是idNO然后是Birthday。数据导入是按字段顺序来计算的，最好与数据库表的字段顺序一致，如冲突，ctl以excel表为主。修改完毕后就可以另存为CSV文件了。转换CSV编码为UTF-8注意：不要用excel直接打开CSV，部分CSV会出现乱码。这里建议使用Notepad++打开CSV，在格式那里把文件转换为UTF8格式。二、编写CTL： CTL是编译控制文件，是由我们编写的代码程序。这是ctl的通用控制语句，还有部分特别的控制语句可以在网上搜索。比如rows是用于行参数的值，1 rows 大概等于219300 bytes。日期字段的导入需要格式。在最后字段添加该语句可以有效防止导入额外空格的情况。编写sh文件可以方便导入多文件数据，可以不用。Sqlldr是 sqlloader的编译开始关键字。 后面接数据库用户信息，ctl和csv文件所在的路径。三、编译运行：上传到服务器编译sh文件编译成功查看log日志文件四、问题解决：使用SQLLoader最大的问题就是，报错信息比较难懂。比如说：这些问题看上去易懂，但并没有具体说出报错的原因，很多时候它的报错信息都非常难以解决。比如说ORA-12899 的报错信息，字面意思为录入字段大小超过了数据库的分配容量。这可能是因为数据的字段大小的确超标了，也可能是在录入时插入了不需要的空格，也可能是数据串行了等等。所以不建议只针对报错信息进行debug。这里分享一下我的经验。首先读取数据不成功时，是ctl或者编译代码的问题。注意ctl里面的中字符、空格等。Ctl文件的每一行后面都最好给一个空格，括号前后不要有空格，等等格式问题。很多错误比如编译时乱码、编译时报不能为空的问题，大部分都是ctl问题。我的建议是遇上实在解决不了的错误，重新手打一份ctl，注意空格等。最麻烦的例如ORA-01722: invalid number问题或者ORA-12899串行问题（字节不够之类），要解决的话要从多方面入手，因为它对应的是多种错误。最好的解决方法是排查ctl里面的参数、数据库参数、excel文件里面的字段排序，最好都是一致的，注意格式。可以浏览一下CSV文件，看一下是否发生了乱码错位等现象。解决思路：先看ORA报错信息，遇上无法解决的问题，先从ctl开始排除问题，然后重新生成csv文件，注意excel格式的修改操作。格式编码也很重要，Ctl的一般为默认的GBK , CSV的数据源必须是UTF-8。