Oracle-sqlldr

 

1引言

1.1 概述

SQLLDR简介

 

2sql loader的特点

oracle自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。但是每个工具都有自己的特点。

exp和imp:逻辑转移备份,对象主要是是数据库用户，将一个用户的所有表结构及索引，触发器，过程，函数等导入到另一用户里

expdp和impdp:10g新添功能，逻辑转移备份,对象是数据库或数据库用户,实现表空间的转移

sql loader: 文本导入，它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中，是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。

 

3sql loader命令

3.1控制文件

控制文件是用一种语言写的文本文件，这个文本文件能被SQL*LOADER识别。 SQL*LOADER根据控制文件可以找到需要加载的数据。并且分析和解释这些数据。控制文件由三个部分组成：

全局选件，行，跳过的记录数等； INFILE子句指定的输入数据；数据特性说明。

3.2输入文件

对于 SQL*Loader, 除控制文件外就是输入数据。SQL*Loader可从一个或多个指定的文件中读出数据。

3.3失败文件

坏文件包含那些被SQL*Loader拒绝的记录。被拒绝的记录可能是不符合要求的记录。坏文件的名字由SQL*Loader命令的BADFILE 参数来给定。

3.4日志文件及日志信息

当SQL*Loader 开始执行后，它就自动建立 日志文件。日志文件包含有加载的总结，加载中的错误信息等。

3.5sqlldr命令3.5.1命令

D:\work\Sqlldr\培训>sqlldr

userid=usertest/123456@NQUSER_192.168.0.18

control =TBL_TEMP_WJN_SQLLDR.ctl

data = data.txt

bad = TBL_TEMP_WJN_SQLLDR.bad

log = TBL_TEMP_WJN_SQLLDR.log

上面的例子中，usertest/123456@nquser表示入库的目标数据库以及登陆名和密码，control表示控制文件，data表示数据文件，log表示入库日志，bad表示入库失败的数据

3.5.2Sqlldr帮助

C:\Documentsand Settings\Administrator>sqlldr

SQL*Loader:Release 10.2.0.3.0 - Production on 星期三 4月 6 10:35:29 2011

Copyright(c) 1982, 2005, Oracle. All rightsreserved.

 

用法: SQLLDR keyword=value [,keyword=value,...]

 

有效的关键字:

 

userid--ORACLE用户名/口令

control--控制文件名

log--日志文件名

bad--错误文件名

data--数据文件名

discard--废弃文件名

discardmax--允许废弃的文件的数目(全部默认)

skip--要跳过的逻辑记录的数目(默认0)

load--要加载的逻辑记录的数目(全部默认)

errors--允许的错误的数目(默认50)

rows--常规路径绑定数组中或直接路径保存数据间的行数

(默认:常规路径64,所有直接路径)

bindsize--常规路径绑定数组的大小(以字节计)(默认256000)

silent--运行过程中隐藏消息(标题,反馈,错误,废弃,分区)

direct--使用直接路径(默认FALSE)

parfile--参数文件:包含参数说明的文件的名称

parallel--执行并行加载(默认FALSE)

file--要从以下对象中分配区的文件

skip_unusable_indexes--不允许/允许使用无用的索引或索引分区(默认FALSE)

skip_index_maintenance--没有维护索引,将受到影响的索引标记为无用(默认FALSE)

commit_discontinued--提交加载中断时已加载的行(默认FALSE)

readsize--读取缓冲区的大小(默认1048576)

external_table--使用外部表进行加载;NOT_USED,GENERATE_ONLY,EXECUTE(默认NO

T_USED)

columnarrayrows--直接路径列数组的行数(默认5000)

streamsize--直接路径流缓冲区的大小(以字节计)(默认256000)

multithreading--在直接路径中使用多线程

resumable--启用或禁用当前的可恢复会话(默认FALSE)

resumable_name--有助于标识可恢复语句的文本字符串

resumable_timeout--RESUMABLE的等待时间(以秒计)(默认7200)

date_cache--日期转换高速缓存的大小(以条目计)(默认1000)

 

PLEASENOTE:

