[转]关于 Oracle 的数据导入导出及 Sql Loader (sqlld

时间:2020-04-30 22:59来源:计算机教程
oracle提供了sqlldr的工具,有时需要讲数据导入到文本,oracle的spool可以轻松实现。 关于 Oracle 的数据导入导出及 Sql Loader (sqlldr)的用法 方便的实现oracle导出数据到txt、txt导入数据到orac

oracle提供了sqlldr的工具,有时需要讲数据导入到文本,oracle的spool可以轻松实现。

关于 Oracle 的数据导入导出及 Sql Loader (sqlldr) 的用法

方便的实现oracle导出数据到txt、txt导入数据到oracle。

原文地址:http://www.blogjava.net/Unmi/archive/2009/01/05/249956.html

一、导出数据到txt

在 Oracle 数据库中,我们通常在不同数据库的表间记录进行复制或迁移时会用以下几种方法:

用all_objects表做测试

  1. A 表的记录导出为一条条分号隔开的 insert 语句,然后执行插入到 B 表中
  2. 建立数据库间的 dblink,然后用 create table B as select * from A@dblink where ...,或 insert into B select * from A@dblink where ...
  3. exp A 表,再 imp 到 B 表,exp 时可加查询条件
  4. 程序实现 select from A ..,然后 insert into B ...,也要分批提交
  5. 再就是本篇要说到的 Sql Loader(sqlldr) 来导入数据,效果比起逐条 insert 来很明显
SQL desc all_objects; Name Null Type ----------------------------------------- -------- ---------------------------- OWNER NOT NULL VARCHAR2(30) OBJECT_NAME NOT NULL VARCHAR2(30) SUBOBJECT_NAME VARCHAR2(30) OBJECT_ID NOT NULL NUMBER DATA_OBJECT_ID NUMBER OBJECT_TYPE VARCHAR2(19) CREATED NOT NULL DATE LAST_DDL_TIME NOT NULL DATE TIMESTAMP VARCHAR2(19) STATUS VARCHAR2(7) TEMPORARY VARCHAR2(1) GENERATED VARCHAR2(1) SECONDARY VARCHAR2(1)

第 1 种方法在记录多时是个噩梦,需三五百条的分批提交,否则客户端会死掉,而且导入过程很慢。如果要不产生 REDO 来提高 insert into 的性能,就要下面那样做:

拿object_id,object_name做导出、导入测试。

view source

一些设置满足数据导出的样式:

print?

vi exp_table.sql

1.``alter table B nologging;

set line 1000 --设置行的长度set pagesize 0 --输出不换页set feedback off --默认的当一条sql发出的时候,oracle会给一个反馈,比如说创建表的时候,如果成功命令行会返回类似:Table created的反馈,off后不显示反馈set heading off --不显示表头信息set trimspool on --如果trimspool设置为on,将移除spool文件中的尾部空set trims on --去掉空字符set echo off; --显示start启动的脚本中的每个sql命令,缺省为onset colsep '|' --设置分隔符set termout off --不在屏幕上显示结果spool db1.txt --记录数据到db1.txtselect object_id,object_name from all_objects; --导出数据语句spool off --收集完毕exit

2.``insert /* APPEND */ ``into B(c1,c2) ``values``(x,xx);

一切就绪后导出数据:

3.``insert /* APPEND */ ``into B ``select * ``from A@dblink ``where .....;

[oracle@centos5 ~]$ sqlplus test/test @exp_table.sql SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 13 16:35:14 2013Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsDisconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options[oracle@centos5 ~]$ sed -i 's/ //g' db1.txt --可选,去除每行开头部分的空格[oracle@centos5 ~]$ more db1.txt 20|ICOL$44|I_USER128|CON$15|UNDO$29|C_COBJ#3|I_OBJ#25|PROXY_ROLE_DATA$

好啦,前面简述了 Oracle 中数据导入导出的各种方法,我想一定还有更高明的。下面重点讲讲 Oracle  的 Sql Loader (sqlldr) 的用法。

导出后检查数据的记录数是否正确

在命令行下执行 Oracle  的 sqlldr 命令,可以看到它的详细参数说明,要着重关注以下几个参数:

[oracle@centos5 ~]$ cat db1.txt |wc -l49988[oracle@centos5 ~]$ sqlplus test/testSQL*Plus: Release 10.2.0.4.0 - Production on Thu Jun 13 16:36:21 2013Copyright (c) 1982, 2007, Oracle. All Rights Reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL select count(*) from all_objects; COUNT(*)---------- 49988 --数据正确

userid -- Oracle 的 username/password[@servicename]

二、从txt导入数据到oracle

control -- 控制文件,可能包含表的数据

log -- 记录导入时的日志文件,默认为 控制文件(去除扩展名).log
bad -- 坏数据文件,默认为 控制文件(去除扩展名).bad
data -- 数据文件,一般在控制文件中指定。用参数控制文件中不指定数据文件更适于自动操作
errors -- 允许的错误记录数,可以用他来控制一条记录都不能错
rows -- 多少条记录提交一次,默认为 64
skip -- 跳过的行数,比如导出的数据文件前面几行是表头或其他描述

还有更多的 sqlldr 的参数说明请参考:sql loader的用法

用例子来演示 sqlldr 的使用,有两种使用方法:

  1. 只使用一个控制文件,在这个控制文件中包含数据
  2. 使用一个控制文件(作为模板) 和一个数据文件

一般为了利于模板和数据的分离,以及程序的不同分工会使用第二种方式,所以先来看这种用法。数据文件可以是 CSV 文件或者以其他分割符分隔的,数据文件可以用 PL/SQL Developer 或者 Toad 导出,也可以用 SQL *Plus 的  spool 格式化产出,或是 UTL_FILE 包生成。另外,用 Toad 还能直接生成包含数据的控制文件。

首先,假定有这么一个表 users,并插入五条记录:

view source

print?

1.``create table users(

2.``    ``user_id number,           ``--用户 ID

3.``    ``user_name varchar2(50),   ``--用户名

4.``    ``login_times number,       ``--登陆次数

5.``    ``last_login ``date           --最后登录日期

6.``)

view source

print?

1.``insert into users ``values``(1,``'Unmi'``,3,sysdate);

2.``insert into users ``values``(2,``NULL``,5,to_date(``'2008-10-15'``,``'YYYY-MM-DD'``));

3.``insert into users ``values``(3,``'隔叶黄莺'``,8,to_date(``'2009-01-02'``,``'YYYY-MM-DD'``));

4.``insert into users ``values``(4,``'Kypfos'``,``NULL``,``NULL``);

5.``insert into users ``values``(5,``'不知秋'``,1,to_date(``'2008-12-23'``,``'YYYY-MM-DD'``));

第二种方式: 使用一个控制文件(作为模板) 和一个数据文件

1) 建立数据文件,我们这里用 PL/SQL Developer 导出表 users 的记录为 users_data.csv 文件,内容如下:

