SQL*Loader 笔记 （一） 热身练习

SQL*Loader是加载数据的主流方法
传统路径：SQLLDR会利用SQL插入的方式加载数据
直径路径：直接格式化数据块

从一个平面文件读取数据，并将其直接写至格式化的数据库块，而绕开整个SQL引擎，同时还能避免redo、undo的生成。要在一个没有任何数据的数据库中充分加载数据，最快的方法就是采用并行直接路径加载

外部表：允许访问操作系统文件，就好像它们是数据库表一样

数据卸载技术

平面文件卸载(flat file unload) 数据泵卸载(data pump unload)

实验1

文件路径 [oracle@node1 mysqlldr]$ pwd /home/oracle/mysqlldr [oracle@node1 mysqlldr]$ ll total 16 -rw-r--r-- 1 oracle oinstall 76 Jul 22 09:53 afiedt.buf -rw-r--r-- 1 oracle oinstall 173 Jul 22 09:56 demo1.ctl -rw-r--r-- 1 oracle oinstall 1611 Jul 22 09:57 demo1.log -rw-r--r-- 1 oracle oinstall 111 Jul 22 09:47 dept.sql配置文件demo1.ctl [oracle@node1 mysqlldr]$ cat demo1.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' (DEPTNO,DNAME,LOC) BEGINDATA 10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia [oracle@node1 mysqlldr]$创建表脚本 [oracle@node1 mysqlldr]$ cat dept.sql create table dept (deptno number(2) constraint dept_pk primary key, dname varchar2(14), loc varchar2(13) ) /执行命令 sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo1.ctl

实验2

配置文件中第四条记录的最后一个字段有很多的字符 [oracle@node1 mysqlldr]$ cat demo2.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' (DEPTNO,DNAME,LOC) BEGINDATA 10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more清空之前插入的数据 ALEX@devdb1 >truncate table dept;执行命令 [oracle@node1 mysqlldr]$ sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo2.ctl在demo2.log中出现了exceeds maximum length 最大长度不够 [oracle@node1 mysqlldr]$ cat demo2.logColumn Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , CHARACTER DNAME NEXT * , CHARACTER LOC NEXT * , CHARACTERRecord 4: Rejected - Error on table DEPT, column LOC. Field in data file exceeds maximum lengthTable DEPT:3 Rows successfully loaded.1 Row not loaded due to data errors.0 Rows not loaded because all WHEN clauses were failed.0 Rows not loaded because all fields were null.数据只成功加载3条，最后一条没有成功 ALEX@devdb1 >select * from dept;DEPTNO DNAME LOC ---------- ------------------------------------------ ---------------------------------------10 Sales Virginia20 Accounting Virginia30 Consulting Virginiademo2.bad文件中记录了加载失败的数据 [oracle@node1 mysqlldr]$ cat demo2.bad 40,Finance,Virginia text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more修改控制文件demo2.ctl，将char默认的255扩展到1000 [oracle@node1 mysqlldr]$ cat demo2.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' (DEPTNO,DNAME ,LOC char(1000)) BEGINDATA 10,Sales,Virginia 20,Accounting,Virginia 30,Consulting,Virginia 40,Finance,Virginia text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text moretext more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text more text moretext more text more text more text more更改表dept的字段loc为varchar2（1000） ALEX@devdb1 >alter table dept modify loc varchar2(1000);Table altered.ALEX@devdb1 >desc dept;Name Null? Type----------------------------------------- -------- ----------------------------DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(1000)

清空表后重新加载

truncate table dept;$ sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo2.ctl

验证结果插入成功

select * from dept;DEPTNO DNAME ---------- ------------------------------------------ LOC --------------------------------------------------------------------------------10 Sales Virginia20 Accounting Virginia30 Consulting VirginiaDEPTNO DNAME ---------- ------------------------------------------ LOC --------------------------------------------------------------------------------40 Finance Virginia text more text more text more text more text more text more text more t ext more text more text more text more text more text more text more text more t ext moretext more text more text more text more text more text more text more te xt more text more text more text more text more text more text more text more te xt more text more text more text more text moretext more text more text more tex t more text more text more text more text moretext more text more text more textmore text m ore text more text more text more text more text more text more textmore text more text more text more text more text more text more text more textDEPTNO DNAME ---------- ------------------------------------------ LOC --------------------------------------------------------------------------------more text more text more text more text more text more text more text more textmoretext more text more text more text more

实验3

