Oracle SQL Loader数据导入

1、SQL LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。

 

它使用的命令为：在NT下，SQL LOADER的命令为SQLLDR，在UNIX下则为sqlldr/sqlload。

 

2、一般用法

sqlldr scott/tiger control=control_file.ctl log=logFile.log direct=true

 

 

其它参数则在输入sqlldr命令时出现：

用法: SQLLDR keyword=value [,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(默认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 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)
    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 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)

  PLEASE NOTE: 命令行参数可以由位置或关键字指定。前者的例子是 'sqlload scott/tiger foo'; 后一种情况的一个示例是 'sqlldr control=foo
userid=scott/tiger'.位置指定参数的时间必须早于但不可迟于由关键字指定的参数。例如,
允许 'sqlldr scott/tiger control=foo logfile=log', 但是
不允许 'sqlldr scott/tiger control=foo log', 即使
参数 'log' 的位置正确。

 

3、控制文件制定

LOAD DATA
INFILE 'input_File.csv’
INTO TABLE tableName
FIELDS TERMINATED BY  "," 

(field1,field2,field3,field4)

 

 

解释：

infile　指数据源文件　这里我们省略了默认的　discardfile   result.dsc   badfile   result.bad   
into   table   tableName 默认是INSERT，也可以 APPEND into   table   tableName  为追加方式，或REPLACE  

注意：

  1、insert，为缺省方式，在数据装载开始时要求表为空     
  2、append，在表中追加新记录     
  3、replace，删除旧记录，替换成新装载的记录     
  4、truncate，同上      

terminated   by   ','　指用逗号分隔   
terminated   by   whitespace　结尾以空白分隔  

fields   terminated   by   X'09'　　字段终止于X'09'，是一个制表符（TAB） 

tableName：数据表

(field1,field2,field3,field4)：数据表导入字段

 

注意：当infile *时，导入数据就在控制文件中.....

如：

 

 

LOAD DATA
INFILE *
APPEND  INTO TABLE tableName
FIELDS TERMINATED BY  ","  Optionally enclosed by '"'
(field1,field2,field3,field4)

 

BEGINDATA

 

field1_data,field2_data,field3_data,field4_data

field1_data,field2_data,field3_data,field4_data

field1_data,field2_data,field3_data,field4_data

 

 

 注:"*" 代表数据文件与此文件同名，即在后面使用BEGINDATA段来标识数据

 

 4、并发操作

 

sqlldr scott/tiger control=control_file.ctl log=logFile.log direct=true parallel=true 

 

 

 5、导出规则数据

 

Oracle 没有提供将数据导出到一个文件的工具。但是，我们可以用SQL*Plus的select 及 format 数据来输出到一个文件

    set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
　　spool oradata.txt
　　select col1 || ',' || col2 || ',' || col3
　　from tab1
　　where col2 = 'XYZ';
　　spool off

 

 

   另外，也可以使用使用 UTL_FILE PL/SQL 包处理:
　　rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
　　declare
　　fp utl_file.file_type;
　　begin
　　fp := utl_file.fopen('c:\oradata','tab1.txt','w');
　　utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
　　utl_file.fclose(fp);
　　end;

 

当然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。
　　加载可变长度或指定长度的记录
　　如：
　　LOAD DATA
　　INFILE *
　　INTO TABLE load_delimited_data
　　FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
　　TRAILING NULLCOLS
　　( data1,
　　data2
　　)
　　BEGINDATA
　　11111,AAAAAAAAAA
　　22222,"A,B,C,D,"
　　下面是导入固定位置（固定长度）数据示例：
　　LOAD DATA
　　INFILE *
　　INTO TABLE load_positional_data
　　( data1 POSITION(1:5),
　　data2 POSITION(6:15)
　　)
　　BEGINDATA
　　11111AAAAAAAAAA
　　22222BBBBBBBBBB
　　跳过数据行：
　　可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据。如：
　　LOAD DATA
　　INFILE *
　　INTO TABLE load_positional_data
　　SKIP 5
　　( data1 POSITION(1:5),
　　data2 POSITION(6:15)
　　)
　　BEGINDATA
　　11111AAAAAAAAAA
　　22222BBBBBBBBBB
　　导入数据时修改数据：
　　在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 direct导入方式.