view source

print?

1.``"   "``,``"USER_ID"``,``"USER_NAME"``,``"LOGIN_TIMES"``,``"LAST_LOGIN"

2.``"1"``,``"1"``,``"Unmi"``,``"3"``,``"2009-1-5 20:34:44"

3.``"2"``,``"2"``,``""``,``"5"``,``"2008-10-15"

4.``"3"``,``"3"``,``"隔叶黄莺"``,``"8"``,``"2009-1-2"

5.``"4"``,``"4"``,``"Kypfos"``,``""``,``""

6.``"5"``,``"5"``,``"不知秋"``,``"1"``,``"2008-12-23"

2) 建立一个控制文件 users.ctl,内容如下:

view source

print?

01.``OPTIONS (skip=1,``rows``=128) ``-- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行

02.``LOAD DATA

03.``INFILE ``"users_data.csv" --指定外部数据文件,可以写多个 INFILE "another_data_file.csv" 指定多个数据文件

04.``--这里还可以使用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件,

05.``truncate --操作类型,用 truncate table 来清除表中原有记录

06.``INTO TABLE users ``-- 要插入记录的表

07.``Fields terminated ``by "," -- 数据中每行记录用 "," 分隔

08.``Optionally enclosed ``by '"' -- 数据中每个字段用 '"' 框起,比如字段中有 "," 分隔符时

09.``trailing nullcols ``--表的字段没有对应的值时允许为空

