本文永久链接地址：https://www.askmac.cn/archives/oracle-11g-ocm-sqlloader.html

 

1.1 sqlldr 基本概念

 

sqlldr有3种加载数据的模式：

1.传统路径加载

2.直接路径加载

3.外部表加载

 

在传统加载模式中，输入的记录按字段规范解析，并且每个数据域被复制到对应的二进制数组中。当二进制数组满了（或者需要读取更多数据），数组被插入。

 

SQL*Loader在二进制数组插入完成后才存储LOB域。所以所有在LOB插入的时候出现了错误（例如LOBFILE找不到），那么LOB域就会成为空。注意BEFORE和AFTER行除非起不会在LOB字段上起作用。

例如：C1 LOB字段将备插入，你有一个BEFORE触发器来检查LOB字段的值和其他字段C2 来检查。这个是不可能，因为LOB文本不会在触发器触发时加载。

 

 

直接路径加载:

直接路径加载解析输入记录的相关域规格，转换输入域数据到字段数据类型，然后构建列数组。列数据传入到块格式，这个格式是Oracle 数据块的格式。这个新的数据库块格式直接写入到数据库，绕过了大部分普通的数据处理。直接路径的速度要原比传统路径加载快，但是有一些限制。

 

 

外部表加载：

外部表是在数据库外部被定义的表，并且可以被任何格式提供的驱动访问。oracle提供了2个访问驱动：ORACLE_LOADER和ORACLE_DATAPUMP。通过数据库通过的外部表的元数据，数据库能够将外部表中的数据公开，使得其好像普通数据库中的数据。

 

外部表加载为包含数据文件的数据创建一个外部表。加载执行INSERT语句来从数据文件插入数据到外部表。

与传统路径和直接路径加载相比，使用外部表的优势在于：

1.如果数据文件很大，那么外部表加载试图并行加载文件

2.外部表加载允许在数据加载的时候，通过在创建外部表的时候制定来使用SQL函数和PL/SQL作为INSERT语句的一部分来修改数据。

 

 

 

1.2 sqlldr配置文件介绍：

 

Sqlldr 这个命令行工具在使用的时候使用控制文件来操作 要装载的文本。

简单的使用命令行命令： sqlldr  user/passwd control=xxx.ctl

–在很多参数均是默认情况下，使用控制文件以传统的方式加载数据

在命名行中还有其他的一些控制参数：

LOG：

此参数指定了 SQL*Loader的日志文件。

READSIZE：

用于从数据文件读取数据文件。如果是从控制文件读取，一般使用的READSIZE是64KB

一个较大的读取缓存可以在提交之前，读取更多的数据。例如设置READSIZE 为100000，可以让SQL*Loader在提交之前从外部数据文件读取1000000字节的chunks。

如果READSIZE的大小比BINDSIZE值大小小，那么READSIZE只将被增加。这参数不会影响LOB字段，LOB字段的读取大小是固定的64K。

 

RESUMABLE

用来控制 RESUMABLE_name 和 RESUMABLE_timeout

如果这个参数设置为ture，表示启用恢复空间分配，这后面2个参数有效，否则无效。

 

可以查看DBA_RESUMABLE和USER_RESUMABLE来确定被停止的可恢复语句。

RESUMABLE_timeout 默认是7200（2h）。如果错误在这个时间内未修复，那么执行的语句被终止，没有完成。

 

 

ROWS：

定义多少行提交一次。传统模式下这个参数的最大值是65534。在直接路径加载模式下，如果表包含LOB，XML这些字段。这个参数会被忽略，意味着加载完成时没有保存点。

 

 

SILENT:

指定在输出端返回的信息。其可以指定下列参数:

HEADER- 抑制SQL*Loader 头信息在屏幕上限制。头信息仍然会在日志文件中显示。

FEEDBACK-抑制“commit point reached”这种消息出在在屏幕上。

ERRORS-抑制在日志文件中出现的错误信息。多少行被拒绝仍然显示

DISCARDS-抑制写入到丢弃文件时，日志文件中的相关信息

PARTITIONS-在直接加载分区表时，禁止将每个分区统计信息写入到日志文件。

ALL-包含上述所有的选项

 

 

SKIP：

指定在文件开始要跳过的逻辑记录数目。默认不会跳过任何记录。

这个在配合导入行数的时候很好用，确定导入了行数，然后继续的时候就可以跳过之前导入的行数。

 

USERID：

用来指定用户和密码。如果忽略，则会被提示输入。

下面重点讲解控制文件一个控制文件的例子：

 

-- This is a sample control file

  LOAD DATA
  INFILE 'sample.dat'
  BADFILE 'sample.bad'
  DISCARDFILE 'sample.dsc'
  APPEND
   INTO TABLE emp
   WHEN (57) = '.'
 TRAILING NULLCOLS
