sql loader(sqlldr)最基本的使用方法

这里说的是sql*plus界面下的使用,并非TOAD一类工具里的sqlload的使用

使用SQLLDR去导入一段CSV文件里的数据,最关键的是CONTROL File的建立。

以下部分为ctl里的内容:

options (skip=1,rows=128)
LOAD DATA
INFILE "C:/your file name.csv"  --------------你的csv文件的存放路径,尽量简单,容易输入
append
into TABLE MEO_TBL_IEEQP_INFO
FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(                                  ----------------------括号内为你的表里的栏位(column)
SID,
Faa,
Logic_Area,
Category,
Area,
Bay_Location,
PR,
PT_Owner,
EEQ,
EEQ_Owner,
MG,
EQEE_Type,
ID,
Mach_Function,
Vendor,
Agent,
Model,
Phase,
Status,
FdYear,
Actual_Movein_Finish,
Remark,
Actual_Moveout

   )                             ---------------------括号内为你的表里的栏位(column)

 

------------------------------------------------------------------------------------------------------------------------------------------------

下面也贴出一些命令,方便大家查看,也方便自己下次查看:

 

Oracle   SQL   Loader的详细语法   
SQL*LOADER
ORACLE的数据加载工具,通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法,因为它提供了最快速的途径(DIRECTPARALLEL)。现在,我们抛开其理论不谈,用实例来使您快速掌握SQL*LOADER的使用方法。  
   
首先,我们认识一下SQL*LOADER  
   
NT下,SQL*LOADER的命令为SQLLDR,在UNIX下一般为sqlldr/sqlload  
   
如执行:d:/oracle>sqlldr  
  SQL*Loader:   Release   8.1.6.0.0   -   Production   on  
星期二   1   8   11:06:42   2002  
  (c)   Copyright   1999   Oracle   Corporation.   All   rights   reserved.  
 
用法:   SQLLOAD   关键字   =      [,keyword=value,...]  
 
有效的关键字:  
  userid   --   ORACLE   username/password  
  control   --   Control   file   name  
  log   --   Log   file   name  
  bad   --   Bad   file   name  
  data   --   Data   file   name  
  discard   --   Discard   file   name  
  discardmax   --   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(
默认65536)  
  silent   --   Suppress   messages   during   run   (header,feedback,errors,discards,partitions)  
  direct   --   use   direct   path   (
默认FALSE)  
  parfile   --   parameter   file:   name   of   file   that   contains   parameter   specifications  
  parallel   --   do   parallel   load   (
默认FALSE)  
  file   --   File   to   allocate   extents   from  
  skip_unusable_indexes   --   disallow/allow   unusable   indexes   or   index   partitions(
默认FALSE)  
  skip_index_maintenance   --   do   not   maintain   indexes,   mark   affected   indexes   as   unusable(
默认FALSE)  
  commit_discontinued   --   commit   loaded   rows   when   load   is   discontinued(
默认FALSE)  
  readsize   --   Size   of   Read   buffer   (
默认1048576)  
  PLEASE   NOTE:  
命令行参数可以由位置或关键字指定  
 
。前者的例子是   'sqlload  scott/tiger   foo';后者的例子是   'sqlload   control=foo userid=scott/tiger'.位置指定参数的时间必须早于但不可迟于由关键字指定的参数。例如,  
  'SQLLOAD   SCott/tiger   control=foo   logfile=log',  
'不允许                             
sqlload   scott/tiger   control=foo   log',即使允许 参数   'log'   的位置正确。  
  d:/oracle>  
 
我们可以从中看到一些基本的帮助信息,这里,我用到的是中文的WIN2000 ADV SERVER  
 
     我们知道,SQL*LOADER只能导入纯文本,所以我们现在开始以实例来讲解其用法。  
 
     一、已存在数据源result.csv,欲倒入ORACLEFANCY用户下。  
 
       result.csv内容:  
 
     1,默认   Web   站点,192.168.2.254:80:,RUNNING  
 
     2,other,192.168.2.254:80:test.com,STOPPED  
 
     3,third,192.168.2.254:81:thirdabc.com,RUNNING  
 
     从中,我们看出4列,分别以逗号分隔,为变长字符串。  
 
     二、制定控制文件result.ctl  
  result.ctl
内容:  
  load   data  
  infile   'result.csv'  
  into   table   resultxt    
  (resultid   char   terminated   by   ',',  
  website   char   terminated   by   ',',  
  ipport   char   terminated   by   ',',  
  status   char   terminated   by   whitespace)  
 
     说明:  
 
     infile 指数据源文件 这里我们省略了默认的 discardfile   result.dsc   badfile   result.bad  
 
     into   table   resultxt   默认是INSERT,也可以into   table   resultxt   APPEND为追加方式,或REPLACE  
 
     terminated   by   ',' 指用逗号分隔  
 
     terminated   by   whitespace 结尾以空白分隔  
 
     三、此时我们执行加载:  
  D:/>sqlldr   userid=fancy/testpass   control=result.ctl   log=resulthis.out  
  SQL*Loader:   Release   8.1.6.0.0   -   Production   on  
