oracle sql loader全攻略.doc

oracle sql loader全攻略 part i一：sql loader 的特点oracle自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。但是每个工具都有自己的 特点。比如说exp和imp可以对数据库中的数据进行导出和导出的工作，是一种很好的数据库备份和恢复的工具，因此主要用在数据库的热备份和恢复方面。有着速度快，使用简单，快捷的优点；同时也有一些缺点，比如在不同版本数据库之间的导出、导入的过程之中，总会出现这样或者那样的问题，这个也许是 oracle公司自己产品的兼容性的问题吧。sql loader 工具却没有这方面的问题，它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中，是一种在不同数据库之间进行数据迁移的非常方便而且通用的 工具。缺点就速度比较慢，另外对blob等类型的数据就有点麻烦了。二：sql loader 的帮助 C:sqlldrSQL*Loader: Release 9.2.0.1.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.txt append 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.bad 2.还有一种方法可以把EXCEL文件另存为CSV（逗号分隔）(*.csv),控制文件就改为用 逗号分隔 LOAD DATA INFILE d:car.csv APPEND INTO TABLE t_car_temp FIELDS 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.ctl part 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)begindata COSC 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 on spool oradata.txtselect col1 | , | col2 | , | col3 from 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,AAAAAAAAAA 22222,A,B,C,D,下面是导入固定位置（固定长度）数据示例：LOAD DATAINFILE * INTO TABLE load_positional_data( data1 POSITION(1:5),data2 POSITION(6:15)BEGINDATA11111AAAAAAAAAA 22222BBBBBBBBBB跳过数据行：可以用 SKIP n 关键字来指定导入时可以跳过多少行数据。如： LOAD DATAINFILE *INTO TABLE load_positional_dataSKIP 5 ( data1 POSITION(1:5),data2 POSITION(6:15)BEGINDATA 11111AAAAAAAAAA22222BBBBBBBBBB导入数据时修改数据：在导入数据到数据库时，可以 修改数据。注意，这仅适合于常规导入，并不适合 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) )BEGINDATA11111AAAAAAAAAA99120122222BBBBBBBBBB990112 LOAD DATAINFILE mail_orders.txtBADFILE bad_orders.txt APPENDINTO 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 )将数据导入多个表：如:LOAD DATAINFILE *REPLACE INTO TABLE empWHEN empno != ( empno POSITION(1:4) INTEGER EXTERNAL,ename POSITION(6:15) CHAR,deptno POSITION(17:18) CHAR,mgr POSITION(20:23) INTEGER EXTERNAL) INTO TABLE projWHEN projno != ( projno POSITION(25:27) INTEGER EXTERNAL,empno POSITION(1:4) INTEGER EXTERNAL) 导入选定的记录：如下例： (01) 代表第一个字符, (30:37) 代表30到37之间的字符:LOAD DATA INFILE mydata.dat BADFILE mydata.bad DISCARDFILE mydata.dis APPENDINTO TABLE my_selective_tableWHEN (01) H and (01) T and (30:37) = 19991217(region CONSTANT 31,service_key POSITION(01:11) INTEGER EXTERNAL, call_b_no POSITION(12:29) CHAR)导入时跳过某些字段：可用 POSTION(x:y) 来分隔数据. 在Oracle8i中可以通过指定 FILLER 字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段.如：LOAD DATATRUNCATE INTO TABLE T1 FIELDS TERMINATED BY ,( field1,field2 FILLER,field3 )导入多行记录：可以使用下面两个选项之一来实现将多行数据导入为一个记录:CONCATENATE: