sqlldr的介绍.doc

sqlldr的介绍SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件（数据）迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。sqlldr的执行(在UNIX下用shell来调用)$ORACLE_HOME/bin/sqlldr dwh/cognosORA8 control=./tmp/load.ctl $ORACLE_HOME/bin/sqlldr dwh/cognosORA8 control=./tmp/load.ctl direct=true log=.制定控制文件load.ctl1、控制文件标识2、要输入的数据文件名为test.txt3、向表test中（追加）记录4、指定分隔符load datainfile /query5/Ascential/data/month/mgmid.200304Minto table DC_RPT_T_MGMID_200304M_30 append(APPEND为追加方式，或REPLACE)fields terminated by ,(userid,svcnum,brand,svcPlan,busist,hvcFlag,mntFlag,userYear,joinMonth,callfee,callfeefav,tollfee,tollfeefav,calltimes,callduration,billduration,tollduration,totalFee,groupID) 导入方式* 以下是4种装入表的方式 APPEND / 原先的表有数据 就加在后面 INSERT / 装载空表 如果原先的表有数据 sqlloader会停止 默认值 REPLACE / 原先的表有数据 原先的数据会全部删除 TRUNCATE / 指定的内容和replace的相同 会用truncate语句删除现存数据导入日期型字段LOAD DATAINFILE zlx.TXTappend INTO TABLE zlxFIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY (ID,L,F,M,DATE1 date dd-mm-yyyy)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) -定义列对应顺序 2.还有一种方法可以把EXCEL文件另存为CSV（逗号分隔）(*.csv),控制文件就改为用逗号分隔 LOAD DATA INFILE d:car.csv APPEND INTO TABLE t_car_temp FIELDS TERMINATED BY ,(phoneno,vip_car)在控制文件中直接导入数据1、控制文件test.ctl的内容LOAD DATAINFILE *BADFILE C:Documents and SettingsJackey桌面WMCOUNTRY.BADINSERT INTO TABLE EMCCOUNTRYFields terminated by ; Optionally enclosed by (COUNTRYID,COUNTRYCODE,COUNTRYNAME,CONTINENTID ,MAPID ,CREATETIME DATE MM/DD/YYYY HH24:MI:SSLASTMODIFIEDTIME DATE MM/DD/YYYY HH24:MI:SS)BEGINDATA1;JP;Japan;1;9;09/16/2004 16:31:32;2;CN;China;1;10;09/16/2004 16:31:32;3;IN;India;1;11;09/16/2004 16:31:32;4;AU;Australia;6;12;09/16/2004 16:31:32;5;CA;Canada;4;13;09/16/2004 16:31:32;6;US;United States;4;14;09/16/2004 16:31:32;7;MX;Mexico;4;15;09/16/2004 16:31:32;8;GB;United Kingdom;3;16;09/16/2004 16:31:32;9;DE;Germany;3;17;09/16/2004 16:31:32;10;FR;France;3;18;09/16/2004 16:31:32;11;IT;Italy;3;19;09/16/2004 16:31:32;12;ES;Spain;3;20;09/16/2004 16:31:32;13;FI;Finland;3;21;09/16/2004 16:31:32;14;SE;Sweden;3;22;09/16/2004 16:31:32;15;IE;Ireland;3;23;09/16/2004 16:31:32;16;NL;Netherlands;3;24;09/16/2004 16:31:32;17;DK;Denmark;3;25;09/16/2004 16:31:32;18;BR;Brazil;5;85;09/30/2004 11:25:43;19;KR;Korea, Republic of;1;88;09/30/2004 11:25:43;20;NZ;New Zealand;6;89;09/30/2004 11:25:43;21;BE;Belgium;3;79;09/30/2004 11:25:43;22;AT;Austria;3;78;09/30/2004 11:25:43;23;NO;Norway;3;82;09/30/2004 11:25:43;24;LU;Luxembourg;3;81;09/30/2004 11:25:43;25;PT;Portugal;3;83;09/30/2004 11:25:43;26;GR;Greece;3;80;09/30/2004 11:25:43;27;IL;Israel;1;86;09/30/2004 11:25:43;28;CH;Switzerland;3;84;09/30/2004 11:25:43;29;A1;Anonymous Proxy;0;0;09/30/2004 11:25:43;30;A2;Satellite Provider;0;0;09/30/2004 11:25:43;31;AD;Andorra;3;0;09/30/2004 11:25:43;32;AE;United Arab Emirates;1;0;09/30/2004 11:25:43;33;AF;Afghanistan;1;0;09/30/2004 11:25:43;34;AG;Antigua and Barbuda;7;0;09/30/2004 11:25:43;35;AI;Anguilla;7;0;09/30/2004 11:25:43;36;AL;Albania;3;0;09/30/2004 11:25:43;37;AM;Armenia;3;0;09/30/2004 11:25:43;38;AN;Netherlands Antilles;3;0;09/30/2004 11:25:43;39;AO;Angola;2;0;09/30/2004 11:25:43;40;AP;Asia/Pacific Region;2;0;09/30/2004 11:25:43;41;AQ;Antarctica;8;0;09/30/2004 11:25:43;42;AR;Argentina;5;0;09/30/2004 11:25:43;43;AS;American Samoa;6;0;09/30/2004 11:25:43;44;AW;Aruba;5;0;09/30/2004 11:25:43;45;AZ;Azerbaijan;1;0;09/30/2004 11:25:43;46;BA;Bosnia and Herzegovina;3;0;09/30/2004 11:25:43;47;BB;Barbados;5;0;09/30/2004 11:25:43;48;BD;Bangladesh;1;0;09/30/2004 11:25:43;49;BF;Burkina Faso;2;0;09/30/2004 11:25:43;50;BG;Bulgaria;3;0;09/30/2004 11:25:43;51;BH;Bahrain;1;0;09/30/2004 11:25:43;52;BI;Burundi;2;0;09/30/2004 11:25:43;53;BJ;Benin;2;0;09/30/2004 11:25:43;54;BM;Bermuda;4;0;09/30/2004 11:25:43;55;BN;Brunei Darussalam;1;0;09/30/2004 11:25:43;56;BO;Bolivia;5;0;09/30/2004 11:25:43;57;BS;Bahamas;7;0;09/30/2004 11:25:43;58;BT;Bhutan;1;0;09/30/2004 11:25:43;59;BV;Bouvet Island;5;0;09/30/2004 11:25:43;60;BW;Botswana;2;0;09/30/2004 11:25:43;61;BY;Belarus;3;0;09/30/2004 11:25:43;2、执行导入命令C:sqlldr userid=system/manager control=test.ctl有效的关键字: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: 命令行参数可以由位置或关键字指定。前者的例子是 sqlload scott/tiger foo; 后一种情况的一个示例是 sqlldr control=foouserid=scott/tiger.位置指定参数的时间必须早于但不可迟于由关键字指定的参数。例如,允许 sqlldr scott/tiger control=foo logfile=log, 但是不允许qlldr scott/tiger control=foo log, 即使参数 log 的位置正确。optionally enclosed by 将数据文件中的数据字段含有的引号“ ”去掉，如果没有这段话，将会把“ ”和字符合在一起导入数据库表中。指定不装载那一列 LOAD DATA INFILE * INTO TABLE DEPT REPLACE FIELDS TERMINATED BY , OPTIONALLY ENCLOSED BY (DEPTNO, FILLER_1 FILLER, / 下面的 Something Not To Be Loaded 将不会被装载 DNAME, LOC ) BEGINDATA 20,Something Not To Be Loaded,Accounting,Virginia,USAposition的列子 LOAD DATA INFILE * INTO TABLE DEPT REPLACE (DEPTNO position(1:2), DNAME position(*:16), / 这个字段的开始位置在前一字段的结束位置 LOC position(*:29), ENTIRE_LINE position(1:29) ) BEGINDATA 10Accounting Virginia,USA合并多行记录为一行记录 LOAD DATA INFILE * concatenate 3 / 通过关键字concatenate 把几行的记录看成一行记录 INTO TABLE DEPT replace FIELDS TERMINATED BY , (DEPTNO, DNAME upper(:dname), LOC upper(:loc), LAST_UPDATED date dd/mm/yyyy ) BEGINDATA 10,Sales, / 其实这3行看成一行 10,Sales,Virginia,1/5/2000 Virginia, 1/5/2000 / 这列子用 continueif list=, 也可以 告