SQLload

SQLload
sqlload是oracle提供的批量导⼊⽂件数据的⼯具
1、在导⼊先，先根据数据建表。

2、执⾏sqlldr命令
sqlldr userid=username/password control =*.tl --要在ctl⽂件的⽬录下执⾏
3、编写ctl⽂件
for example：
load data
infile 'F:\STUDY\6.1\CELLPORT_20161124143843_exp_1.csv' --加载的⽂件
infile 'F:\STUDY\6.1\CELLPORT_20161124143843_exp_2.csv' --加载的⽂件
into table cellport --⽬标表
when port_status='空闲' --可以设置条件筛选数据
FIELDS TERMINATED BY ',' --设置分隔符，这⾥分隔符⽯，
OPTIONALLY ENCLOSED BY '"' --设置结束符，也可以不设置，除⾮数据很⼯整，否则最好设置TRAILING NULLCOLS --设置空数据也，加载。

( --下⾯是表的字段，对每个字段还可以使⽤sql函数，进⾏⼀些计算或者格式转换等等，
city,
house_name "upper(:house_name)", --使⽤函数
houes_type,
house_jd,
houese_wd,
eq_name,
eq_range,
eq_location,
eq_fgb,
eq_port,
port_status,
port_type,
OLT,
OLT_PON ,
NUMBER_209 , --
DL_TIMESTAMP date 'yyyy-mm-dd hh24:mi:ss' --时间转换
)
1.什么是*.csv,如何得到?
⾥⾯存放的是数据表.每⾏代表数据库表格的⼀⾏,
每⾏中,每两个数据中间由逗号","分割.
*.csv可以通过"将excel⽂件另存为*.csv"得到.
2.如何将*.csv格式的数据导⼊oracle数据库?
举例来说:
test.csv⽂件中存放以下数据.
a1,b1
a2,b2
存放路径为:
d:\test.csv
准备将其存放到oracle数据库中.
a.⾸先,则需要新建表(栏位数量和类型要和需要导⼊的⼀致)
create table test(A char,B char);
b.新建test.ctl⽂件
⽤记事本编辑写⼊:
load data
infile ‘d:\test.csv’
into table “test”
fields terminated by ‘,’
(A,B)
c.打开cmd命令⾏界⾯
输⼊:
sqlldr control=d:\test.ctl log=d:\test.log
回车
:为账号和密码以及数据库名称.
以上的介绍⽐较简单.
⾥⾯的介绍⽐较详细,我转到这⾥:
在 Oracle 数据库中，我们通常在不同数据库的表间记录进⾏复制或迁移时会⽤以下⼏种⽅法：
1. A 表的记录导出为⼀条条分号隔开的 insert 语句，然后执⾏插⼊到 B 表中
2. 建⽴数据库间的 dblink，然后⽤ create table B as select * from where ...，或 insert into B select * from where ...
3. exp A 表，再 imp 到 B 表，exp 时可加查询条件
4. 程序实现 select from A ..，然后 insert into B ...，也要分批提交
5. 再就是本篇要说到的 Sql Loader(sqlldr) 来导⼊数据，效果⽐起逐条 insert 来很明显
第 1 种⽅法在记录多时是个噩梦，需三五百条的分批提交，否则客户端会死掉，⽽且导⼊过程很慢。

如果要不产⽣ REDO 来提⾼ insert into 的性能，就要下⾯那样做：
1 alter table B nologging;
2
3 insert /* +APPEND */ into B(c1,c2) values(x,xx);
4 insert /* +APPEND */ into B select * from A@dblink where .....;
5
好啦，前⾯简述了 Oracle 中数据导⼊导出的各种⽅法，我想⼀定还有更⾼明的。

下⾯重点讲讲 Oracle 的 Sql Loader (sqlldr) 的⽤法。

在命令⾏下执⾏ Oracle 的 sqlldr 命令，可以看到它的详细参数说明，要着重关注以下⼏个参数：
userid -- Oracle 的 username/password[@servicename]
control -- 控制⽂件，可能包含表的数据
-------------------------------------------------------------------------------------------------------
log -- 记录导⼊时的⽇志⽂件，默认为控制⽂件(去除扩展名).log
bad -- 坏数据⽂件，默认为控制⽂件(去除扩展名).bad
data -- 数据⽂件，⼀般在控制⽂件中指定。

⽤参数控制⽂件中不指定数据⽂件更适于⾃动操作
errors -- 允许的错误记录数，可以⽤他来控制⼀条记录都不能错
rows -- 多少条记录提交⼀次，默认为 64
skip -- 跳过的⾏数，⽐如导出的数据⽂件前⾯⼏⾏是表头或其他描述
还有更多的 sqlldr 的参数说明请参考：。

⽤例⼦来演⽰ sqlldr 的使⽤，有两种使⽤⽅法：
1. 只使⽤⼀个控制⽂件，在这个控制⽂件中包含数据
2. 使⽤⼀个控制⽂件(作为模板) 和⼀个数据⽂件
⼀般为了利于模板和数据的分离，以及程序的不同分⼯会使⽤第⼆种⽅式，所以先来看这种⽤法。

