Oracle学习之：数据迁移工具（1）：SQL Loader

SQL Loader是一种数据加载工具，可以把外部数据加载到Oracle数据库中。SQL Loader中的参数有很多，本文只在第一部分中列出常用参数。要想运用SQL Loader工具 ，需要我们编辑一个控制文件，所谓控制文件就是告诉SQL Loader应该怎样去工作的文件，这很好理解。当我们在加载数据时可以用两种方式：一种是把数据写在控制文件中；另外一种是把数据单独存放在一个数据文件中，并在控制文件中标明此数据文件。

1.常用参数

参数名称

含义

userid

ORACLE username/password

control

控制文件，可能包含表的数据

log

记录导入时的日志文件，默认为：控制文件.log

bad

坏数据文件，默认为：控制文件.bad

data

数据文件

discard

丢弃的数据文件

discardmax

允许丢弃的数据文件的最大值，默认全部

skip

跳过的行数，默认为0

load

导入的行数，默认全部

errors

允许的错误记录条数，默认为50条

rows

规定多少条记录后提交，默认为64条

silent

禁止输出信息(header,feedback,errors,discards,partitions)

tions)

parfile

参数文件，包含参数规范的文件名称

parallel

并行导入，默认为FALSE

##在操作系统中输入sqlldr，如:[oracle@lgr ~]$ sqlldr，可以获得更多参数。

2.控制文件

load data

infile 't_01.dat' -- 要导入的数据文件名称

--infile 't_02.dat' -- 如果是多个数据文件，那么可以在此处写入多个

--infile * -- 要导入的内容就在 CONTROL 文件里, BEGINDATA 后面就是导入的内容(与

上面格式不能同时使用)

 

insert：数据加载方式（默认）

加载方式有如下四种：

append：原先的表有数据就加在后面

insert：（默认值）装载空表,如果原先的表有数据 SQLLOADER 会停止

replace：原先的表有数据原先的数据会全部删除

truncate：指定的内容和 REPLACE 的相同会用 TRUNCATE 语句删除现存数据

 

badfile 'bf_name.bad'：指定出现错误的记录存放的位置及名称。如果此参数没有指定，那么默认会

在控制文件同目录下生成一个与存放数据的文件同名的且后缀为 bad 的文件。

 

fields terminated by ',' optionally enclosed by '"'

转载的数据格式为，以','分隔的数据，且以'"'来标识一个字段的起始。主要是因为，在平文本文件中，

有可能出现带逗号的字段，那样，sqlloader 会误以为，那个逗号为分隔符，导致 load 的数据是错误的。

注：此参数可以声明也可以不声明，如果没声明，那么需要在定义字段的地方声明用什么来区分。

 

trailing nullcols：允许出现空值，当平文本文件中，没有对应表中字段的值，那么以 null 来代替。

如果不加此参数，那么，对应不上的记录将无法写入表，会出现在 bad 文件中。

(col_name1,col_name2,col_name3) ：声明所有字段的名称。

如果没有声明 FIELDS TERMINATED BY ','，那么也可以在字段处进行声明，如下：

(

col_name1 [INTERGER EXTERNAL] TERMINATED BY ',' ,

col_name2 [DATE "DD-MON-YYY"] TERMINATED BY ',' ,

col_name3 [CHAR] TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

)

如果没有声明 FIELDS TERMINATED BY ','，并且文本文件中没有任何的分隔标识，那么也可以用指

定位置的方式来装载数据，如下：

(

col_name1 POSITION(1:2),

col_name2 POSITION(3:9),

col_name3 POSITION(*:15) CHAR(8),//char(8)指定字段类型及长度，*:15，表示，从上一个字

段结束的位置开始，15 结束

col_name4 POSITION(16:30) "TRIM(:col_name4)", // 去掉本字段截取的字符两边的空格

)

begindata：与 infile * 遥相呼应，即要导入的数据就在控制文件中，且在 begindata 的下面。

 

 

3.导入命令

编辑好控制文件后在操作系统中执行如下命令：

sqlldr user/password@dbservice control=file_name

##如果在本机导入数据，则登录时不用添加dbservice

4.友情提示

1) ROWS 的默认值为 64，你可以根据实际指定更合适的 ROWS 参数来指定每次提交记录数。

