首页 > 数据库 > SQL Server >

Oracle学习之:数据迁移工具(1):SQLLoader

2017-01-18

SQL Loader是一种数据加载工具,可以把外部数据加载到Oracle数据库中。SQL Loader中的参数有很多,本文只在第一部分中列出常用参数。要想运用SQL Loader工具 ,需要我们编辑一个控制文件,所谓控制文件就是告诉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


相关文章
最新文章
热点推荐