数据⽂件可以是 CSV ⽂件或者以其他分割符分隔的，数据⽂件可以⽤ PL/SQL Developer 或者 Toad 导出，也可以。

另外，⽤ Toad 还能直接⽣成包含数据的控制⽂件。

⾸先，假定有这么⼀个表 users，并插⼊五条记录：
insert into users values(1,'Unmi',3,sysdate);
insert into users values(2,NULL,5,to_date('2008-10-15','YYYY-MM-DD'));
insert into users values(3,'隔叶黄莺',8,to_date('2009-01-02','YYYY-MM-DD'));
insert into users values(4,'Kypfos',NULL,NULL);
insert into users values(5,'不知秋',1,to_date('2008-12-23','YYYY-MM-DD'));
第⼆种⽅式：使⽤⼀个控制⽂件(作为模板) 和⼀个数据⽂件
1) 建⽴数据⽂件，我们这⾥⽤ PL/SQL Developer 导出表 users 的记录为 users_data.csv ⽂件，内容如下：
" ","USER_ID","USER_NAME","LOGIN_TIMES","LAST_LOGIN"
"1","1","Unmi","3","2009-1-5 20:34:44"
"2","2","","5","2008-10-15"
"3","3","隔叶黄莺","8","2009-1-2"
"4","4","Kypfos","",""
"5","5","不知秋","1","2008-12-23"
2) 建⽴⼀个控制⽂件 users.ctl，内容如下：
说明：在操作类型 truncate 位置可⽤以下中的⼀值：
1) insert --为缺省⽅式，在数据装载开始时要求表为空
2) append --在表中追加新记录
3) replace --删除旧记录(⽤ delete from table 语句)，替换成新装载的记录
4) truncate --删除旧记录(⽤ truncate table 语句)，替换成新装载的记录
3) 执⾏命令：
sqlldr control=users.ctl
在 dbservice 指⽰的数据库的表 users 中记录就和数据⽂件中的⼀样了。

执⾏完 sqlldr 后希望能留意⼀下⽣成的⼏个⽂件，如 users.log ⽇志⽂件、users.bad 坏数据⽂件等。

特别是要看看⽇志⽂件，从中可让你更好的理解 Sql Loader，⾥⾯有对控制⽂件的解析、列出每个字段的类型、加载记录的统计、出错原因等信息。

第⼀种⽅式，只使⽤⼀个控制⽂件在这个控制⽂件中包含数据
1) 把 users_data.cvs 中的内容补到 users.ctl 中，并以 BEGINDATA 连接，还要把 INFILE "users_data.csv" 改为 INFILE *。

同时为了更⼤化的说明问题，把数据处理了⼀下。

此时，完整的 users.ctl ⽂件内容是：
OPTIONS (skip=1,rows=128) -- sqlldr 命令显⽰的选项可以写到这⾥边来,skip=1 ⽤来跳过数据中的第⼀⾏
LOAD DATA
INFILE * -- 因为数据同控制⽂件在⼀起，所以⽤ * 表⽰
append -- 这⾥⽤了 append 来操作，在表 users 中附加记录
INTO TABLE users
when LOGIN_TIMES<>'8' -- 还可以⽤ when ⼦句选择导⼊符合条件的记录
Fields terminated by ","
trailing nullcols
(
virtual_column FILLER, --跳过由 PL/SQL Developer ⽣成的第⼀列序号
user_id "user_seq.nextval", --这⼀列直接取序列的下⼀值，⽽不⽤数据中提供的值
user_name "'Hi '||upper(:user_name)",--,还能⽤SQL函数或运算对数据进⾏加⼯处理
login_times terminated by ",", NULLIF(login_times='NULL') --可为列单独指定分隔符
last_login DATE "YYYY-MM-DD HH24:MI:SS" NULLIF (last_login="NULL") -- 当字段为"NULL"时就是 NULL
)
BEGINDATA --数据从这⾥开始
,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN
1,1,Unmi,3,2009-1-5 20:34
2,2,Fantasia,5,2008-10-15
3,3,隔叶黄莺,8,2009-1-2
4,4,Kypfos,NULL,NULL
5,5,不知秋,1,2008-12-23
2) 执⾏⼀样的命令：
sqlldr control=users.ctl
⽐如，在控制台会显⽰这样的信息：
上⾯的控制⽂件包含的内容⽐较复杂(演⽰⽬的)，请根据注释理解每个参数的意义。

还能由此发掘更多⽤法。

最后说下有关 SQL *Loader 的性能与并发操作
1) ROWS 的默认值为 64，你可以根据实际指定更合适的 ROWS 参数来指定每次提交记录数。

(体验过在 PL/SQL Developer 中⼀次执⾏⼏条条以上的 insert 语句的情形吗？)
2）常规导⼊可以通过使⽤ INSERT语句来导⼊数据。

