sqlldr使用简介
来源：互联网 发布：淘宝站外推广方式 编辑：程序博客网 时间：2024/06/05 16:06
--------------------------------------------------------------------------------SQL*Loader: Release 11.2.0.1.0 - Production on 星期六 8月 4 12:54:31 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 用法: SQLLDR keyword=value [,keyword=value,...] 有效的关键字: userid -- ORACLE 用户名/口令 control -- 控制文件名 log -- 日志文件名 bad -- 错误文件名 data -- 数据文件名 discard -- 废弃文件名discardmax -- 允许废弃的文件的数目 (全部默认) skip -- 要跳过的逻辑记录的数目 (默认 0) load -- 要加载的逻辑记录的数目 (全部默认) errors -- 允许的错误的数目 (默认 50) rows -- 常规路径绑定数组中或直接路径保存数据间的行数 (默认: 常规路径 64, 所有直接路径) bindsize -- 常规路径绑定数组的大小 (以字节计) (默认 256000) silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区) direct -- 使用直接路径 (默认 FALSE) parfile -- 参数文件: 包含参数说明的文件的名称 parallel -- 执行并行加载 (默认 FALSE) file -- 要从以下对象中分配区的文件 skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区 (默认 FALSE)skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用 (默认 FALSE)commit_discontinued -- 提交加载中断时已加载的行 (默认 FALSE) readsize -- 读取缓冲区的大小 (默认 1048576)external_table -- 使用外部表进行加载; NOT_USED, GENERATE_ONLY, EXECUTE (默认 NOT_USED)columnarrayrows -- 直接路径列数组的行数 (默认 5000)streamsize -- 直接路径流缓冲区的大小 (以字节计) (默认 256000)multithreading -- 在直接路径中使用多线程 resumable -- 启用或禁用当前的可恢复会话 (默认 FALSE)resumable_name -- 有助于标识可恢复语句的文本字符串resumable_timeout -- RESUMABLE 的等待时间 (以秒计) (默认 7200)date_cache -- 日期转换高速缓存的大小 (以条目计) (默认 1000)no_index_errors -- 出现任何索引错误时中止加载 (默认 FALSE) PLEASE NOTE: 命令行参数可以由位置或关键字指定。前者的例子是 'sqlldrscott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foouserid=scott/tiger'。位置指定参数的时间必须早于但不可迟于由关键字指定的参数。例如,允许 'sqlldr scott/tiger control=foo logfile=log', 但是不允许 'sqlldr scott/tiger control=foo log', 即使参数 'log' 的位置正确。--------------------------------------------------------------------------------
**控制文件描述--------------------------------------------------------------------------------LOAD DATA INFILE 't.dat' // 要导入的文件 // INFILE 'tt.date' // 导入多个文件 // INFILE * // 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容, *和't.dat'不能同时存在INTO TABLE table_name // 指定装入的表 BADFILE 'c:bad.txt' // 指定坏文件地址 ************* 以下是4种装入表的方式 APPEND // 原先的表有数据 就加在后面 // INSERT // 装载空表 如果原先的表有数据 sqlloader会停止 默认值 // REPLACE // 原先的表有数据 原先的数据会全部删除 // TRUNCATE // 指定的内容和replace的相同 会用truncate语句删除现存数据 ************* 指定的TERMINATED可以在表的开头 也可在表的内部字段部分 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' // 装载这种数据: 10,lg,"""lg""","lg,lg" // 在表中结果: 10 lg "lg" lg,lg // TERMINATED BY X '09' // 以十六进制格式 '09' 表示的 // TERMINATED BY WRITESPACE // 装载这种数据: 10 lg lg // OPTIONALLY ENCLOSED BY '"' //表明每个字段可以用双引号括起TRAILING NULLCOLS ************* 表的字段没有对应的值时允许为空 ************* 下面是表的字段 ( col_1 , col_2 ,col_filler FILLER // FILLER 关键字 此列的数值不会被装载 // 如: lg,lg,not 结果 lg lg ) // 当没声明FIELDS TERMINATED BY ',' 时 // ( // col_1 [interger external] TERMINATED BY ',' , // col_2 [date "dd-mon-yyy"] TERMINATED BY ',' , // col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg' // ) // 当没声明FIELDS TERMINATED BY ','用位置告诉字段装载数据 // ( // col_1 position(1:2), // col_2 position(3:10), // col_3 position(*:16), // 这个字段的开始位置在前一字段的结束位置 // col_4 position(1:16), // col_5 position(3:10) char(8) // 指定字段的类型 // ) BEGINDATA // 对应开始的 INFILE * 要导入的内容就在control文件里 10,Sql,what 20,lg,show --------------------------------------------------------------------------------
**实际例子---------------------------------------------------------------------------------- Why do I receive "exceeds maximum length" in my log file?LOAD DATAINFILE *INTO TABLE DEPTFIELDS TERMINATED BY ','(DEPTNO, DNAME char(1000), LOC )BEGINDATA10,Sales,Virginia20,Accounting,Virginia30,Consulting,Virginia40,Finance more text more text more text more ... <repeated many times> ... more text,Virginia---------------------------------------------------------------- How Do I Load Delimited Data? quotes exampleLOAD DATAINFILE *INTO TABLE DEPTFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'(DEPTNO, DNAME, LOC )BEGINDATA10,Sales,"Virginia,USA"20,Accounting,"Va, ""USA"""30,Consulting,Virginia40,Finance,Virginia---------------------------------------------------------------- How Do I Load Delimited Data? tabs exampleLOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY WHITESPACE(DEPTNO, DNAME, LOC)BEGINDATA10SalesVirginia---------------------------------------------------------------- How Do I Load Delimited Data? X'09' exampleLOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY X'09'(DEPTNO, DNAME, LOC )BEGINDATA10SalesVirginia---------------------------------------------------------------- How Do I Load Delimited Data? X'09' skip column exampleLOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY x'09'(DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC)BEGINDATA10SalesVirginia---------------------------------------------------------------- How Do I Load Fixed Format Data?LOAD DATAINFILE *INTO TABLE DEPTREPLACE( DEPTNO position(1:2), DNAME position(3:16), LOC position(17:29))BEGINDATA10Accounting Virginia,USA---------------------------------------------------------------- How Do I Load Fixed Format Data? with entire_line, relative offset, example 1LOAD DATAINFILE *INTO TABLE DEPTREPLACE( DEPTNO position(1:2), DNAME position(*:16), LOC position(*:29), ENTIRE_LINE position(1:29))BEGINDATA10Accounting Virginia,USA---------------------------------------------------------------- How Do I Load Fixed Format Data? with entire_line, relative offset, example 2LOAD DATAINFILE *INTO TABLE DEPTREPLACE( DEPTNO position(1) char(2), DNAME position(*) char(14), LOC position(*) char(13), ENTIRE_LINE position(1) char(29))BEGINDATA10Accounting Virginia,USA---------------------------------------------------------------- How Do I Load Dates?LOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','(DEPTNO, DNAME, LOC, LAST_UPDATED date 'dd/mm/yyyy')BEGINDATA10,Sales,Virginia,1/5/201420,Accounting,Virginia,21/6/201430,Consulting,Virginia,5/1/201340,Finance,Virginia,15/3/2014---------------------------------------------------------------- How Do I Load Data Using Functions?LOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','(DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED date 'dd/mm/yyyy')BEGINDATA10,Sales,Virginia,1/5/201420,Accounting,Virginia,21/6/201430,Consulting,Virginia,5/1/201340,Finance,Virginia,15/3/2014---------------------------------------------------------------- How Do I Load Data Using Functions?, using trailing nullcolsLOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','TRAILING NULLCOLS(DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED date 'dd/mm/yyyy', ENTIRE_LINE ":deptno||:dname||:loc||:last_updated")BEGINDATA10,Sales,Virginia,1/5/201420,Accounting,Virginia,21/6/201430,Consulting,Virginia,5/1/201340,Finance,Virginia,15/3/2014---------------------------------------------------------------- How Do I Load Data Using Functions?, CASE example LOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','TRAILING NULLCOLS(DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", LAST_UPDATED"case when length(:last_updated) > 9 then to_date(:last_updated,'hh24:mi:ss dd/mm/yyyy') when instr(:last_updated,':') > 0 then to_date(:last_updated,'hh24:mi:ss') else to_date(:last_updated,'dd/mm/yyyy') end")BEGINDATA10,Sales,Virginia,12:03:03 17/10/201420,Accounting,Virginia,02:23:5430,Consulting,Virginia,01:24:00 21/10/201440,Finance,Virginia,17/8/2014---------------------------------------------------------------- How Do I Load Data with Embedded Newlines? Use a Character Other Than a NewlineLOAD DATAINFILE *INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','TRAILING NULLCOLS(DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS "replace(:comments,'\\n',chr(10))")BEGINDATA10,Sales,Virginia,This is the Sales\nOffice in Virginia20,Accounting,Virginia,This is the Accounting\nOffice in Virginia30,Consulting,Virginia,This is the Consulting\nOffice in Virginia40,Finance,Virginia,This is the Finance\nOffice in Virginia---------------------------------------------------------------- How Do I Load Data with Embedded Newlines? Use the FIX Attribute LOAD DATAINFILE demo.dat "fix 80"INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','TRAILING NULLCOLS(DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS)---------------------------------------------------------------- How Do I Load Data with Embedded Newlines? Use the VAR Attribute LOAD DATAINFILE demo.dat "var 3"INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','TRAILING NULLCOLS(DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS)---------------------------------------------------------------- How Do I Load Data with Embedded Newlines? Use the STR AttributeLOAD DATAINFILE demo.dat "str X'7C0A'"INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ','TRAILING NULLCOLS(DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS)---------------------------------------------------------------- Loading LOB Data via SQLLDR, Loading LOB Data That Is InlineLOAD DATAINFILE demo.dat "str X'7C0A'"INTO TABLE DEPTREPLACEFIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'TRAILING NULLCOLS(DEPTNO, DNAME "upper(:dname)", LOC "upper(:loc)", COMMENTS char(1000000))--------------------------------------------------------------create table lob_demo ( owner varchar2(255), time_stamp date, filename varchar2(255), data blob)/-- Loading LOB Data via SQLLDR, Loading LOB Data That Is Out of LineLOAD DATAINFILE *REPLACEINTO TABLE LOB_DEMO( owner position(14:19), time_stamp position(30:41) date "Mon DD HH24:MI", filename position(43:100), data LOBFILE(filename) TERMINATED BY EOF)BEGINDATA-rwxr-xr-x 1 oracle dba 14889 Jul 22 22:01 demo1.log_xt-rwxr-xr-x 1 oracle dba 123 Jul 22 20:07 demo2.ctl-rwxr-xr-x 1 oracle dba 712 Jul 23 12:11 demo.bad-rwxr-xr-x 1 oracle dba 8136 Mar 9 12:36 demo.control_files-rwxr-xr-x 1 oracle dba 825 Jul 23 12:26 demo.ctl-rwxr-xr-x 1 oracle dba 1681 Jul 23 12:26 demo.log-rw-r----- 1 oracle dba 118 Jul 23 12:52 dl.sql-rwxr-xr-x 1 oracle dba 127 Jul 23 12:05 lob_demo.sql-rwxr-xr-x 1 oracle dba 171 Mar 10 13:53 p.bsh-rwxr-xr-x 1 oracle dba 327 Mar 10 11:10 prime.bsh-rwxr-xr-x 1 oracle dba 24 Mar 6 12:09 run_df.sh---------------------------------------------------------------- Loading LOB Data via SQLLDR, Loading LOB Data into Object Columns--------------------------------------------------------------LOAD DATAINFILE *INTO TABLE image_loadREPLACEFIELDS TERMINATED BY ','( ID, NAME, file_name FILLER, IMAGE column object ( SOURCE column object ( LOCALDATA LOBFILE (file_name) TERMINATED BY EOF NULLIF file_name = 'NONE' ) ))BEGINDATA1,icons,icons.gif--------------------------------------------------------------------------------
--参考来源《Oracle编程艺术深入理解数据库体系结构（第三版）》
阅读全文
0 0
- sqlldr使用简介
- sqlldr简介
- SQLLDR 简介
- sqlldr使用
- sqlldr使用
- sqlldr 使用
- sqlldr使用示例
- sqlldr使用小结
- sqlldr使用小结
- 使用SQLLDR导入数据
- Sqlldr使用介绍
- Oracle sqlldr使用总结
- sqlldr的使用
- oracle sqlldr使用
- Oracle中sqlldr使用
- sqlldr使用小结
- 简述sqlldr的使用
- 简述sqlldr的使用
- 如何导出Office 365用户
- 设计模式（八）：外观模式
- JAVA 避免Java应用中NullPointerException的技巧和最佳实践
- 文章标题
- unit7-samba
- sqlldr使用简介
- kvm内存优化--KSM
- 【剑指offer-解题系列（53）】表示数值的字符串
- 使用echarts显示图表
- leetcode15: 3Sum
- 自定义广播
- 深度学习 3 循环神经网络 RNN Recurrent Neural Networks
- Redis的五种存储类型和其应用场景
- 从高考到程序员