命令行参数可以由位置或关键字指定。

前者的例子是'sqlload scott/tigerfoo';

后一种情况的一个示例是'sqlldrcontrol=foo userid=scott/tiger'.位置指定参数的时间必须早于

但不可迟于由关键字指定的参数。

例如,

允许'sqlldr scott/tiger control=foo logfile=log',

但是不允许'sqlldr scott/tiger control=foo log',即使参数'log'的位置正确。

 

 

4控制文件

4.1说明

一个控制命令的脚本文件，通常以ctl结尾，内容如下：

LOAD DATA 控制文件标识

INFILE ' D:\work\Sqlldr\培训\data.txt ' 要导入的文件

// INFILE 'tt.date' 导入多个文件

// INFILE * 表示要导入的内容就在control文件里 下面的BEGINDATA后面就是导入的内容

INTO TABLE TBL_TEMP_WJN_SQLLDR.ctl 指定装入的表

BADFILE 'D:\work\Sqlldr\培训\TBL_TEMP_WJN_SQLLDR.bad' 可选，指定坏文件地址,缺省在当前目录下生成与原文件名一致的.bad文件

*************以下是4种装入表的方式

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

INSERT 装载空表 如果原先的表有数据 sqlloader会停止 默认值

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

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

*************指定分隔符

FIELDSTERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

// TERMINATED BY WRITESPACE 以空白分割

// TERMINATED BY X'09' 以X'09'分割,制表符

TRAILINGNULLCOLS 表的字段没有对应的值时允许为空

*************下面是表的字段

(

TEMP_NAME,

TEMP_SEX,

TEMP_BIRTHDATE DATE "yyyy-mm-dd",

TEMP_HEIGHT,

TEMP_WEIGHT

)

如果没声明FIELDS TERMINATED BY ',' 时，可以用下面两种方式实现同样功能：

1．为每一列指定分隔符

(

col_1[interger external] TERMINATED BY ',' ,

col_2 [date"dd-mon-yyy"] TERMINATED BY ',' ,

col_3 [char]TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'

)

 

2．用位置告诉字段装载数据

(

col_1position(1:2),

col_2 position(3:10),

col_3position(*:16), // 这个字段的开始位置在前一字段的结束位置

col_4position(1:16),

col_5position(3:10) char(8) // 指定字段的类型

)

BEGINDATA 对应开始的 INFILE * 要导入的内容就在control文件里

"小红","女","1983-11-26","161","53"

"小橙","男","1977-02-28","175","70"

"小黄","男","1980-12-27","180","78"

"小绿","男","1984-04-13","178","73"

"小青","女","1975-05-16","165","51"

"小蓝","男","1987-03-25","172","80"

"小紫","女","1986-10-17","159","49"

 

4.2示例

 

 

load data

infile *

truncate into tableTBL_TEMP_WJN_SQLLDR

fields terminated by ',' optionallyenclosed by '"'

(

TEMP_NAME,

TEMP_SEX,

TEMP_BIRTHDATE DATE "yyyy-mm-dd",

TEMP_HEIGHT,

TEMP_WEIGHT

)

begindata

"小红","女","1983-11-26","161","53"

"小橙","男","1977-02-28","175","70"

"小黄","男","1980-12-27","180","78"

"小绿","男","1984-04-13","178","73"

"小青","女","1975-05-16","165","51"

"小蓝","男","1987-03-25","172","80"

"小紫","女","1986-10-17","159","49"

 

 

 

 

==============================================================

//////////// 注意begindata后的数值前面不能有空格

 

4.3几种装载方式4.3.1 普通装载

load data

infile *

truncate into table TBL_TEMP_WJN_SQLLDR

fields terminated by ',' optionallyenclosed by '"'

(

TEMP_NAME,

TEMP_SEX,

TEMP_BIRTHDATE DATE "yyyy-mm-dd",

TEMP_HEIGHT,

TEMP_WEIGHT

)

begindata

小红,女,1983-11-26,161,53

小橙,男,1977-02-28,175,70

"小黄,小白",男,1980-12-27,180,78