Direct导⼊可以跳过数据库的相关逻辑(DIRECT=TRUE)，⽽直接将数据导⼊到数据⽂件中，可以提⾼导⼊数据的性能。

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

3) 通过指定 UNRECOVERABLE选项，可以关闭数据库的⽇志(是否要 alter table table1 nologging 呢?)。

这个选项只能和 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共同应⽤。

在并发操作时，ORACLE声称可以达到每⼩时处理100GB数据的能⼒！其实，估计能到 1－10G 就算不错了，开始可⽤结构相同的⽂件，但只有少量数据，成功后开始加载⼤量数据，这样可以避免时间的浪费。

SQLLDR keyword=value [,keyword=value,...]
有效的关键字:
userid -- ⽤户名/⼝令
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)
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' 的位置正确。

实践
LOAD DATA
INFILE 'result/stat-20100821-detail.txt'
replace
into table LOGDETAILS_20100821 -- insert append replace
FIELDS TERMINATED BY ' |+-s| ' --字段分割符号
TRAILING NULLCOLS --允许匹配不到的字段
(
ID RECNUM , --RECNUM属性来实现id的⾃增如果⼊库⽅式是追加⼀定要使⽤序列 COUNTERINFO_SEQ.nextval
IP ,
HITTIME "to_date(:HITTIME, 'YYYY-MM-DD HH24:Mi:SS')",
URL ,
STATUS ,
SIZES ,
URL_SOURCE char(1024),
TOOLS char(1024),
SERVERINFO ,
SYSTEM_ID ,
SOURCETYPE_ID ,
SERVERSOURCE
)
整理问题
value used for ROWS parameter changed from 64 to 58
Record 49305: Rejected - Error on table LOGDETAILS_20100822, column SERVERSOURCE.
ORA-12899: value too large for column "LOGANALYSER"."LOGDETAILS_20100822"."SERVERSOURCE" (actual: 71, maximum: 50) Record 49561: Rejected - Error on table LOGDETAILS_20100822, column SERVERSOURCE.--字段长度
ORA-12899: value too large for column "LOGANALYSER"."LOGDETAILS_20100822"."SERVERSOURCE" (actual: 71, maximum: 50) ORA-01653: unable to extend table LOGANALYSER.LOGDETAILS_20100822 by 128 in tablespace TBS_LOGANALYSER --表空间配额不⾜
SQL*Loader-605: Non-data dependent ORACLE error occurred -- load discontinued.
特殊说明
使⽤SQLLOAD导⼊数据时四种装⼊⽅式导⼊数据
APPEND //原先的表有数据就加在后⾯
INSERT // 装载空表,如果原先的表有数据SQLLOADER会停⽌默认值
REPLACE // 原先的表有数据原先的数据会全部删除
TRUNCATE // 指定的内容和REPLACE的相同会⽤TRUNCATE语句删除
使⽤SQLLOAD导⼊数据时不导⼊从⽂件头开始的指定⾏数据：sqlldr
使⽤SQL*LOAD⼀次导⼊多个⽂件到同⼀个表:
LOAD DATA
INFILE file1.dat
INFILE file2.dat
INFILE file3.dat
APPEND
INTO TABLE emp
( empno POSITION(1:4) INTEGER EXTERNAL, --字段长度确定时，使⽤POSITION定位
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
使⽤when条件指定SQL*LOAD将⼀个⽂件导⼊不同的表: INTO TABLE tablename WHEN filed = '条件'使⽤SQL*LOAD有选择性的导⼊数据到⼀个或多个表中：
注:
SQL*LOAD不允许在when语句中使⽤OR...只能使⽤AND
(01) 表⽰数据⽂件中的第⼀个字符
(30:37) 表⽰数据⽂件中第30-37个字符
LOAD DATA
INFILE 'mydata.dat' BADFILE 'mydata.bad' DISCARDFILE 'mydata.dis'
APPEND
INTO TABLE my_selective_table
WHEN (01) <> 'H' and (01) <> 'T'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
INTO TABLE my_selective_table
WHEN (30:37) = '20031217'
(
region CONSTANT '31',
service_key POSITION(01:11) INTEGER EXTERNAL,
call_b_no POSITION(12:29) CHAR
)
使⽤SQL*LOAD导⼊数据时,通过在字段上使⽤关键字过滤数据⽂件中的指定列： field2 FILLER
使⽤SQL*LOAD导⼊数据时,通过使⽤关键字表⽰把多⾏记录合并成⼀⾏：CONCATENATE 3
使⽤SQL*LOAD导⼊LOB等⼤对象:
CREATE TABLE image_table (
image_id NUMBER(5),
file_name VARCHAR2(30),
image_data BLOB);
LOAD DATA
INFILE *
INTO TABLE image_table
REPLACE
FIELDS TERMINATED BY ','
(
image_id INTEGER(5),
file_name CHAR(30),
image_data LOBFILE (file_name) TERMINATED BY EOF )
BEGINDATA
001,image1.gif
002,image2.jpg
003,image3.jpg。