星期二   1   8   10:25:42   2002  
  (c)   Copyright   1999   Oracle   Corporation.   All   rights   reserved.  
  SQL*Loader-941:  
在描述表RESULTXT时出现错误  
  ORA-04043:  
对象   RESULTXT   不存在  
 
     提示出错,因为数据库没有对应的表。  
 
     四、在数据库建立表  
 
    create   table   resultxt  
  (resultid   varchar2(500),  
  website   varchar2(500),  
  ipport   varchar2(500),  
  status   varchar2(500))  
  /  
      五、重新执行加载  
 
     D:/>sqlldr   userid=fancy/k1i7l6l8   control=result.ctl   log=resulthis.out  
  SQL*Loader:   Release   8.1.6.0.0   -   Production   on  
星期二   1   8   10:31:57   2002  
  (c)   Copyright   1999   Oracle   Corporation.   All   rights   reserved.  
 
达到提交点,逻辑记录计数2  
 
达到提交点,逻辑记录计数3  
 
     已经成功!我们可以通过日志文件来分析其过程:resulthis.out内容如下:  
  SQL*Loader:   Release   8.1.6.0.0   -   Production   on  
星期二   1   8   10:31:57   2002  
  (c)   Copyright   1999   Oracle   Corporation.   All   rights   reserved.  
 
控制文件:   result.ctl  
 
数据文件:   result.csv  
 
错误文件:   result.bad  
 
废弃文件:   未作指定  
  :    
  (
可废弃所有记录)  
 
装载数:   ALL  
 
跳过数:   0  
 
允许的错误:   50  
 
绑定数组:   64   行,最大   65536   字节  
 
继续:   未作指定  
 
所用路径:   常规  
 
RESULTXT  
 
已载入从每个逻辑记录  
 
插入选项对此表INSERT生效  
 
列名   位置   长度   中止   包装数据类型  
  ------------------------------   ----------   -----   ----   ----   ---------------------  
  RESULTID   FIRST   *   ,   CHARACTER    
  WEBSITE   NEXT   *   ,   CHARACTER    
  IPPORT   NEXT   *   ,   CHARACTER    
  STATUS   NEXT   *   WHT   CHARACTER    
 
RESULTXT:    
  3  
行载入成功  
 
由于数据错误,   0   行没有载入。  
 
由于所有   WHEN   子句失败,   0   行没有载入。  
 
由于所有字段都为空的,   0   行没有载入。  
 
为结合数组分配的空间:   65016字节(63行)  
 
除绑定数组外的内存空间分配:   0字节  
 
跳过的逻辑记录总数:   0  
 
读取的逻辑记录总数:   3  
 
拒绝的逻辑记录总数:   0  
 
废弃的逻辑记录总数:   0  
 
从星期二   1   08   10:31:57   2002开始运行  
 
在星期二   1   08   10:32:00   2002处运行结束  
 
经过时间为:   00:   00:   02.70  
  CPU  