2）常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中，可以提高导入数据的性

   能。 当然，在很多情况下，不能使用此参数(如果主键重复的话会使索引的状态变成UNUSABLE!)。

3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。

4) 对于超大数据文件的导入就要用并发操作了，即同时运行多个导入任务.

   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共同应用。   

5）一般只能用ASCII码形式，切记要转换编码，不然导入数据为空，ftp上传csv文件的传输类型选择ascii。

 

测试一 ：infile *

1）创建目录对象，并赋权给scott用户

SYS@lgr> create or replace directory dir_dt as '/home/oracle';

 

Directory created.

SYS@lgr> grant read,write on directory dir_dt to scott;

 

Grant succeeded.

2）在scott用户下创建表，用于模拟向其中导入外部数据

SYS@lgr> conn scott/tiger

Connected.

SCOTT@lgr> create table sl_base (id number(5),fname varchar2(10),lname varchar2(10));

 

Table created.

 

SCOTT@lgr> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@lgr ~]$

3）创建控制文件

[oracle@lgr ~]$ vi base.ctl

load data

infile *

badfile 'base.bad'

into table sl_base

replace

fields terminated by ','

(id,fname,lname)

begindata

1,zhangfei,zhangyide

2,guanyu,guanyunchang

3,liubei,liuxuande

 

4）通过sqlldr将控制文件中的数据导入sl_base表中

[oracle@lgr ~]$ sqlldr scott/tiger control=base.ctl

 

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 17 18:30:20 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Commit point reached - logical record count 3

 

5）查看生成的相关日志

##我们在编辑控制文件时并没有指明日志文件路径，但我们可以在导入完成后查看到日志文件。其实，日志文件不用我们指定，它的默认路径是登录sqlldr的目录。可以利用日志查看数据的导入情况。

[oracle@lgr ~]$ ls

base.bad  base.ctl  base.log  

 

6）查看生成的bad文件

[oracle@lgr ~]$ cat base.bad

2,guanyu,guanyunchang       ##因为lname超过了sl_base表的字符长度，所以不能导入

 

7）查看sl_base 表的导入结果

SCOTT@lgr> select * from sl_base;

 

ID FNAME      LNAME

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

 1 zhangfei   zhangyide

 3 liubei     liuxuande

 

 

测试二 ：null

要求：

-  利用测试一的目录对象，再次不再创建

-  创建存放数据的文件

-  使用默认的 bad 文件生成方式

-  使用 truncate 选项方式

1）清理测试一产生的文件

[oracle@lgr ~]$ rm base*

 

2）编辑模拟导入的数据文件

[oracle@lgr ~]$ vi base_data.dat

1,zhangfei,zhangyide

2,guanyu,guanyunchang

3,liubei,liuxuande

4,zhugeliang

 

3）编辑控制文件

[oracle@lgr ~]$ vi base.ctl

load data

infile 'base_data.dat'

into table sl_base

truncate

fields terminated by ','

(id,fname,lname)

 

4）通过sqlldr导入数据

[oracle@lgr ~]$ sqlldr scott/tiger control=base.ctl

 

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 17 18:52:27 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Commit point reached - logical record count 4

 

5）查看生成的相关文件

[oracle@lgr ~]$ ll

total 20

-rw-r--r-- 1 oracle oinstall  103 Jan 17 18:50 base.ctl

-rw-r--r-- 1 oracle oinstall   35 Jan 17 18:52 base_data.bad

-rw-r--r-- 1 oracle oinstall   75 Jan 17 18:45 base_data.dat

-rw-r--r-- 1 oracle oinstall 1839 Jan 17 18:52 base.log

-rw-r--r-- 1 oracle oinstall  283 Jan 17 18:28 data.log

 

6）查看bad文件

[oracle@lgr ~]$ cat base_data.bad

4,zhugeliang

2,guanyu,guanyunchang

##bad文件中有两条错误记录，一条是因为字符长度问题，另外一条是因为lname中存在控制，而我们并没有对它进行处理。

 

7）数据库中查看sl_base表中数据

[oracle@lgr ~]$ sqlplus scott/tiger

 

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 17 18:57:54 2017

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SCOTT@lgr> select * from sl_base;

 

