sqlloader的用法.doc

sqlloader的用法sqlldr userid=system/password control=h:control.txtuserid是用户名和密码，control是控制文件位置。下面是控制文件中的内容：load datainfile h:bbb.txt-文本文件的位置append into table table_name -#1FIELDS TERMINATED BY , -字段和字段之间的分隔符，如果使用X09代表是tab，是否是tab没有测试。TRAILING NULLCOLS -表的字段没有对应的值时允许为空 (field1,field2,field3)-具体入库的表字段#1。这里可以使用append、insert、replace和truncate，每个参数具体意义如下：APPEND 原先的表有数据 就加在后面INSERT 装载空表 如果原先的表有数据 sqlloader会停止 默认值REPLACE 原先的表有数据 原先的数据会全部删除TRUNCATE 指定的内容和replace的相同 会用truncate语句删除现存数据 用sqlloader也可以一次导入多个表中只要多加几个into就可以了，具体用法和insert的插入多个表用法类似。入库的时候格式化也是可以的，可以参照外部表那篇文章。用法二:数据文件：test1.txtSYSTEM , 40960 SYSTEM , 40960 SYSTEM , 323649536 SYSTEM , 81920 SYSTEM , 74350592 SYSTEM , 65536 SYSTEM , 81920 SYSTEM , 40960 SYSTEM , 40960 SYSTEM , 19963904 SYSTEM , 90112 SYSTEM , 229376 ZFAD , 11526144 控制文件：input.ctlload datainfile test1.txtappend into table sum_dba_free_spacefields terminated by ,(tablespace_name,bytes) db建表：create table sum_dba_free_space(tablespace_name varchar2(30),bytes number) c:sqlldr scott/tiger control=input.ctl 加载失败的log（同时会产生test.bad文件）：c:input.log SQL*Loader: Release .0 - Production on 星期五 10月 10 01:53:43 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.控制文件: input.ctl数据文件: test1.txt 错误文件: test1.bad 废弃文件: 未作指定 (可废弃所有记录)要加载的数: ALL要跳过的数: 0允许的错误: 50绑定数组: 64 行, 最大 256000 字节继续: 未作指定所用路径: 常规表 SUM_DBA_FREE_SPACE,已加载从每个逻辑记录插入选项对此表 APPEND 生效 列名 位置 长度 中止 包装数据类型- - - - - -TABLESPACE_NAME FIRST * , CHARACTER BYTES NEXT * , CHARACTER 表 SUM_DBA_FREE_SPACE: 6984 行 加载成功。 由于数据错误, 0 行 没有加载。 由于所有 WHEN 子句失败, 0 行 没有加载。 由于所有字段都为空的, 0 行 没有加载。为绑定数组分配的空间: 33024 字节 (64 行)读取 缓冲区字节数: 1048576跳过的逻辑记录总数: 0读取的逻辑记录总数: 6984拒绝的逻辑记录总数: 0废弃的逻辑记录总数: 0从 星期五 10月 10 01:53:43 2008 开始运行在 星期五 10月 10 01:53:43 2008 处运行结束经过时间为: 00: 00: 00.31CPU 时间为: 00: 00: 00.20 c:test.bad: SYSTEM , 40960SYSTEM , 98304SYSTEM , 81920SYSTEM , 40960SYSTEM , 40960SYSTEM , 323649536SYSTEM , 81920SYSTEM , 74350592SYSTEM , 65536SYSTEM , 81920SYSTEM , 40960SYSTEM , 40960SYSTEM , 19963904SYSTEM , 90112SYSTEM , 229376ZFAD , 11526144ZFAX , 11526144RBS , 819200RBS , 778240CTXD , 61743104CTXD , 520093696CTXD , 8380416OWAPUB , 10403840ABMD , 37847040ABMX , 33423360AKD , 81920AKD , 81920AKD , 60866560AKX , 60334080ALRD , 7004160ALRX , 14827520AMSD , 12165120AMSX , 14499840AMVD , 14090240AMVX , 10690560APD , 698572800APX , 885104640APPLSYSD , 409600APPLSYSD , 942080APPLSYSD , 2088960APPLSYSD , 81920APPLSYSD , 901120APPLSYSD , 2129920APPLSYSD , 2129920APPLSYSD , 2129920APPLSYSD , 2129920APPLSYSD , 2129920APPLSYSD , 2129920APPLSYSD , 2129920APPLSYSD , 2129920APPLSYSD , 2129920(共50条记录) 加载成功的情况：C:sqlldr scott/tiger control=input.ctlSQL*Loader: Release .0 - Production on 星期五 10月 10 01:53:43 2008Copyright (c) 1982, 2007, Oracle. All rights reserved.达到提交点 - 逻辑记录计数 64达到提交点 - 逻辑记录计数 128达到提交点 - 逻辑记录计数 192达到提交点 - 逻辑记录计数 256达到提交点 - 逻辑记录计数 320达到提交点 - 逻辑记录计数 384达到提交点 - 逻辑记录计数 448达到提交点 - 逻辑记录计数 512达到提交点 - 逻辑记录计数 576.达到提交点 - 逻辑记录计数 6983达到提交点 - 逻辑记录计数 6984C: 附：test1.csv SYSTEM 40960 SYSTEM 98304 SYSTEM 81920 SYSTEM 40960 SYSTEM 40960 SYSTEM 3.24E+08 SYSTEM 81920 SYSTEM 74350592 SYSTEM 65536 SYSTEM 81920 SYSTEM 40960 SYSTEM 40960 SYSTEM 19963904 SYSTEM 90112 SYSTEM 229376 ZFAD 11526144 ZFAX 11526144 RBS 819200 RBS 778240 CTXD 61743104 CTXD 5.2E+08 CTXD 8380416 OWAPUB 10403840 ABMD 37847040 ABMX 33423360 AKD 81920 AKD 81920 AKD 60866560 AKX 60334080 ALRD 7004160 ALRX 14827520 AMSD 12165120 AMSX 14499840 AMVD 14090240 AMVX 10690560 APD 6.99E+08 APX 8.85E+08 APPLSYSD 409600 APPLSYSD 942080 APPLSYSD 2088960 APPLSYSD 81920 APPLSYSD 901120 APPLSYSD 2129920 APPLSYSD 2129920 APPLSYSD 2129920 APPLSYSD 2129920 APPLSYSD 2129920 2129920 2129920 2129920 2129920 input.ctl： load datainfile test1.csvappend into table sum_dba_free_spacefields terminated by ,(tablespace_name,bytes)发表于 2008年10月10日09:44:00 | 评论( 0 ) | 编辑| 举报| 收藏 旧一篇:vmstat 命令详解 | 新一篇:oracle db block structure给onlydba的留言只有注册用户才能发表评论！登录注册本文来自CSDN博客，转载请标明出处：/onlydba/archive/2008/10/10/3048002.aspxoracle sql loader全攻略 oracle sql loader全攻略(一)一：sql loader 的特点oracle自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。但是每个工具都有自己的特点。比如说exp和imp可以对数据库中的数据进行导出和导出的工作，是一种很好的数据库备份和恢复的工具，因此主要用在数据库的热备份和恢复方面。有着速度快，使用简单，快捷的优点；同时也有一些缺点，比如在不同版本数据库之间的导出、导入的过程之中，总会出现这样或者那样的问题，这个也许是oracle公司自己产品的兼容性的问题吧。sql loader 工具却没有这方面的问题，它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中，是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。缺点就速度比较慢，另外对blob等类型的数据就有点麻烦了。二：sql loader 的帮助 C:sqlldrSQL*Loader: Release .0 - Production on 星期六 10月 9 14:48:12 2004Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.用法: SQLLDR keyword=value ,keyword=value,.有效的关键字:userid - ORACLE username/passwordcontrol - Control file namelog - Log file namebad - Bad file namedata - Data file namediscard - Discard file namediscardmax - Number of discards to allow (全部默认)skip - Number of logical records to skip (默认0)load - Number of logical records to load (全部默认)errors - Number of errors to allow (默认50)rows - Number of rows in conventional path bind array or between direct path data saves（默认: 常规路径 64, 所有直接路径）bindsize - Size of conventional path bind array in bytes(默认256000)silent - Suppress messages during run (header,feedback,errors,discards,partitions)direct - use direct path (默认FALSE)parfile - parameter file: name of file that contains parameter specificationsparallel - do parallel load (默认FALSE)file - File to allocate extents fromskip_unusable_indexes - disallow/allow unusable indexes or index partitions(默认FALSE)skip_index_maintenance - do not maintain indexes, mark affected indexes as unusable(默认FALSE)readsize - Size of Read buffer (默认1048576)external_table - use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE(默认NOT_USED)columnarrayrows - Number of rows for direct path column array(默认5000)streamsize - Size of direct path stream buffer in bytes(默认256000)multithreading - use multithreading in direct pathresumable - enable or disable resumable for current session(默认FALSE)resumable_name - text string to help identify resumable statementresumable_timeout - wait time (in seconds) for RESUMABLE(默认7200)date_cache - size (in entries) of date conversion cache(默认1000)PLEASE NOTE: 命令行参数可以由位置或关键字指定。前者的例子是 sqlloadscott/tiger foo; 后一种情况的一个示例是 sqlldr control=foouserid=scott/tiger.位置指定参数的时间必须早于但不可迟于由关键字指定的参数。例如,允许 sqlldr scott/tiger control=foo logfile=log, 但是不允许 sqlldr scott/tiger control=foo log, 即使参数 log 的位置正确。C:三：sql loader使用例子a）SQLLoader将 Excel 数据导出到 Oracle1.创建SQL*Loader输入数据所需要的文件,均保存到C:，用记事本编辑：控制文件：input.ctl，内容如下：load data -1、控制文件标识infile test.txt -2、要输入的数据文件名为test.txtappend into table test-3、向表test中追加记录fields terminated by X09-4、字段终止于X09，是一个制表符（TAB）(id,username,password,sj) -定义列对应顺序a、insert，为缺省方式，在数据装载开始时要求表为空b、append，在表中追加新记录c、replace，删除旧记录，替换成新装载的记录d、truncate，同上在DOS窗口下使用SQL*Loader命令实现数据的输入C:sqlldr userid=system/manager control=input.ctl默认日志文件名为：input.log默认坏记录文件为：input.bad2.还有一种方法可以把EXCEL文件另存为CSV（逗号分隔）(*.csv),控制文件就改为用逗号分隔LOAD DATAINFILE d:car.csvAPPEND INTO TABLE t_car_tempFIELDS TERMINATED BY ,(phoneno,vip_car)b）在控制文件中直接导入数据1、控制文件test.ctl的内容- The format for executing this file with SQL Loader is:- SQLLDR control= Be sure to substitute your- version of SQL LOADER and the filename for this file.LOAD DATAINFILE *BADFILE C:Documents and SettingsJackey桌面WMCOUNTRY.BADDISCARDFILE C:Documents and SettingsJackey桌面WMCOUNTRY.DSCINSERT INTO TABLE EMCCOUNTRYFields terminated by ; Optionally enclosed by (COUNTRYID NULLIF (COUNTRYID=NULL),COUNTRYCODE,COUNTRYNAME,CONTINENTID NULLIF (CONTINENTID=NULL),MAPID NULLIF (MAPID=NULL),CREATETIME DATE MM/DD/YYYY HH24:MI:SS NULLIF (CREATETIME=NULL),LASTMODIFIEDTIME DATE MM/DD/YYYY HH24:MI:SS NULLIF (LASTMODIFIEDTIME=NULL)BEGINDATA1;JP;Japan;1;9;09/16/2004 16:31:32;NULL2;CN;China;1;10;09/16/2004 16:31:32;NULL3;IN;India;1;11;09/16/2004 16:31:32;NULL4;AU;Australia;6;12;09/16/2004 16:31:32;NULL5;CA;Canada;4;13;09/16/2004 16:31:32;NULL6;US;United States;4;14;09/16/2004 16:31:32;NULL7;MX;Mexico;4;15;09/16/2004 16:31:32;NULL8;GB;United Kingdom;3;16;09/16/2004 16:31:32;NULL9;DE;Germany;3;17;09/16/2004 16:31:32;NULL10;FR;France;3;18;09/16/2004 16:31:32;NULL11;IT;Italy;3;19;09/16/2004 16:31:32;NULL12;ES;Spain;3;20;09/16/2004 16:31:32;NULL13;FI;Finland;3;21;09/16/2004 16:31:32;NULL14;SE;Sweden;3;22;09/16/2004 16:31:32;NULL15;IE;Ireland;3;23;09/16/2004 16:31:32;NULL16;NL;Netherlands;3;24;09/16/2004 16:31:32;NULL17;DK;Denmark;3;25;09/16/2004 16:31:32;NULL18;BR;Brazil;5;85;09/30/2004 11:25:43;NULL19;KR;Korea, Republic of;1;88;09/30/2004 11:25:43;NULL20;NZ;New Zealand;6;89;09/30/2004 11:25:43;NULL21;BE;Belgium;3;79;09/30/2004 11:25:43;NULL22;AT;Austria;3;78;09/30/2004 11:25:43;NULL23;NO;Norway;3;82;09/30/2004 11:25:43;NULL24;LU;Luxembourg;3;81;09/30/2004 11:25:43;NULL25;PT;Portugal;3;83;09/30/2004 11:25:43;NULL26;GR;Greece;3;80;09/30/2004 11:25:43;NULL27;IL;Israel;1;86;09/30/2004 11:25:43;NULL28;CH;Switzerland;3;84;09/30/2004 11:25:43;NULL29;A1;Anonymous Proxy;0;0;09/30/2004 11:25:43;NULL30;A2;Satellite Provider;0;0;09/30/2004 11:25:43;NULL31;AD;Andorra;3;0;09/30/2004 11:25:43;NULL32;AE;United Arab Emirates;1;0;09/30/2004 11:25:43;NULL33;AF;Afghanistan;1;0;09/30/2004 11:25:43;NULL34;AG;Antigua and Barbuda;7;0;09/30/2004 11:25:43;NULL35;AI;Anguilla;7;0;09/30/2004 11:25:43;NULL36;AL;Albania;3;0;09/30/2004 11:25:43;NULL37;AM;armenia;3;0;09/30/2004 11:25:43;NULL38;AN;Netherlands Antilles;3;0;09/30/2004 11:25:43;NULL39;AO;Angola;2;0;09/30/2004 11:25:43;NULL40;AP;Asia/Pacific Region;2;0;09/30/2004 11:25:43;NULL41;AQ;Antarctica;8;0;09/30/2004 11:25:43;NULL42;AR;Argentina;5;0;09/30/2004 11:25:43;NULL43;AS;American Samoa;6;0;09/30/2004 11:25:43;NULL44;AW;Aruba;5;0;09/30/2004 11:25:43;NULL45;AZ;Azerbaijan;1;0;09/30/2004 11:25:43;NULL46;BA;Bosnia and Herzegovina;3;0;09/30/2004 11:25:43;NULL47;BB;Barbados;5;0;09/30/2004 11:25:43;NULL48;BD;Bangladesh;1;0;09/30/2004 11:25:43;NULL49;BF;Burkina Faso;2;0;09/30/2004 11:25:43;NULL50;BG;Bulgaria;3;0;09/30/2004 11:25:43;NULL51;BH;Bahrain;1;0;09/30/2004 11:25:43;NULL52;BI;Burundi;2;0;09/30/2004 11:25:43;NULL53;BJ;Benin;2;0;09/30/2004 11:25:43;NULL54;BM;Bermuda;4;0;09/30/2004 11:25:43;NULL55;BN;Brunei Darussalam;1;0;09/30/2004 11:25:43;NULL56;BO;Bolivia;5;0;09/30/2004 11:25:43;NULL57;BS;Bahamas;7;0;09/30/2004 11:25:43;NULL58;BT;Bhutan;1;0;09/30/2004 11:25:43;NULL59;BV;Bouvet Island;5;0;09/30/2004 11:25:43;NULL60;BW;Botswana;2;0;09/30/2004 11:25:43;NULL61;BY;Belarus;3;0;09/30/2004 11:25:43;NULL2、执行导入命令C:sqlldr userid=system/manager control=test.ctpart iiSQL*Loader是Oracle数据库导入外部数据的一个工具.它和DB2的Load工具相似,但有更多的选择，它支持变化的加载模式，可选的加载及多表加载. 如何使用 SQL*Loader 工具我们可以用Oracle的sqlldr工具来导入数据。例如:sqlldr scott/tiger control=loader.ctl控制文件(loader.ctl) 将加载一个外部数据文件（含分隔符). loader.ctl如下:load datainfile c:datamydata.csvinto table empfields terminated by , optionally enclosed by ( empno, empname, sal, deptno )mydata.csv 如下:10001,Scott Tiger, 1000, 4010002,Frank Naude, 500, 20下面是一个指定记录长度的示例控制文件。* 代表数据文件与此文件同名，即在后面使用BEGINDATA段来标识数据。load datainfile *replaceinto table departments( dept position (02:05) char(4),deptname position (08:27) char(20)begindataCOSC COMPUTER SCIENCEENGL ENGLISH LITERATUREMATH MATHEMATICSPOLY POLITICAL SCIENCEUnloader这样的工具Oracle 没有提供将数据导出到一个文件的工具。但是，我们可以用SQL*Plus的select 及 format 数据来输出到一个文件：set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool onspool oradata.txtselect col1 | , | col2 | , | col3from tab1where col2 = XYZ;spool off另外，也可以使用使用 UTL_FILE PL/SQL 包处理:rem Remember to update initSID.ora, utl_file_dir=c:oradata parameterdeclarefp utl_file.file_type;beginfp := utl_file.fopen(c:oradata,tab1.txt,w);utl_file.putf(fp, %s, %sn, TextField, 55);utl_file.fclose(fp);end;/当然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。加载可变长度或指定长度的记录如：LOAD DATAINFILE *INTO TABLE load_delimited_dataFIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY TRAILING NULLCOLS( data1,data2)BEGINDATA11111,AAAAAAAAAA22222,A,B,C,D,下面是导入固定位置（固定长度）数据示例：LOAD DATAINFILE *INTO TABLE load_positional_data( data1 POSITION(1:5),data2 POSITION(6:15)BEGINDATA11111AAAAAAAAAA22222BBBBBBBBBB跳过数据行：可以用 SKIP n 关键字来指定导入时可以跳过多少行数据。如：LOAD DATAINFILE *INTO TABLE load_positional_dataSKIP 5( data1 POSITION(1:5),data2 POSITION(6:15)BEGINDATA11111AAAAAAAAAA22222BBBBBBBBBB导入数据时修改数据：在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 direct导入方式.如：LOAD DATAINFILE *INTO TABLE modified_data( rec_no my_db_sequence.nextval,region CONSTANT 31,time_loaded to_char(SYSDATE, HH24:MI),data1 POSITION(1:5) :data1/100,data2 POSITION(6:15) upper(:data2),data3 POSITION(16:22)to_date(:data3, YYMMDD)BEGINDATA11111AAAAAAAAAA99120122222BBBBBBBBBB990112LOAD DATAINFILE mail_orders.txtBADFILE bad_orders.txtAPPENDINTO TABLE mailing_listFIELDS TERMINATED BY ,( addr,city,state,zipcode,mailing_addr decode(:mailing_addr, null, :addr, :mailing_addr),mailing_city decode(:mailing_city, null, :city, :mailing_city),mailing_state)将数据导入多个表：如:LO