oracle数据库sqlloader,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