合 Oracle工具之sqlldr的使用
- 简介
- OCP
- 使用CONCATENATE合并多行记录
- 控制根据数据不同插入不同的表
- 处理定长记录
- 处理变长记录(从access到oracle)
- Oracle sqlldr使用的几个注意事项
- 一、sqlldr的命令帮助信息
- 1.1简单例子
- 1.2控制文件解析
- 1.3日志文件解析
- 1.4错误文件解析
- 1.5废弃文件解析
- 2.1 Excel文件
- 2.2要加载的文件不是以逗号分隔
- 2.3要加载的数据中包含分隔符
- 2.4数据文件没有分隔符
- 2.5数据文件中的列比要导入的表中的列少
- 2.6数据文件中的列比要导入的表中列多
- 3.1多个数据文件,导入同一张表
- 3.2同一个数据文件,导入不同表
- 3.3数据文件前N行不想导入
- 3.4加载的数据中有换行符
- 3.4.1手工指定的换行符
- 3.4.2指定FIX属性处理换行符
- 3.4.3指定VAR属性处理换行符(行头部标识换行)
- 3.4.4指定STR属性处理换行符(行尾部标识换行)
简介
有多种方式可以将文本文件的数据导入到数据库中,例如,利用PLSQL Developer软件进行复制粘贴,利用外部表,利用SQL*Loader
等方式。至于EXCEL中的数据可以另存为csv文件(csv文件其实是逗号分隔的文本文件),然后导入到数据库中。
下面简单介绍一下SQL*Loader
的使用方式。
能够接收多种不同格式的数据文件。文件可以存储在磁盘或磁带上,或记录本身可以被嵌套到控制文件中。记录格式可以是定长的或变长的,定长记录是指这样的记录:每条记录具有相同的固定长度,并且每条记录中的数据域也具有相同的固定长度、数据类型和位置。SQL*Loader
是一个Oracle工具,能够将数据从外部数据文件装载到数据库中。
SQL*Loader必须包含一个控制文件,该控制文件是
SQL*Loader的中枢核心,控制文件能够控制外部数据文件中的数据如何映射到Oracle的表和列。通常与SPOOL导出文本数据方法配合使用。
SQL*Loader
SQL*Loader
的数据导入比较专业,有各种参数及选项可供选择,经常是作为数据仓库中大型数据的导入方法选择。
SQL*Loader
的优点:
1、可将导入命令写入BAT文件直接批量处理
2、导入处理比较专业,提供各种参数选择
3、无需操作Oracle所在服务器
SQL*Loader
也有缺点,例如,Excel文件需要另存为txt或csv格式才能导入到数据库中。
总得来说这种方法是最值得采用的,可以自动建立操作系统的批处理文件执行SQL*Loader
命令,将数据导入原始接收表,并在数据库中设置触发器进行精细操作。
SQL*Loader
有两种使用方法:
1、只使用一个控制文件,在这个控制文件中包含数据
2、使用一个控制文件和一个数据文件
SQL*Loader工具使用的命令为sqlldr,其常用参数的含义如下表所示:
下面给出SQL*Loader
控制文件的一个示例:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | options(SKIP=1,errors=1000) --options(SKIP=1,ROWS=1000,errors=1000) UNRECOVERABLE --不产生日志 load data --CHARACTERSET utf8 --ZHS16GBK LENGTH CHARACTER infile 'E:\sql\sqlldr\test.csv' APPEND into table zh_lhr --insert/APPEND/replace fields terminated by ',' optionally enclosed by '"' ---②terminated by x'09' 一个制表符(TAB) trailing nullcols ( position(*:16) "TRIM(:COL_5)" "SEQ.NEXTVAL" date 'YYYY/MM/DD HH24:MI:SS', rn , IMIX_TAG char(4000) "trim(:IMIX_TAG)" ) |
其中,CHARACTERSET指定文件的编码格式,infile指定导入的文件。
接下来就是执行导入命令了,如下所示:
1 | sqlldr 用户名/用户名密码@数据库名称 control= 控制文件名.ctl parallel=y log='log.txt' bad='bad.bad' direct=true readsize=4194304 |
当要加载的数据文件比较大的时候该如何提高SQL*Loader
的性能呢?可以从以下几个方面考虑:
① ROWS的默认值为64,可以根据需要指定更合适的ROWS参数来指定每次提交记录数。
② 采用DIRECT=TRUE导入可以跳过数据库的相关逻辑,直接将数据导入到数据文件中,可以提高导入数据的性能。
③ 通过指定UNRECOVERABLE选项,可以写少量的日志,而从提高数据加载的性能。不过,推荐在加载完成后立即对数据库或至少对备份。
当加载大量数据时,最好抑制日志的产生:
1 | ALTER TABLE RESULTXT NOLOGGING; |
将表修改为NOLOGGING,可以只产生少量的Redo日志,从而提高导入效率。在CONTROL文件中的load data前边加一行:UNRECOVERABLE,此选项必须要与DIRECT共同使用。对于超大数据文件的导入就要用并发操作了,即同时运行多个导入任务:
1 | sqlldr userid=/ control=result1.ctl direct=true parallel=true |
下表给出在使用SQL*Loader
的过程中,一些常用的需求实现方法:
下表给出了在使用SQL*Loader
的过程中,经常会遇到的一些错误及其解决方法:
OCP
关于SQL*Loader
还有很多参数本书不再详述,具体可以参考官方文档。有关如何导出数据到EXCEL中,本书也不再详述。读者若有需要可以关注作者的微信公众号来阅读。
真题1、Which two statements are true regarding the usage of the SQL*Loader
utility? (Choose two.)
A、You can load data into multiple tables during the same load session.
B、You can load data from multiple files to a table during the same load session.
C、You cannot perform selective data loading based on the values available in the records.
D、You can use an export file generated by the EXPDP utility as an input data file to load the data.
答案:A、B。
题目中要求选出关于SQL*Loader
工具的描述中正确的两个选项。
本题中,对于选项A,可以在一个SQL*Loader
会话中导入多个表的数据,根据前面的分析,该功能可以实现。所以,选项A正确。
对于选项B,可以在一个SQL*Loader
会话中将多个数据文件导入到1个表中,根据前面的分析,该功能可以实现。所以,选项B正确。
对于选项C,在导入数据的时候可以使用WHEN关键词实现选择性的导入。所以,选项C错误。
对于选项D,SQL*Loader
是文本导入工具,只能导入文本数据,而exp导出的文件属于二进制文件,所以不能导入。所以,选项D错误。
所以,本题的答案为A、B。
真题2、Which statement is true about loading data using the conventional path of SQL*Loader
?
A、Redo is not generated while performing conventional path loads.
B、Only PRIMARY KEY, UNIQUE KEY, and NOT NULL constraints are checked.
C、The SQL*Loader
control file is a text file that contains data definition language (DDL) instructions.
D、Instead of performing transactions, SQL*Loader
directly writes data blocks to the data files.
E、INSERT triggers are disabled before the conventional path load and reenabled at the end of the load.
答案:C。
题目要求选出关于SQL*Loader
工具的常规路径加载说法正确的选项。
本题中,对于选项A,常规路径加载的时候会产生Redo日志,选项A错误。
对于选项B,所有的约束均有效,而不止PRIMARY KEY、UNIQUE KEY和NOT NULL这3项约束。所以,选项B错误。
对于选项C,SQL*Loader
的控制文件是一个文本文件,其中包含了一些DDL结构。所以,选项C正确。
对于选项D,SQL*Loader
的常规路径加载是经过内存的,而不是直接写入数据文件。所以,选项D错误。
对于选项E,SQL*Loader
加载的时候,表上的相关触发器是会执行的。所以,选项E错误。
所以,本题的答案为C。
使用CONCATENATE合并多行记录
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | create table scott.tb_manager(mgrno number,mname varchar2(30),job varchar2(300),remark varchar2(4000)); #a.ctl LOAD DATA INFILE 'a.dat' CONCATENATE 2 INTO TABLE tb_manager truncate ( MGRNO POSITION(1:2), MNAME POSITION(*+1:10), JOB POSITION(*+1:24), REMARK POSITION(*+1:65) "REPLACE(upper(:REMARK),'HE',chr(10)||'HE')" ) #a.dat 10 SMITH SALES MANAGER THIS IS SMITH. HE IS A SALES MANAGER. 11 ALLEN.W TECH MANAGER THIS IS ALLEN.W. HE IS A TECH MANAGER. 16 BLAKE HR MANAGER THIS IS BLAKE. HE IS A HR MANAGER. sqlldr scott/tiger control=a.ctl data=a.dat sqlplus / as sysdba set linesize 9999 col job format a30 col REMARK format a50 select * from scott.tb_manager; |
sqlldr userid=lgone/tiger control=a.ctl
其中,a.ctl为控制文件,内容:
LOAD DATA --控制文件标识
INFILE 't.dat' -- 要导入的文件
INFILE 'tt.dat' -- 导入多个文件
INFILE -- "" 代表要导入的内容就在control文件里,下面的BEGINDATA后面就是导入的内容
INSERT --数据载入方式,有四种
INTO TABLE table_name -- 指定装入的表
BADFILE 'c:bad.txt' --指定坏文件地址
LOGFILE 'c:log.txt' --log文件
DISCARD 'c:discard.txt' --包含丢弃数据的文件
ERRORS=1000 --指定错误的数目
可以使用sqlldr查看帮助。
以下是4种装入表的方式:
APPEND 原先的表有数据,就加在后面
INSERT 装载空表 如果原先的表有数据 sqlloader会停止,默认值
REPLACE 原先的表有数据,原先的数据会全部删除
TRUNCATE 指定的内容和replace的相同 会用truncate语句删除现存数据
指定的TERMINATED可以在表的开头,也可在表的内部字段部分:
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' --OPTIONALLY ENCLOSED BY是指字段值用什么括起来,平时我们都是用单引号,如'value'
装载这种数据: 10,lg,"""lg""","lg,lg"
在表中结果: 10 lg "lg" lg,lg
TERMINATED BY X'09' -- 表示字段之间通过制表符tab键(以十六进制格式 '09' 表示 )分割,也可以把FIELDS TERMINATED BY ';'分号表示成ascii编码
TERMINATED BY WRITESPACE -- 以空白分割,装载这种数据: 10 lg lg
(col_1 [interger external] TERMINATED BY ',' ,
col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,
col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg' )
当某列为空,不存在对应值:
TRAILING NULLCOLS --如果某个字段没有对应的值时(空格、空白、或null),被当作null列,向表中插入null
不导入某列:
( col_1 , col_2 ,col_filler FILLER --FILLER 关键字(不是filter)指定此列(第三列)的数值不会被装载,如: lg,lg,not,结果 lg lg)
也可以写成这样:( col_1 , col_2 ) --数据文件中存在超过两列数据,但是只导入前两列
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(DEPTNO, DNAME, LOC )
BEGINDATA
10,Sales,"""USA"""
20,Accounting,"Virginia,USA"
30,Consulting,Virginia
40,Finance,Virginia
50,"Finance","",Virginia --loc 列将为空
60,"Finance",,Virginia --loc 列将为空
跳过数据行,可以用 "SKIP n" 关键字来指定导入时可以跳过多少行数据。如:
INTO TABLE load_positional_data
SKIP 5
当没声明FIELDS TERMINATED BY ','用位置告诉字段装载数据:
(
col_1 position(1:2), --1到2,(01) 代表第一个字符
col_2 position(3:10), --3到10
col_3 position(:16), -- 这个字段的开始位置在前一字段的结束位置
col_4 position(1:16),
col_5 position(3:10) char(8) -- 指定字段的类型
)
BEGINDATA -- 对应开始的 INFILE 要导入的内容就在control文件里,注意begindata后的数值前面不能有空格
10,Sql,what
20,lg,show
使用函数(包括自定义函数)、日期的某种表达方式:
FIELDS TERMINATED BY ','
(DEPTNO,
DNAME "upper(:dname)", -- 使用函数
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy', --或'dd-month-yyyy'等,也可以使用自定义函数LAST_UPDATED "my_to_date( :last_updated )" , my_to_date()为自定义函数
ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"
)
载入有换行符的数据:
注意: unix 和 windows换行符不同 & /n
(......
COMMENTS "replace(:comments,'n',chr(10))" --replace 的使用帮助转换换行符
......
)
载入每行的行号:
......
( seqno RECNUM --载入每行的行号
text Position(1:20))
BEGINDATA
fsdfasj --自动分配一行号给载入 表t 的seqno字段, 此行行号为1
fasdjfasdfl --此行行号为2
合并多行记录为一行记录:
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 1.
LOAD DATA
INFILE
concatenate 3 --通过关键字concatenate 把几行的记录看成一行记录
INTO TABLE DEPT
......
(DEPTNO,
DNAME "upper(:dname)",
LOC "upper(:loc)",
LAST_UPDATED date 'dd/mm/yyyy'
)
BEGINDATA
10,Sales, --其实这3行看成一行 10,Sales,Virginia,1/5/2000
Virginia,
1/5/2000
这列子用 continueif list="," 也可以,告诉sqlldr在每行的末尾找逗号 找到逗号就把下一行附加到上一行.
LOAD DATA
INFILE
continueif this(1:1) = '-' -- 找每行的开始是否有连接字符'-',有就把下一行连接为一行:
如 -10,Sales,Virginia,
1/5/2000 就是一行 10,Sales,Virginia,1/5/2000
其中1:1 表示从第一行开始 并在第一行结束 还有continueif next, 但continueif list最理想
将数据导入多个表:
LOAD DATA
INFILE *
REPLACE
INTO TABLE emp
WHEN empno != ' '
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
)
INTO TABLE proj
WHEN projno != ' '
( projno POSITION(25:27) INTEGER EXTERNAL,
empno POSITION(1:4) INTEGER EXTERNAL
)
导入数据时修改数据:
在导入数据到数据库时,可以修改数据。注意,这仅适合于常规导入,并不适合 direct导入方式.
LOAD DATA
INFILE *
INTO TABLE modified_data
( rec_no "my_db_sequence.nextval",
region CONSTANT '31',
time_loaded "to_char(SYSDATE, 'HH24:MI')",
data1 POSITION(1:5) ":data1/100",
data2 POSITION(6:15) "upper(:data2)",
data3 POSITION(16:22)"to_date(:data3, 'YYMMDD')"
)
BEGINDATA
11111AAAAAAAAAA991201
22222BBBBBBBBBB990112
LOAD DATA
INFILE 'mail_orders.txt'
BADFILE 'bad_orders.txt'
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
)
SQL*Loader
数据的提交:
一般情况下是在导入数据文件数据后提交的。也可以通过指定 ROWS= 参数来指定每次提交记录数。
提高 SQL*Loader
的性能:
\1) 一个简单而容易忽略的问题是,没有对导入的表使用任何索引和/或约束(主键)。如果这样做,甚至在使用ROWS=参数时,会很明显降低数据库导入性能。
\2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然,在很多情况下,不能使用此参数。
\3) 通过指定 UNRECOVERABLE选项,可以关闭数据库的日志。这个选项只能和 direct 一起使用。
\4) 可以同时运行多个导入任务.
常规导入与direct导入方式的区别:
常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE),而直接将数据导入到数据文件中。
Oracle SQL*Loader 使用指南(转载)
如何使用 SQL*Loader
工具
我们可以用Oracle的sqlldr工具来导入数据。例如:
sqlldr scott/tiger control=loader.ctl
控制文件(loader.ctl)将加载一个外部数据文件(含分隔符)
loader.ctl如下:
load data
infile 'c:\data\mydata.csv'
into table emp
fields terminated by "," optionally enclosed by '"'
(empno, empname, sal, deptno)
mydata.csv 如下:
10001,"Scott Tiger", 1000, 40
10002,"Frank Naude", 500, 20
下面是一个指定记录长度的示例控制文件。“*” 代表数据文件与此文件同名,即在后面使用BEGINDATA段来标识数据。
load data
infile *
replace
into table departments
( dept position (02:05) char(4),
deptname position (08:27) char(20)
)
begindata
COSC COMPUTER SCIENCE
ENGL ENGLISH LITERATURE
MATH MATHEMATICS
POLY POLITICAL SCIENCE
Unloader这样的工具
Oracle没有提供将数据导出到一个文件的工具。但是我们可以用SQL*Plus的select及 format 数据来输出到一个文件:
set echo off newpage 0 space 0 pagesize 0 feed off head off trimspool on
spool oradata.txt
select col1 || ',' || col2 || ',' || col3
from tab1
where col2 = 'XYZ';
spool off
另外,也可以使用使用 UTL_FILE PL/SQL 包处理:
rem Remember to update initSID.ora, utl_file_dir='c:\oradata' parameter
declare
fp utl_file.file_type;
begin
fp := utl_file.fopen('c:\oradata','tab1.txt','w');
utl_file.putf(fp, '%s, %s\n', 'TextField', 55);
utl_file.fclose(fp);
end;
/
当然你也可以使用第三方工具,如SQLWays ,TOAD for Quest等。