一sql loader 的特点
oracle自己带了很多的工具可以用来进行数据的迁移备份和恢复等工作但是每个工具都有自己的特点
比如说exp和imp可以对数据库中的数据进行导出和导出的工作是一种很好的数据库备份和恢复的工具因此主要用在数据库的热备份和恢复方面有着速度快使用简单快捷的优点同时也有一些缺点比如在不同版本数据库之间的导出导入的过程之中总会出现这样或者那样的问题这个也许是oracle公司自己产品的兼容性的问题吧
sql loader 工具却没有这方面的问题它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具缺点就速度比较慢另外对blob等类型的数据就有点麻烦了
二sql loader 的帮助
C:\>sqlldr
SQL*Loader: Release Production on 星期六 月 ::
Copyright (c) Oracle Corporation All rights reserved
用法: SQLLDR keyword=value [keyword=value]
有效的关键字:
userid ORACLE username/password
control Control file name
log Log file name
bad Bad file name
data Data file name
discard Discard file name
discardmax Number of discards to allow (全部默认)
skip Number of logical records to skip (默认)
load Number of logical records to load (全部默认)
errors Number of errors to allow (默认)
rows Number of rows in conventional path bind array or between direct p
ath data saves
（默认: 常规路径 所有直接路径）
bindsize Size of conventional path bind array in bytes(默认)
silent Suppress messages during run (headerfeedbackerrorsdiscardspart
itions)
direct use direct path (默认FALSE)
parfile parameter file: name of file that contains parameter specification
s
parallel do parallel load (默认FALSE)
file File to allocate extents from
skip_unusable_indexes disallow/allow unusable indexes or index partitions(默
认FALSE)
skip_index_maintenance do not maintain indexes mark affected indexes as unus
able(默认FALSE)
readsize Size of Read buffer (默认)
external_table use external table for load; NOT_USED GENERATE_ONLY EXECUTE(
默认NOT_USED)
columnarrayrows Number of rows for direct path column array(默认)
streamsize Size of direct path stream buffer in bytes(默认)
multithreading use multithreading in direct path
resumable enable or disable resumable for current session(默认FALSE)
resumable_name text string to help identify resumable statement
resumable_timeout wait time (in seconds) for RESUMABLE(默认)
date_cache size (in entries) of date conversion cache(默认)
PLEASE NOTE: 命令行参数可以由位置或关键字指定
前者的例子是 sqlload
scott/tiger foo; 后一种情况的一个示例是 sqlldr control=foo
userid=scott/tiger位置指定参数的时间必须早于
但不可迟于由关键字指定的参数例如
允许 sqlldr scott/tiger control=foo logfile=log 但是
不允许 sqlldr scott/tiger control=foo log 即使
参数 log 的位置正确
C:\>
三sql loader使用例子
a）SQLLoader将 Excel 数据导出到 Oracle
创建SQL*Loader输入数据所需要的文件均保存到C:\用记事本编辑
控制文件inputctl内容如下
load data 控制文件标识
infile testtxt 要输入的数据文件名为testtxt
append into table test向表test中追加记录
fields terminated by X字段终止于X是一个制表符（TAB）
(idusernamepasswordsj) 定义列对应顺序
ainsert为缺省方式在数据装载开始时要求表为空
bappend在表中追加新记录
creplace删除旧记录替换成新装载的记录
dtruncate同上
在DOS窗口下使用SQL*Loader命令实现数据的输入
C:\>sqlldr userid=system/manager control=inputctl
默认日志文件名为inputlog
默认坏记录文件为inputbad
还有一种方法
可以把EXCEL文件另存为CSV（逗号分隔）(*csv)控制文件就改为用逗号分隔
LOAD DATA
INFILE d:\carcsv
APPEND INTO TABLE t_car_temp
FIELDS TERMINATED BY
(phonenovip_car)
b）在控制文件中直接导入数据
控制文件testctl的内容
The format for executing this file with SQL Loader is:
SQLLDR control=<filename> Be sure to substitute your
version of SQL LOADER and the filename for this file
LOAD DATA
INFILE *
BADFILE C:\Documents and Settings\Jackey\桌面\WMCOUNTRYBAD
DISCARDFILE C:\Documents and Settings\Jackey\桌面\WMCOUNTRYDSC
INSERT INTO TABLE EMCCOUNTRY
Fields terminated by ; Optionally enclosed by
(
COUNTRYID NULLIF (COUNTRYID=NULL)
COUNTRYCODE
COUNTRYNAME
CONTINENTID NULLIF (CONTINENTID=NULL)
MAPID NULLIF (MAPID=NULL)
CREATETIME DATE MM/DD/YYYY HH:MI:SS NULLIF (CREATETIME=NULL)
LASTMODIFIEDTIME DATE MM/DD/YYYY HH:MI:SS NULLIF (LASTMODIFIEDTIME=NULL)
)
BEGINDATA
;JP;Japan;;;// ::;NULL
;CN;China;;;// ::;NULL
;IN;India;;;// ::;NULL
;AU;Australia;;;// ::;NULL
;CA;Canada;;;// ::;NULL
;US;United States;;;// ::;NULL
;MX;Mexico;;;// ::;NULL
;GB;United Kingdom;;;// ::;NULL
;DE;Germany;;;// ::;NULL
;FR;France;;;// ::;NULL
;IT;Italy;;;// ::;NULL
;ES;Spain;;;// ::;NULL
;FI;Finland;;;// ::;NULL
;SE;Sweden;;;// ::;NULL
;IE;Ireland;;;// ::;NULL
;NL;Netherlands;;;// ::;NULL
;DK;Denmark;;;// ::;NULL
;BR;Brazil;;;// ::;NULL
;KR;Korea Republic of;;;// ::;NULL
;NZ;New Zealand;;;// ::;NULL
;BE;Belgium;;;// ::;NULL
;AT;Austria;;;// ::;NULL
;NO;Norway;;;// ::;NULL
;LU;Luxembourg;;;// ::;NULL
;PT;Portugal;;;// ::;NULL
;GR;Greece;;;// ::;NULL
;IL;Israel;;;// ::;NULL
;CH;Switzerland;;;// ::;NULL
;A;Anonymous Proxy;;;// ::;NULL
;A;Satellite Provider;;;// ::;NULL
;AD;Andorra;;;// ::;NULL
;AE;United Arab Emirates;;;// ::;NULL
;AF;Afghanistan;;;// ::;NULL
;AG;Antigua and Barbuda;;;// ::;NULL
;AI;Anguilla;;;// ::;NULL
;AL;Albania;;;// ::;NULL
;AM;armenia;;;// ::;NULL
;AN;Netherlands Antilles;;;// ::;NULL
;AO;Angola;;;// ::;NULL
;AP;Asia/Pacific Region;;;// ::;NULL
;AQ;Antarctica;;;// ::;NULL
;AR;Argentina;;;// ::;NULL
;AS;American Samoa;;;// ::;NULL
;AW;Aruba;;;// ::;NULL
;AZ;Azerbaijan;;;// ::;NULL
;BA;Bosnia and Herzegovina;;;// ::;NULL
;BB;Barbados;;;// ::;NULL
;BD;Bangladesh;;;// ::;NULL
;BF;Burkina Faso;;;// ::;NULL
;BG;Bulgaria;;;// ::;NULL
;BH;Bahrain;;;// ::;NULL
;BI;Burundi;;;// ::;NULL
;BJ;Benin;;;// ::;NULL
;BM;Bermuda;;;// ::;NULL
;BN;Brunei Darussalam;;;// ::;NULL
;BO;Bolivia;;;// ::;NULL
;BS;Bahamas;;;// ::;NULL
;BT;Bhutan;;;// ::;NULL
;BV;Bouvet Island;;;// ::;NULL
;BW;Botswana;;;// ::;NULL
;BY;Belarus;;;// ::;NULL
执行导入命令
C:\>sqlldr userid=system/manager control=testctl
part ii
SQL*Loader是Oracle数据库导入外部数据的一个工具它和DB的Load工具相似但有更多的选择它支持变化的加载模式可选的加载及多表加载
如何使用 SQL*Loader 工具
我们可以用Oracle的sqlldr工具来导入数据例如:
sqlldr scott/tiger control=loaderctl
控制文件(loaderctl) 将加载一个外部数据文件（含分隔符) loaderctl如下:
load data
infile c:\data\mydatacsv
into table emp
fields terminated by optionally enclosed by
( empno empname sal deptno )
mydatacsv 如下:
Scott Tiger
Frank Naude
下面是一个指定记录长度的示例控制文件* 代表数据文件与此文件同名即在后面使用BEGINDATA段来标识数据
load data
infile *
replace
into table departments
( dept position (:) char()
deptname position (:) char()
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
Unloader这样的工具
Oracle 没有提供将数据导出到一个文件的工具但是我们可以用SQL*Plus的select 及 format 数据来输出到一个文件
set echo off newpage space pagesize feed off head off trimspool on
spool oradatatxt
select col || || col || || col
from tab
where col = XYZ;
spool off
另外也可以使用使用 UTL_FILE PL/SQL 包处理:
rem Remember to update initSIDora utl_file_dir=c:\oradata parameter
declare
fp utl_filefile_type;
begin
fp := utl_filefopen(c:\oradatatabtxtw);
utl_fileputf(fp %s %s\n TextField );
utl_filefclose(fp);
end;
/
当然你也可以使用第三方工具如SQLWays TOAD for Quest等
加载可变长度或指定长度的记录
如
LOAD DATA
INFILE *
INTO TABLE load_delimited_data
FIELDS TERMINATED BY OPTIONALLY ENCLOSED BY
TRAILING NULLCOLS
( data
data
)
BEGINDATA
AAAAAAAAAA
ABCD
下面是导入固定位置（固定长度）数据示例
LOAD DATA
INFILE *
INTO TABLE load_positional_data
( data POSITION(:)
data POSITION(:)
)
BEGINDATA
AAAAAAAAAA
BBBBBBBBBB
跳过数据行
可以用 SKIP n 关键字来指定导入时可以跳过多少行数据如
LOAD DATA
INFILE *
INTO TABLE load_positional_data
SKIP
( data POSITION(:)
data POSITION(:)
)
BEGINDATA
AAAAAAAAAA
BBBBBBBBBB
导入数据时修改数据
在导入数据到数据库时可以修改数据注意这仅适合于常规导入并不适合 direct导入方式如
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no my_db_sequencenextval
region CONSTANT
time_loaded to_char(SYSDATE HH:MI)
data POSITION(:) :data/
data POSITION(:) upper(:data)
data POSITION(:)to_date(:data YYMMDD)
)
BEGINDATA
AAAAAAAAAA
BBBBBBBBBB
LOAD DATA
INFILE mail_orderstxt
BADFILE bad_orderstxt
APPEND
INTO TABLE mailing_list
FIELDS TERMINATED BY
( addr
city
state
zipcode
mailing_addr decode(:mailing_addr null :addr :mailing_addr)
mailing_city decode(:mailing_city null :city :mailing_city)
mailing_state
)
将数据导入多个表
如:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno !=
( empno POSITION(:) INTEGER EXTERNAL
ename POSITION(:) CHAR
deptno POSITION(:) CHAR
mgr POSITION(:) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno !=
( projno POSITION(:) INTEGER EXTERNAL
empno POSITION(:) INTEGER EXTERNAL
)
导入选定的记录
如下例 () 代表第一个字符 (:) 代表到之间的字符:
LOAD DATA
INFILE mydatadat BADFILE mydatabad DISCARDFILE mydatadis
APPEND
INTO TABLE my_selective_table
WHEN () <> H and () <> T and (:) =
(
region CONSTANT
service_key POSITION(:) INTEGER EXTERNAL
call_b_no POSITION(:) CHAR
)
导入时跳过某些字段
可用 POSTION(x:y) 来分隔数据 在Oraclei中可以通过指定 FILLER 字段实现FILLER 字段用来跳过忽略导入数据文件中的字段如
LOAD DATA
TRUNCATE INTO TABLE T
FIELDS TERMINATED BY
( field
field FILLER
field
)
导入多行记录
可以使用下面两个选项之一来实现将多行数据导入为一个记录:
CONCATENATE: use when SQL*Loader should combine the same number of physical records together to form one logical record
CONTINUEIF use if a condition indicates that multiple records should be treated as one Eg by having a # character in column
SQL*Loader 数据的提交
一般情况下是在导入数据文件数据后提交的
也可以通过指定 ROWS= 参数来指定每次提交记录数
提高 SQL*Loader 的性能
) 一个简单而容易忽略的问题是没有对导入的表使用任何索引和/或约束(主键)如果这样做甚至在使用ROWS=参数时会很明显降低数据库导入性能
) 可以添加 DIRECT=TRUE来提高导入数据的性能当然在很多情况下不能使用此参数
) 通过指定 UNRECOVERABLE选项可以关闭数据库的日志这个选项只能和 direct 一起使用
) 可以同时运行多个导入任务
常规导入与direct导入方式的区别
常规导入可以通过使用 INSERT语句来导入数据Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)而直接将数据导入到数据文件中
