Oracle工具之sqlldr的使用

0    498    1

Tags:

👉 本文共约37960个字,系统预计阅读时间或需143分钟。

简介

有多种方式可以将文本文件的数据导入到数据库中,例如,利用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,其常用参数的含义如下表所示:

Oracle工具之sqlldr的使用

下面给出SQL*Loader控制文件的一个示例:

其中,CHARACTERSET指定文件的编码格式,infile指定导入的文件。

接下来就是执行导入命令了,如下所示:

当要加载的数据文件比较大的时候该如何提高SQL*Loader的性能呢?可以从以下几个方面考虑:

① ROWS的默认值为64,可以根据需要指定更合适的ROWS参数来指定每次提交记录数。

② 采用DIRECT=TRUE导入可以跳过数据库的相关逻辑,直接将数据导入到数据文件中,可以提高导入数据的性能。

③ 通过指定UNRECOVERABLE选项,可以写少量的日志,而从提高数据加载的性能。不过,推荐在加载完成后立即对数据库或至少对备份。

当加载大量数据时,最好抑制日志的产生:

将表修改为NOLOGGING,可以只产生少量的Redo日志,从而提高导入效率。在CONTROL文件中的load data前边加一行:UNRECOVERABLE,此选项必须要与DIRECT共同使用。对于超大数据文件的导入就要用并发操作了,即同时运行多个导入任务:

下表给出在使用SQL*Loader的过程中,一些常用的需求实现方法:

Oracle工具之sqlldr的使用

下表给出了在使用SQL*Loader的过程中,经常会遇到的一些错误及其解决方法:

Oracle工具之sqlldr的使用

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合并多行记录

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等。

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!
Oracle工具之sqlldr的使用后续精彩内容已被小麦苗无情隐藏,请输入验证码解锁本站所有文章!
验证码:
请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“DB宝”或者“www_xmmup_com”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

嘿,我是小麦,需要帮助随时找我哦。
  • 18509239930
  • 个人微信

  • DB宝
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部