时间为:   00:   00:   00.10(  
 
     六、并发操作   
  sqlldr   userid=/   control=result1.ctl   direct=true   parallel=true  
  sqlldr   userid=/   control=result2.ctl   direct=true   parallel=true  
  sqlldr   userid=/   control=result2.ctl   direct=true   parallel=true
  
 
当加载大量数据时(大约超过10GB),最好抑制日志的产生:  
 
     SQL>ALTER   TABLE   RESULTXT   nologging;  
 
这样不产生REDO LOG,可以提高效率。然后在CONTROL文件中load   data上面加一行:unrecoverable     此选项必须要与DIRECT共同应用。  
 
 在并发操作时,ORACLE声称可以达到每小时处理100GB数据的能力!其实,估计能到110G就算不错了,开始可用结构 相同的文件,但只有少量数据,成功后开始加载大量数据,这样可以避免时间的浪费。   
 
有关SQLLDR的问题    
 
控制文件:input.ctl,内容如下:    
 
     load   data             --1、控制文件标识    
 
     infile   'test.txt'         --2、要输入的数据文件名为test.txt    
 
     append   into   table   test    --3、向表test中追加记录    
 
     fields   terminated   by   X'09'  --4、字段终止于X'09',是一个制表符(TAB    
 
     (id,username,password,sj)     -----定义列对应顺序    
   
 
其中append为数据装载方式,还有其他选项:    
  a
insert,为缺省方式,在数据装载开始时要求表为空    
  b
append,在表中追加新记录    
  c
replace,删除旧记录,替换成新装载的记录    
  d
truncate,同上      
==================================================================================
 
sql loader可以把一些以文本格式存放的数据顺利的导入到oracle数据库中,
是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。

                                                                           Sqlldr

sql loader可以把一些以文本格式存放的数据顺利的导入到oracle数据库中,
是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。
缺点就速度比较慢,另外对blob等类型的数据就有点麻烦了。
用法: SQLLDR keyword=value [,keyword=value,...]
 
有效的关键字:
    userid -- ORACLE username/password
   control – 控制文件
       log – 记录的日志文件
       bad – 坏数据文件
      data – 数据文件
   discard – 丢弃的数据文件
discardmax – 允许丢弃数据的最大值        (全部默认)
      skip -- Number of logical records to skip  (默认0)
      load -- Number of logical records to load  (全部默认)
    errors – 允许的错误记录数          (默认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)
                每次提交记录的缓冲区的大小(字节为单位,默认256000)
    silent --禁止输出信息 (header,feedback,errors,discards,partitions)
    direct – 使用直通路径方式导入                    (默认FALSE)
   parfile -- parameter file: name of file that contains parameter specifications
  parallel -- 并行导入                   (默认FALSE)
       file -- File to allocate extents from
  与bindsize成对使用,其中较小者会自动调整到较大者
sqlldr先计算单条记录长度,乘以rows,如小于bindsize,不会试图扩张rows以填充bindsize;如超出,则以bindsize为准。
 
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 path
 resumable -- enable or disable resumable for current session(默认FALSE)
resumable_name
           -- text string to help identify resumable statement
resumable_timeout
           -- wait time (in seconds) for RESUMABLE(默认7200)
date_cache -- size (in entries) of date conversion cache(默认1000)
 
注意:有两种方式可以指定命令行参数:通过位置或者通过关键字。前者的例子:'sqlldr scott/tiger foo';
后者的例子:'sqlldr control=foo userid=scott/tiger';
不能前面使用关键字指定后面通过位置制定的混合方式;
比如:'sqlldr scott/tiger control=foo logfile=log' 是允许的,
但'sqlldr scott/tiger control=foo log'不允许。
为清楚起见最好所有命令行参数都用关键字指定。
控制文件:
一个控制命令的脚本文件,通常以ctl结尾,内容如下:
LOAD DATA
INFILE 't.dat'              要导入的文件
// INFILE 'tt.date' 导入多个文件
// INFILE *               表示要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容
 
INTO TABLE table_name    指定装入的表
BADFILE 'c:/bad.txt'    可选,指定坏文件地址,缺省在当前目录下生成与原文件名一致的.bad文件
 
************* 以下是4种装入表的方式
APPEND             原先的表有数据 就加在后面
INSERT             装载空表 如果原先的表有数据 sqlloader会停止 默认值
REPLACE           原先的表有数据 原先的数据会全部删除
TRUNCATE        指定的内容和replace的相同 会用truncate语句删除现存数据
 
************* 指定分隔符
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
// TERMINATED BY WRITESPACE 以空白分割
 
TRAILING NULLCOLS            表的字段没有对应的值时允许为空
 
************* 下面是表的字段
(
col_1 , col_2 ,col_filler FILLER // FILLER 关键字 此列的数值不会被装载
// 如: lg,lg,not 结果 lg lg
)
如果没声明FIELDS TERMINATED BY ',' 时,可以用下面两种方式实现同样功能:
1.为每一列指定分隔符
 (
 col_1 [interger external] TERMINATED BY ',' ,
 col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
 col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'
 )
2.用位置告诉字段装载数据
 (
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

 

 -------------------------------------------------------------------------------------------------

 

 

sqlldr userid=lgone/tiger control=a.ctl

  LOAD DATA

  INFILE 't.dat' // 要导入的文件

  // INFILE 'tt.date' // 导入多个文件

  // INFILE * // 要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容

  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

  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

  =====================================================================================

  //////////// 注意begindata后的数值前面不能有空格

  1 ***** 普通装载

  LOAD DATA

  INFILE *

  INTO TABLE DEPT

  REPLACE

  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

  (DEPTNO,

  DNAME,

  LOC

  )

  BEGINDATA

  10,Sales,"""USA"""

  20,Accounting,"Virginia,USA"

  30,Consulting,Virginia

  40,Finance,Virginia

  50,"Finance","",Virginia // loc 列将为空

  60,"Finance",,Virginia // loc 列将为空

  2 ***** FIELDS TERMINATED BY WHITESPACE 和 FIELDS TERMINATED BY x'09' 的情况

  LOAD DATA

  INFILE *

  INTO TABLE DEPT

  REPLACE

  FIELDS TERMINATED BY WHITESPACE

  -- FIELDS TERMINATED BY x'09'

  (DEPTNO,

  DNAME,

  LOC

  )

  BEGINDATA

  10 Sales Virginia

  3 ***** 指定不装载那一列

  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,USA"

  4 ***** position的列子

  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

  5 ***** 使用函数 日期的一种表达 TRAILING NULLCOLS的使用

  LOAD DATA

  INFILE *

  INTO TABLE DEPT

  REPLACE

  FIELDS TERMINATED BY ','

  TRAILING NULLCOLS // 其实下面的ENTIRE_LINE在BEGINDATA后面的数据中是没有直接对应

  // 的列的值的 如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS了

  (DEPTNO,

  DNAME "upper(:dname)", // 使用函数

  LOC "upper(:loc)",

  LAST_UPDATED date 'dd/mm/yyyy', // 日期的一种表达方式 还有'dd-mon-yyyy' 等

  ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"

  )

  BEGINDATA

  10,Sales,Virginia,1/5/2000

  20,Accounting,Virginia,21/6/1999

  30,Consulting,Virginia,5/1/2000

  40,Finance,Virginia,15/3/2001

  6 ***** 使用自定义的函数 // 解决的时间问题

  create or replace

  function my_to_date( p_string in varchar2 ) return date

  as

  type fmtArray is table of varchar2(25);

  l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',

  'dd/mm/yyyy',

  'dd/mm/yyyy hh24:mi:ss' );

  l_return date;

  begin

  for i in 1 .. l_fmts.count

  loop

  begin

  l_return := to_date( p_string, l_fmts(i) );

  exception

  when others then null;

  end;

  EXIT when l_return is not null;

  end loop;

  if ( l_return is null )

  then

  l_return :=

  new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *

  p_string, 'GMT', 'EST' );

  end if;

  return l_return;

  end;

  /

  LOAD DATA

  INFILE *

  INTO TABLE DEPT

  REPLACE

  FIELDS TERMINATED BY ','

  TRAILING NULLCOLS

  (DEPTNO,

  DNAME "upper(:dname)",

  LOC "upper(:loc)",

  LAST_UPDATED "my_to_date( :last_updated )" // 使用自定义的函数

  )

  BEGINDATA

  10,Sales,Virginia,01-april-2001

  20,Accounting,Virginia,13/04/2001

  30,Consulting,Virginia,14/04/2001 12:02:02

  40,Finance,Virginia,987268297

  50,Finance,Virginia,02-apr-2001

  60,Finance,Virginia,Not a date

  7 ***** 合并多行记录为一行记录

  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="," 也可以

  告诉sqlldr在每行的末尾找逗号 找到逗号就把下一行附加到上一行

  LOAD DATA

  INFILE *

  continueif this(1:1) = '-' // 找每行的开始是否有连接字符 - 有就把下一行连接为一行

  // 如 -10,Sales,Virginia,

  // 1/5/2000 就是一行 10,Sales,Virginia,1/5/2000

  // 其中1:1 表示从第一行开始 并在第一行结束 还有continueif next 但continueif list最理想

  INTO TABLE DEPT

  replace

  FIELDS TERMINATED BY ','

  (DEPTNO,

  DNAME "upper(:dname)",

  LOC "upper(:loc)",

  LAST_UPDATED date 'dd/mm/yyyy'

  )

  BEGINDATA // 但是好象不能象右面的那样使用

  -10,Sales,Virginia, -10,Sales,Virginia,

  1/5/2000 1/5/2000

  -40, 40,Finance,Virginia,13/04/2001

  Finance,Virginia,13/04/2001

  8 ***** 载入每行的行号

  load data

  infile *

  into table t

  replace

  ( seqno RECNUM //载入每行的行号

  text Position(1:1024))

  BEGINDATA

  fsdfasj //自动分配一行号给载入 表t 的seqno字段 此行为 1

  fasdjfasdfl // 此行为 2 ...

  9 ***** 载入有换行符的数据

  注意: unix 和 windows 不同 //n & /n

  < 1 > 使用一个非换行符的字符

  LOAD DATA

  INFILE *

  INTO TABLE DEPT

  REPLACE

  FIELDS TERMINATED BY ','

  TRAILING NULLCOLS

  (DEPTNO,

  DNAME "upper(:dname)",

  LOC "upper(:loc)",

  LAST_UPDATED "my_to_date( :last_updated )",

  COMMENTS "replace(:comments,'/n',chr(10))" // replace 的使用帮助转换换行符

  )

  BEGINDATA

  10,Sales,Virginia,01-april-2001,This is

 

 

 

 

 

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页