ID FNAME      LNAME

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

 1 zhangfei   zhangyide

 3 liubei     liuxuande

 

8）对空值进行处理，重新编辑控制文件

[oracle@lgr ~]$ vi base.ctl

load data

infile 'base_data.dat'

into table sl_base

truncate

fields terminated by ','

trailing nullcols

(id,fname,lname)

 

9）利用sqlldr重新导入数据

[oracle@lgr ~]$ sqlldr scott/tiger control=base.ctl

 

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 17 19:02:33 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Commit point reached - logical record count 4

 

10）再次查看bad文件

[oracle@lgr ~]$ cat base_data.bad

2,guanyu,guanyunchang

 

11）再次查看表sl_base中数据

[oracle@lgr ~]$ sqlplus scott/tiger

 

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 17 19:03:58 2017

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SCOTT@lgr> select * from sl_base;

 

ID FNAME      LNAME

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

 1 zhangfei   zhangyide

 3 liubei     liuxuande

 4 zhugeliang

 

12)  小结

如果数据文件中的数据存在 null 值，那么一定记得在控制文件中加入 trailing nullcols 语句，

除非你不想导入 null 值。

 

 

测试三 ：字符串中包含逗号

 

要求：

-  数据文件中的数据存在逗号

-  在控制文件中定义字段时指定分隔符

1）删除上次实验相关文件

[oracle@lgr ~]$ rm base* data*

 

2）创建数据文件

[oracle@lgr ~]$ vi base_data.bat

1,zhangfei,"zhang,yide"

2,guanyu,"guan,yunchang"

3,liubei,"liu,xuande"

4,zhugeliang

 

3）创建控制文件

[oracle@lgr ~]$ vi base.ctl

load data

infile 'base_data.bat'

into table sl_base

truncate

trailing nullcols

(

id terminated by ',',

fname terminated by ',',

lname terminated by ',' optionally enclosed by '"'

)

 

4）利用sqlldr导入数据

[oracle@lgr ~]$ sqlldr scott/tiger control=base.ctl

 

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 17 19:18:54 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Commit point reached - logical record count 4

 

5）查看生成的相关文件

[oracle@lgr ~]$ ls

base.ctl  base_data.bad  base_data.bat  base.log

 

6）查看bad文件，还是那条因为字符长度不合要求的数据

[oracle@lgr ~]$ cat base_data.bad

2,guanyu,"guan,yunchang"

 

7）查看sl_base表的数据

 

[oracle@lgr ~]$ sqlplus scott/tiger

 

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 17 19:19:35 2017

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SCOTT@lgr> select * from sl_base;

 

ID FNAME      LNAME

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

 1 zhangfei   zhang,yide

 3 liubei     liu,xuande

 4 zhugeliang

 

 

测试四 ：数据文件中无分隔符

 

1）删除上次实验生成的文件

[oracle@lgr ~]$ rm base*

 

2）创建数据文件

[oracle@lgr ~]$ vi base_data.bat

 

1zhangfei  zhangyide

2guanyu    guanyuchang

3liubei    liuxuande

4zhugeliang

 

3）创建控制文件

[oracle@lgr ~]$ vi base.ctl

 

load data

infile 'base_data.bat'

into table sl_base

truncate

trailing nullcols

(

id position(1:1),

fname position(2:11),

lname position(12:22)

)

 

4）利用sqlldr导入数据

[oracle@lgr ~]$ sqlldr scott/tiger control=base.ctl

 

SQL*Loader: Release 11.2.0.4.0 - Production on Tue Jan 17 19:43:42 2017

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

Commit point reached - logical record count 4

 

5）查看生成的相关文件

[oracle@lgr ~]$ ls

base.ctl  base_data.bad  base_data.bat  base_data.log  base.log

 

6）查看bad文件

[oracle@lgr ~]$ cat base_data.bad

2guanyu    guanyuchang

 

7）验证导入结果

[oracle@lgr ~]$ sqlplus scott/tiger

 

SQL*Plus: Release 11.2.0.4.0 Production on Tue Jan 17 19:45:17 2017

 

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

 

 

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

 

SCOTT@lgr> select * from sl_base;

 

ID FNAME      LNAME

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

 1 zhangfei   zhangyide

 3 liubei     liuxuande

 4 zhugeliang