小绿,男,1984-04-13,178,73

小青,女,1975-05-16,165,51

小蓝,男,1987-03-25,172,80

小紫,女,1986-10-17,159,,49

 

 

 

4.3.2 FIELDS TERMINATED BY WHITESPACE 和 FIELDSTERMINATED BY x'09' 的情况

load data

infile *

truncate into tableTBL_TEMP_WJN_SQLLDR

fields terminated by WHITESPACE

-- FIELDS TERMINATED BY x'09'

(

TEMP_NAME,

TEMP_SEX,

TEMP_BIRTHDATE DATE "yyyy-mm-dd",

TEMP_HEIGHT,

TEMP_WEIGHT

)

begindata

小红 女 1983-11-26 16153

小橙 男 1977-02-28 17570

小黄 男 1980-12-27 18078

小绿 男 1984-04-13 17873

小青 女 1975-05-16 16551

小蓝 男 1987-03-25 17280

小紫 女 1986-10-17 15949

 

 

用于xls导数据

4.3.3指定不装载那一列

load data

infile *

truncate into tableTBL_TEMP_WJN_SQLLDR

fields terminated by ',' optionallyenclosed by '"'

(

TEMP_NAME,

TEMP_SEX,

TEMP_HOBBY FILLER,

TEMP_BIRTHDATE DATE "yyyy-mm-dd",

TEMP_HEIGHT,

TEMP_WEIGHT

)

begindata

"小红","女","登山","1983-11-26","161","53"

"小橙","男","玩游戏","1977-02-28","175","70"

"小黄","男","篮球","1980-12-27","180","78"

"小绿","男","唱歌","1984-04-13","178","73"

"小青","女","购物","1975-05-16","165","51"

"小蓝","男","打架","1987-03-25","172","80"

"小紫","女","游泳","1986-10-17","159","49"

 

 

4.3.4position的列子

load data

infile *

truncate into tableTBL_TEMP_WJN_SQLLDR

(

TEMP_NAME position(1:4),

TEMP_SEX position(*:6),

TEMP_BIRTHDATE position(*:16) DATE "yyyy-mm-dd",

TEMP_HEIGHT position (*:20),

TEMP_WEIGHT position(*:21)

)

begindata

小红女1983-11-2616153

小橙男1977-02-2817570

小黄男1980-12-2718078

小绿男1984-04-1317873

小青女1975-05-1616551

小蓝男1987-03-2517280

小紫女1986-10-1715949

 

 

4.3.5nullif 子句

load data

infile *

truncate into tableTBL_TEMP_WJN_SQLLDR

fields terminated by ',' optionallyenclosed by '"'

(

TEMP_NAME,

TEMP_SEX NULLIF TEMP_SEX = '男',

TEMP_BIRTHDATE DATE "yyyy-mm-dd",

TEMP_HEIGHT,

TEMP_WEIGHT

)

begindata

"小红","女","1983-11-26","161","53"

"小橙","男","1977-02-28","175","70"

"小黄","男","1980-12-27","180","78"

"小绿","男","1984-04-13","178","73"

"小青","女","1975-05-16","165","51"

"小蓝","男","1987-03-25","172","80"

"小紫","女","1986-10-17","159","49"

 

 

 

4.3.6多文件导入

多个数据文件导入同一个表

load data

infile 'D:\work\Sqlldr\培训\data.txt'

infile 'D:\work\Sqlldr\培训\data2.txt'

truncate into tableTBL_TEMP_WJN_SQLLDR

fields terminated by ',' optionallyenclosed by '"'

(

TEMP_NAME,

TEMP_SEX,

TEMP_BIRTHDATE DATE "yyyy-mm-dd hh24",

TEMP_HEIGHT,

TEMP_WEIGHT

)

 

 

 

4.3.7导入多个表

load data

infile 'D:\work\Sqlldr\培训\data.txt'

truncate into table TBL_TEMP_WJN_SQLLDR

when TEMP_SEX = '男'

fields terminated by ',' optionallyenclosed by '"'

TRAILING NULLCOLS

