sqlldr使用简介

--------------------------------------------------------------------------------
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: 命令行参数可以由位置或关键字指定
。前者的例子是 'sqlldr
scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo
userid=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 DATA
INFILE *
INTO TABLE DEPT
FIELDS TERMINATED BY ','
(DEPTNO, DNAME char(1000), LOC )
BEGINDATA
10,Sales,Virginia
20,Accounting,Virginia
30,Consulting,Virginia
40,Finance more text more text more text more ... <repeated many times> ... more text,Virginia
--------------------------------------------------------------
-- How Do I Load Delimited Data? quotes example
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,Virginia
--------------------------------------------------------------
-- How Do I Load Delimited Data? tabs example
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY WHITESPACE
(DEPTNO, DNAME, LOC)
BEGINDATA
10		Sales		Virginia
--------------------------------------------------------------
-- How Do I Load Delimited Data? X'09' example
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY X'09'
(DEPTNO, DNAME, LOC )
BEGINDATA
10		Sales		Virginia
--------------------------------------------------------------
-- How Do I Load Delimited Data? X'09' skip column example
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY x'09'
(DEPTNO, dummy1 filler, DNAME, dummy2 filler, LOC)
BEGINDATA
10		Sales		Virginia
--------------------------------------------------------------
-- How Do I Load Fixed Format Data?
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO position(1:2),
  DNAME  position(3:16),
  LOC    position(17:29)
)
BEGINDATA
10Accounting    Virginia,USA
--------------------------------------------------------------
-- How Do I Load Fixed Format Data? with entire_line, relative offset, example 1
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
--------------------------------------------------------------
-- How Do I Load Fixed Format Data? with entire_line, relative offset, example 2
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
( DEPTNO      position(1) char(2),
  DNAME       position(*) char(14),
  LOC         position(*) char(13),
  ENTIRE_LINE position(1) char(29)
)
BEGINDATA
10Accounting    Virginia,USA
--------------------------------------------------------------
-- How Do I Load Dates?
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
  DNAME,
  LOC,
  LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales,Virginia,1/5/2014
20,Accounting,Virginia,21/6/2014
30,Consulting,Virginia,5/1/2013
40,Finance,Virginia,15/3/2014
--------------------------------------------------------------
-- How Do I Load Data Using Functions?
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
(DEPTNO,
  DNAME        "upper(:dname)",
  LOC          "upper(:loc)",
  LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales,Virginia,1/5/2014
20,Accounting,Virginia,21/6/2014
30,Consulting,Virginia,5/1/2013
40,Finance,Virginia,15/3/2014
--------------------------------------------------------------
-- How Do I Load Data Using Functions?, using trailing nullcols
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
  DNAME        "upper(:dname)",
  LOC          "upper(:loc)",
  LAST_UPDATED date 'dd/mm/yyyy',
  ENTIRE_LINE  ":deptno||:dname||:loc||:last_updated"
)
BEGINDATA
10,Sales,Virginia,1/5/2014
20,Accounting,Virginia,21/6/2014
30,Consulting,Virginia,5/1/2013
40,Finance,Virginia,15/3/2014
--------------------------------------------------------------
-- How Do I Load Data Using Functions?, CASE example 
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS 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"
)
BEGINDATA
10,Sales,Virginia,12:03:03 17/10/2014
20,Accounting,Virginia,02:23:54
30,Consulting,Virginia,01:24:00 21/10/2014
40,Finance,Virginia,17/8/2014
--------------------------------------------------------------
-- How Do I Load Data with Embedded Newlines? Use a Character Other Than a Newline
LOAD DATA
INFILE *
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
  DNAME        "upper(:dname)",
  LOC          "upper(:loc)",
  COMMENTS     "replace(:comments,'\\n',chr(10))"
)
BEGINDATA
10,Sales,Virginia,This is the Sales\nOffice in Virginia
20,Accounting,Virginia,This is the Accounting\nOffice in Virginia
30,Consulting,Virginia,This is the Consulting\nOffice in Virginia
40,Finance,Virginia,This is the Finance\nOffice in Virginia
--------------------------------------------------------------
-- How Do I Load Data with Embedded Newlines? Use the FIX Attribute 
LOAD DATA
INFILE demo.dat "fix 80"
INTO TABLE DEPT
REPLACE
FIELDS 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 DATA
INFILE demo.dat "var 3"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
  DNAME        "upper(:dname)",
  LOC          "upper(:loc)",
  COMMENTS
)
--------------------------------------------------------------
-- How Do I Load Data with Embedded Newlines? Use the STR Attribute
LOAD DATA
INFILE demo.dat "str X'7C0A'"
INTO TABLE DEPT
REPLACE
FIELDS TERMINATED BY ','
TRAILING NULLCOLS
(DEPTNO,
  DNAME        "upper(:dname)",
  LOC          "upper(:loc)",
  COMMENTS
)
--------------------------------------------------------------
-- Loading LOB Data via SQLLDR, Loading LOB Data That Is Inline
LOAD DATA
INFILE demo.dat "str X'7C0A'"
INTO TABLE DEPT
REPLACE
FIELDS 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 Line
LOAD DATA
INFILE *
REPLACE
INTO 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 DATA
INFILE *
INTO TABLE image_load
REPLACE
FIELDS TERMINATED BY ','
( ID,
  NAME,
  file_name FILLER,
  IMAGE column object
  (
    SOURCE column object
    (
      LOCALDATA LOBFILE (file_name) TERMINATED BY EOF
                NULLIF file_name = 'NONE'
    )
  )
)
BEGINDATA
1,icons,icons.gif
--------------------------------------------------------------------------------

--参考来源《Oracle编程艺术深入理解 数据库 体系结构（第三版）》