配置文件中加入选项“ ”区域中代表一个完整的字段，可忽略其中的,号。

如果字段中含有”，需要用”“来替代 [oracle@node1 mysqlldr]$ cat demo3.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO,DNAME,LOC) BEGINDATA 10,Sales,"Virginia,USA" 20,Accounting,"Va,""USA""" 30,Consulting,Virginia 40,Finance,VirginiaALEX@devdb1 >select * from dept;DEPTNO DNAME LOC ---------- ---------- --------------------10 Sales Virginia,USA <===字段中包含分隔符 , 20 Accounting Va,”USA” <===字段中包含” 30 Consulting Virginia40 Finance Virginia

实验4 使用FILLER参数来过滤掉不需要的列

[oracle@node1 mysqlldr]$ cp demo3.ctl demo4.ctl [oracle@node1 mysqlldr]$ vi demo4.ctlLOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO,DNAME,LOC,dummy1 filler) BEGINDATA 10,Sales,"Virginia,USA",haha 20,Accounting,"Va,""USA""",haha 30,Consulting,Virginia,haha 40,Finance,Virginia,hahatruncate table dept;sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo4.ctl[oracle@node1 mysqlldr]$ cat demo4.logColumn Name Position Len Term Encl Datatype ------------------------------ ---------- ----- ---- ---- --------------------- DEPTNO FIRST * , O(") CHARACTER DNAME NEXT * , O(") CHARACTER LOC NEXT * , O(") CHARACTER DUMMY1 NEXT * , O(") CHARACTER (FILLER FIELD)[oracle@node1 mysqlldr]$ sqlplus alex/alexALEX@devdb1 >select * from dept;DEPTNO DNAME LOC ---------- --------------- ---------------10 Sales Virginia,USA20 Accounting Va,"USA"30 Consulting Virginia40 Finance Virginia

实验5 日期类型的加载

ALEX@devdb1 >alter table dept add last_updated date;Table altered.ALEX@devdb1 >desc dept;Name Null? Type----------------------------------------- -------- ----------------------------DEPTNO NOT NULL NUMBER(2)DNAME VARCHAR2(14)LOC VARCHAR2(1000)LAST_UPDATED DATE[oracle@node1 mysqlldr]$ cat demo5.ctl LOAD DATA INFILE * INTO TABLE DEPT FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' (DEPTNO,DNAME,LOC,LAST_UPDATED date 'yyyy-mm-dd') BEGINDATA 10,Sales,Virginia,2014-05-01 20,Accounting,"Va,""USA""",2014-06-03 30,Consulting,Virginia,2014-07-22 40,Finance,Virginia,2014-07-23[oracle@node1 mysqlldr]$ sqlldr userid=alex/alex control=/home/oracle/mysqlldr/demo5.ctlcol dname for a15 col loc for a15select * from dept;DEPTNO DNAME LOC LAST_UPDATED ---------- --------------- --------------- -------------------10 Sales Virginia 2014/05/01 00:00:0020 Accounting Va,"USA" 2014/06/03 00:00:0030 Consulting Virginia 2014/07/22 00:00:0040 Finance Virginia 2014/07/23 00:00:00

实验6 函数加载

[oracle@node1 mysqlldr]$ cat demo6.ctl LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY ',' (DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc), LAST_UPDATED date 'yyyy-mm-dd') BEGINDATA 10,Sales,Virginia,1998-09-01 20,Accounting,Virginia,2002-08-09 30,Consulting,Virginia,2008-08-08 40,Finance,Virginia,2014-06-03[oracle@node1 mysqlldr]$ sqlldr userid=alex/alex control=demo6.ctl ALEX@devdb1 >select * from dept;DEPTNO DNAME LOC LAST_UPDATED ---------- --------------- --------------- -------------------10 SALES VIRGINIA 1998/09/01 00:00:0020 ACCOUNTING VIRGINIA 2002/08/09 00:00:0030 CONSULTING VIRGINIA 2008/08/08 00:00:0040 FINANCE VIRGINIA 2014/06/03 00:00:00

本文转自ICT时空 dbasdk博客，原文链接： SQL*Loader 笔记 （一） 热身练习，如需转载请自行联系原博主。

总结

以上是生活随笔为你收集整理的SQL*Loader 笔记 （一） 热身练习的全部内容，希望文章能够帮你解决所遇到的问题。

如果觉得生活随笔网站内容还不错，欢迎将生活随笔推荐给好友。