10.``(

11.``  ``virtual_column FILLER, ``--这是一个虚拟字段,用来跳过由 PL/SQL Developer 生成的第一列序号

12.``  ``user_id number, ``--字段可以指定类型,否则认为是 CHARACTER 类型, log 文件中有显示

13.``  ``user_name,

14.``  ``login_times,

15.``  ``last_login ``DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式,相当用 to_date() 函数转换

16.``)

说明:在操作类型 truncate 位置可用以下中的一值:

1) insert     --为缺省方式,在数据装载开始时要求表为空
2) append  --在表中追加新记录
3) replace  --删除旧记录(用 delete from table 语句),替换成新装载的记录
4) truncate --删除旧记录(用 truncate table 语句),替换成新装载的记录

3) 执行命令:

sqlldr dbuser/dbpass@dbservice 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 文件内容是:

view source

print?

01.``OPTIONS (skip=1,``rows``=128) ``-- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行

02.``LOAD DATA

03.``INFILE *  ``-- 因为数据同控制文件在一起,所以用 * 表示

04.``append    ``-- 这里用了 append 来操作,在表 users 中附加记录

05.``INTO TABLE users

06.``when LOGIN_TIMES<>``'8'  -- 还可以用 when 子句选择导入符合条件的记录

07.``Fields terminated ``by ","

08.``trailing nullcols

09.``(

10.``  ``virtual_column FILLER, ``--跳过由 PL/SQL Developer 生成的第一列序号

11.``  ``user_id ``"user_seq.nextval"``, ``--这一列直接取序列的下一值,而不用数据中提供的值

12.``  ``user_name ``"'Hi '||upper(:user_name)"``,``--,还能用SQL函数或运算对数据进行加工处理

13.``  ``login_times terminated ``by ","``, ``NULLIF``(login_times=``'NULL'``) ``--可为列单独指定分隔符

14.``  ``last_login ``DATE "YYYY-MM-DD HH24:MI:SS" NULLIF (last_login=``"NULL"``) ``-- 当字段为"NULL"时就是 NULL

15.``)

16.``BEGINDATA ``--数据从这里开始

17.``   ``,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN

18.``1,1,Unmi,3,2009-1-5 20:34

19.``2,2,Fantasia,5,2008-10-15

20.``3,3,隔叶黄莺,8,2009-1-2

21.``4,4,Kypfos,``NULL``,``NULL

22.``5,5,不知秋,1,2008-12-23

2) 执行一样的命令:

sqlldr dbuser/dbpass@dbservice control=users.ctl

比如,在控制台会显示这样的信息:

C:>sqlldr dbuser/dbpass@dbservice control=users.ctl

 

SQL*Loader: Release 9.2.0.1.0 - Production on 星期三 1月 7 22:26:25 2009

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

达到提交点,逻辑记录计数4
达到提交点,逻辑记录计数5

上面的控制文件包含的内容比较复杂(演示目的),请根据注释理解每个参数的意义。还能由此发掘更多用法。

最后说下有关 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 就算不错了,开始可用结构 相同的文件,但只有少量数据,成功后开始加载大量数据,这样可以避免时间的浪费。

参考:1. Oracle SQL*Loader  -- 英文,Sql Loader 的官方使用说明,包含多种类型的  Demo
        2. sql loader的用法  -- 列出了 sql loader 的选择参数的中文说明
        3. 使用SQL Loader导入大量数据,避免使用SQL频繁写库 -- 一个简单的例子,快带了解 Sql Loader 的用法
        4. Oracle SQL Loader的详细语法
        5. oracle sql loader全攻略 -- 还算名符其实。并讲了如何用 SQL *Plus 的  spool 或 UTL_FILE 包生成数据文件
        6. SQL*Loader Control File Reference   -- 英文,控制文件使用参考
        7. 学习oracle sql loader 的使用 
        8. 用sqlloader(sqlldr)装载LOB数据  -- LOB 的内类是一个外部文件,用 sql loader 导入到数据库
        9. SQLLDR直接加载几个参数的测试
        10.Maximizing SQL*Loader Performance

sqlldr是通过一个control文件设定后,从文本导入数据

建立一张测试表

SQL create table tb_sqlldr (id number,name varchar2(50));Table created.

建立一个control文件

vi tb_sqlldr.ctl

编辑:计算机教程 本文来源:[转]关于 Oracle 的数据导入导出及 Sql Loader (sqlld

关键词:

  • 上一篇:没有了
  • 下一篇:没有了