(hiredate SYSDATE,
      deptno POSITION(1:2)  INTEGER EXTERNAL(2)
              NULLIF deptno=BLANKS,
       job    POSITION(7:14)  CHAR  TERMINATED BY WHITESPACE
              NULLIF job=BLANKS  "UPPER(:job)",
       mgr    POSITION(28:31) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE, NULLIF mgr=BLANKS,
       ename  POSITION(34:41) CHAR 
              TERMINATED BY WHITESPACE  "UPPER(:ename)",
       empno  POSITION(45) INTEGER EXTERNAL 
              TERMINATED BY WHITESPACE,
       sal    POSITION(51) CHAR  TERMINATED BY WHITESPACE
              "TO_NUMBER(:sal,'$99,999.99')",
       comm   INTEGER EXTERNAL  ENCLOSED BY '(' AND '%'
              ":comm * 100"
    )

 

 

— 在sqlldr中表示注释

LOAD DATA 语句表示sqlldr开始一个新的数据加载。

CHARACTERSET UTF8 –指定字符集。

INFILE 字句指定了包含要加载数据的文件，此处为文件sample.dat。

BADFILE 和 DISCARDFILE 指定了拒绝和丢弃记录所在的文件，如果不指定会使用默认的文件名。

APPEND 字句表名要加载的表并不是空的。（如果是INSERT，那么表必须是空的）

INTO TABLE 字句让你定义表，字段和数据类型。这个定义了数据文件中记录和数据库中表的关系。

WHERE子句定了一个或多个字段条件，SQL*Loader 使用这些条件来决定是否加载数据。

第57列的值是’.’才会被加载，当然也可以指定列名和值：

WHEN (deptno = ’10’) AND (job = ‘SALES’)

 

TRAILING NULLCOLS字句定义了那些未在数据文件中列出的空值列的相对位置。告诉sqlldr哪些列当前值是空值。这样即便数据文件中没有包含到 控制文件TRAILING NULLCOLS定义的字段，会自动的用空代替，不会报错。

 

 

2外部表

 

外部表特性是sqlldr功能的一个补充。可以让你以外部源形式访问数据库中的表。

在创建外部表的时候需要指定的属性

TYPE属性：

默认的访问驱动是ORACLE_LOADER。从外部表加载数据到内部表。数据必须来自于文本文件。这个驱动不能进行导出，也就是移动数据从内部表到外部表

 

ORACLE_DATADUMP 访问驱动可以指定导入和导出。数据必须来自于二进制转储文件。这个驱动可以在使用SQL CREATE TABLE AS SELECT语句创建外部表的时候同时写入数据到dump文件。一旦dumpfile被创建，其可以被读取任意此，但是不能被修改（不能执行DML操作）

 

DEFAULT DIRECTORY:

指定用户使用的目录，输入输出文件的位置。这个是数据库中目录对象，要为用户创建好。

 

ACCESS PARAMETERS：描述外部数据源并且指定外部表的类型。每个外部表都有其自动的访问驱动，该驱动程序提供唯一访问外部表的访问参数。访问参数是可选的。

LOCATION：

命名格式是 directory:file，目录是可选的，如果忽略，只指定文件，那么就使用之前配置得默认的目录（default directory）.下面是一个创建外部表的例子：

 

CREATE TABLE emp_load
  2    (employee_number      CHAR(5),
  3     employee_dob         CHAR(20),
  4     employee_last_name   CHAR(20),
  5     employee_first_name  CHAR(15),
  6     employee_middle_name CHAR(15),
  7     employee_hire_date   DATE)
  8  ORGANIZATION EXTERNAL
  9    (TYPE ORACLE_LOADER
 10     DEFAULT DIRECTORY def_dir1
 11     ACCESS PARAMETERS
 12       (RECORDS DELIMITED BY NEWLINE
 13        FIELDS (employee_number      CHAR(2),
 14                employee_dob         CHAR(20),
 15                employee_last_name   CHAR(18),
 16                employee_first_name  CHAR(11),
 17                employee_middle_name CHAR(11),
 18                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
 19               )
 20       )
 21     LOCATION ('info.dat')
 22    );

 

 

使用默认的目录 def_dir1 文件是info.dat。驱动模式是ORACLE_LOADER。FIELDS定义了原文件info.dat中实际的元数据。

当你创建外部表的时候，你可以指定access parameters来修改默认访问驱动的行为。每个驱动都有自己的语法。

 

外部表的限制：

外部表特性不支持下列场景：

 

1.导入和导入外部表有加密字段是不支持的

2.外部表不描述存储在数据库中的任何数据

3.外部表不藐视任何存储在外部源的数据。

4.字段处理

5.不能导入LONG字段

6.在ORACLE_LOADER驱动下不能在access parameters中指定SQL字符串

7.当在外部表访问参数中指定标识符（例如，列或表名）时，某些值被认为是保留字的访问参数分析器。如果将保留字用作标识符，则必须用双引号括起来。