sql loader ---ORACLE SQLLDR

sql loader的基本使用：

1. sql loader里有几个概念：
控制文件：和数据库的文件不是一回事，个人理解是用于数据加载控制的。
数据文件：要加载入库的数据文件，支持文本，csv, 等格式。 数据文件的内容可以放在控制文件的最后。
×注意：最好数据记录之间不要有空行，包括数据的最后，否则有可能有些报错，但是不影响导入。
错误文件： 从sql loader中产生，文件名一般为"<数据文件名>.bad"，主要是把一些sql loader无法加载的数据放在这个文件里。
日志文件： 从sql loader中产生，文件名一般为"<控制文件名>.log"， 主要是把数据加载过程中的信息放在这个文件里。

2. 一个使用的例子：

 

1.SCOTT@orcl> create table t11 (id int, ename varchar2(20), job varchar2(20));

2.

3.Table created.

数据文件：每一行表示一条记录， 每一个逗号之间的是字段。

1.[milo@milo ~]$ cat data.txt

2.1, milo, dba

3.2, henrry, mobile sw developer

4.3, kail, developer

5.4, sanzo, test engineer

控制文件：

1.[milo@milo ~]$ cat sqlldr.ctl

2.load data

3.infile data.txt

4.truncate into table t11

5.fields terminated by ","

6.(id, ename, job)
由于使用的是truncate，会先把表truncate再加载，truncate的位置还可能是insert(表要为空），append，replace(相当于delete表中数据，然后在insert）。
fields terminated by ","  => 说明使用逗号去分割每个字段

执行数据加载：
之前我们先看下表中的数据

1.SCOTT@orcl> select * from t11;

2.

3.        ID ENAME JOB

4.---------- -------------------- --------------------

5.         1 milo dba

执行加载：

1.[milo@milo ~]$ sqlldr scott/tiger control=sqlldr.ctl

2.

3.SQL*Loader: Release 10.2.0.1.0 - Production on Wed Apr 6 07:20:47 2011

4.

5.Copyright (c) 1982, 2005, Oracle. All rights reserved.

6.

7.Commit point reached - logical record count 4

再次查询如下：数据已经加载到表里

1.SCOTT@orcl> select * from t11;

2.

3.        ID ENAME                JOB

4.---------- -------------------- --------------------

5.         1  milo                 dba

6.         2  henrry               mobile sw developer

7.         3  kail                 developer

8.         4  sanzo                test engineer
细心的你也许发现了，我们的分割符是","， 但是数据文件里的数据形式是
1, milo, dba
也就是说每个逗号后，带有一个空格，这个空格也被加入了数据中。
在这种情况下，terminated by语句是严格按照""里内容来的。
所以需要注意数据格式的统一，以及分隔符的严谨性。

1.SCOTT@orcl> select ename, length(ename) length from t11;

2.

3.ENAME LENGTH

4.-------------------- ----------

5. milo 5

6. henrry 7

7. kail 5

8. sanzo 6

而且，正如之前提到的如果数据文件有多余的空行，sql loader会把他们看成是非法数据：
我们在第5行加入了一个空行，犹如加入一行非法数据一样：

1.[milo@milo ~]$ cat -n data.txt

2.     1 1, milo, dba

3.     2 2, henrry, mobile sw developer

4.     3 3, kail, developer

5.     4 4, sanzo, test engineer

6.     5

注意到count为5.而实际真实数据为4行。

1.[milo@milo ~]$ sqlldr scott/tiger control=sqlldr.ctl

2.

3.SQL*Loader: Release 10.2.0.1.0 - Production on Wed Apr 6 07:36:47 2011

4.

5.Copyright (c) 1982, 2005, Oracle. All rights reserved.

6.

7.Commit point reached - logical record count 5

注意此时，data.bad里放入了一行数据告诉你，此行数据无效。

1.[milo@milo ~]$ cat -n data.bad

2.     1

查看log：
[milo@milo ~]$ cat sqlldr.log

SQL*Loader: Release 10.2.0.1.0 - Production on Wed Apr 6 07:42:34 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   sqlldr.ctl
Data File:      data.txt
  Bad File:     data.bad
  Discard File:  none specified

 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table T11, loaded from every logical record.
Insert option in effect for this table: TRUNCATE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
ID                                  FIRST     *           CHARACTER
    Terminator string : ', '
ENAME                                NEXT     *           CHARACTER
    Terminator string : ', '
JOB                                  NEXT     *           CHARACTER
    Terminator string : ', '

Record 5: Rejected - Error on table T11, column ID.
Column not found before end of logical record (use TRAILING NULLCOLS)

Table T11:
  4 Rows successfully loaded.
  1 Row not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.

Space allocated for bind array:                  49536 bytes(64 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:             5
Total logical records rejected:         1
Total logical records discarded:        0

Run began on Wed Apr 06 07:42:34 2011
Run ended on Wed Apr 06 07:42:34 2011

Elapsed time was:     00:00:00.11
CPU time was:         00:00:00.02

把分隔符也改了下，这下就数据就没问题了：

1.SCOTT@orcl> select ename, length(ename) length from t11;

2.

3.ENAME LENGTH

4.-------------------- ----------

5.milo 4

6.henrry 6

7.kail 4

8.sanzo 5

文章出处：飞诺网(www.diybl.com):http://www.diybl.com/course/7_databases/oracle/oraclejs/20110608/556851.html