(

TEMP_NAME,

TEMP_SEX,

TEMP_BIRTHDATE DATE 'yyyy-mm-dd hh24',

TEMP_HEIGHT,

TEMP_WEIGHT

)

 

into table TBL_TEMP_WJN_SQLLDR_01

when TEMP_SEX = '女'

fields terminated by ',' optionallyenclosed by '"'

(

TEMP_NAME position(1),

TEMP_SEX,

TEMP_BIRTHDATE DATE 'yyyy-mm-dd hh24',

TEMP_HEIGHT,

TEMP_WEIGHT

)

 

 

 

 

需要注意的是要在后续的表中加入position(1), 指明开始位置是从头开始,否则只有第一个表中能够导入数据

5坏文件/日志文件

执行完 sqlldr 后希望能留意一下生成的几个文件，如 users.log 日志文件、users.bad 坏数据文件等。特别是要看看日志文件，从中可让你更好的理解 SqlLoader，里面有对控制文件的解析、列出每个字段的类型、加载记录的统计、出错原因等信息

loaddata

infile*

truncateinto table TBL_TEMP_WJN_SQLLDR

fieldsterminated by ',' optionally enclosed by '"'

(

TEMP_NAME,

TEMP_SEX,

TEMP_BIRTHDATE DATE "yyyy-mm-ddhh24",

TEMP_HEIGHT,

TEMP_WEIGHT

)

begindata

"小红","女","1983-11-26","a161","53"

"小橙","男","1977-02-28","175","70"

"小黄","男","1980-12-27","180","78"

"小绿","男","1984-04-13","178","73"

"小青","女","1975-05-16","165","51"

"小蓝","男","1987-03-25","172","80"

"小紫","女","1986-10-17","159","49"

5.1失败文件

TBL_TEMP_WJN_SQLLDR.bad

 

"小红","女","1983-11-26","a161","53"

5.2日志文件

TBL_TEMP_WJN_SQLLDR.log

 

SQL*Loader:Release 10.2.0.3.0 - Production on 星期三 4月 6 16:03:54 2011

Copyright(c) 1982, 2005, Oracle. All rightsreserved.

控制文件: TBL_TEMP_WJN_SQLLDR.ctl

数据文件: TBL_TEMP_WJN_SQLLDR.ctl

错误文件: TBL_TEMP_WJN_SQLLDR.bad

废弃文件: 未作指定

 

(可废弃所有记录)

 

要加载的数: ALL

要跳过的数: 0

允许的错误: 50

绑定数组: 64 行, 最大 256000 字节

继续: 未作指定

所用路径: 常规

 

表 TBL_TEMP_WJN_SQLLDR,已加载从每个逻辑记录

插入选项对此表 TRUNCATE 生效

 

列名 位置 长度 中止 包装数据类型

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

TEMP_NAME FIRST *, O (") CHARACTER

TEMP_SEX NEXT *, O (") CHARACTER

TEMP_BIRTHDATE NEXT *, O (") DATE yyyy-mm-dd hh24

TEMP_HEIGHT NEXT *, O (") CHARACTER

TEMP_WEIGHT NEXT *, O (") CHARACTER

 

记录 1: 被拒绝 - 表TBL_TEMP_WJN_SQLLDR 的列 TEMP_HEIGHT 出现错误。

ORA-01722:invalid number

 

 

表 TBL_TEMP_WJN_SQLLDR:

6 行 加载成功。

由于数据错误, 1 行 没有加载。

由于所有 WHEN 子句失败, 0 行 没有加载。

由于所有字段都为空的, 0 行 没有加载。

 

 

为绑定数组分配的空间: 82560 字节 (64 行)

读取 缓冲区字节数: 1048576

 

跳过的逻辑记录总数: 0

读取的逻辑记录总数: 7

拒绝的逻辑记录总数: 1

废弃的逻辑记录总数: 0

 

从星期三 4月06 16:03:54 2011 开始运行

在星期三 4月06 16:03:55 2011 处运行结束

 

经过时间为: 00: 00: 00.21

CPU时间为: 00: 00: 00.06