Chinaunix首页 | 论坛 | 博客
  • 博客访问: 274592
  • 博文数量: 20
  • 博客积分: 307
  • 博客等级: 入伍新兵
  • 技术积分: 832
  • 用 户 组: 普通用户
  • 注册时间: 2012-07-03 23:52
文章分类

全部博文(20)

文章存档

2013年(18)

2012年(2)

分类: Oracle

2013-02-27 17:55:15

今天公司需要把外部文本的一些数据导入到数据库。这里把相关步骤和注意的地方记录,供需要的人参考学习!这里的环境是在windows下的数据库,linux或者其他数据库同理!

1、准备工作:创建需要导入数据的表结构,如果已有相关的表,此步省略!

SQL> create table test
  2  (
  3    host          VARCHAR2(30),
  4    user_name VARCHAR2(30),
  5    ip_address      VARCHAR2(15),
  6    pass            VARCHAR2(4) default 'no' not null,
  7    judge           NUMBER default 0 not null,
  8    endtime         DATE
  9  );

表已创建。

2、编写sqlload导入数据的控制文件,这里测试的控制文件如下,可以根据自己需要添加相关的控制参数,测试的话复制保存为txt文件即可!


  1. LOAD DATA
  2. INFILE 'd:\data.txt'
  3. INTO TABLE test
  4. TRUNCATE
  5. fields terminated by ','
  6. trailing nullcols
  7. (HOST,USER_NAME,IP_AddrESS,PASS,JUDge,endTIME)

控制文件还有其他参数,根据自己需求调整和测试:

附部分控制参数:具体用法以官方文档为准

OPTIONS (skip=1,rows=128)   -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA
INFILE "users_data.csv"     --指定外部数据文件,可以是不同格式的数据文件,如csv、txt都支持

                                        可以写多个 INFILE "another_data_file.csv" 指定多个数据文件
truncate                          --操作类型,用 truncate table 来清除表中原有记录,根据情况而定是否需要清楚原有表中数据
INTO TABLE users             --要插入记录的表
Fields terminated by ","      --数据中每行记录用 "," 分隔
Optionally enclosed by '"'    --数据中每个字段用 '"' 框起,比如字段中有 "," 分隔符时
trailing nullcols                   --表的字段没有对应的值时允许为空
(
  virtual_column FILLER,    --这是一个虚拟字段,用来跳过由 PL/SQL Developer 生成的第一列序号
  user_id number,           --字段可以指定类型,否则认为是 CHARACTER 类型, log 文件中有显示
  user_name,
  login_times,
  last_login DATE "YYYY-MM-DD HH24:MI:SS" -- 指定接受日期的格式,相当用 to_date() 函数转换

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

3、创建需要导入的数据,注意数据格式必须和表结构严格对应,否则导入失败!测试数据如下:有部分数据最后字段为空,所以控制文件中需要加trailing nullcols 参数!



  1. ttt,SCOTT,192.168.1.111,yes,1,
  2. qq,JACK,192.168.1.20,no,1,
  3. YY,TOM,192.168.1.20,no,1,
  4. WEB1,HAHA,192.168.1.1,no,1,
  5. XXX,ROBIN,111.111.111.111,no,1,08-AUG-08
  6. DB2,LUCY,192.168.10.10,no,1,
  7. ORACLE,LILY,222.222.222.222,no,1,
  8. WORKGROUP,DENNIS,133.133.133.133,no,0,08-AUG-08
  9. DCR,CANDY,192.168.100.10,no,1,
  10. T3,FLY,192.168.10.33,no,1,
  11. T1,LINDA,192.168.10.200,no,1,08-AUG-08
  12. T2,LILEI,192.168.100.31,no,1,08-AUG-08


4、导入数据-导入时也有相关的参数进行控制

附部分导入参数:各参数可以再命令行下输入sqlldr查看

C:\Documents and Settings\Administrator>sqlldr

SQL*Loader: Release 11.2.0.1.0 - Production on 星期三 2月 27 17:13:24 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

用法: SQLLDR keyword=value [,keyword=value,...]

有效的关键字:

    userid -- ORACLE 用户名/口令
   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)
no_index_errors -- 出现任何索引错误时中止加载  (默认 FALSE)

 

如下命令:control=指定控制文件和路径  log=导入日志文件保存  bad=错误信息  data=数据文件

开始导入:

C:\Documents and Settings\Administrator>sqlldr scott/tiger control=d:\sqlload.txt log=d:\loadlog.txt bad=d:\bad.txt data=d:\data.txt
SQL*Loader: Release 11.2.0.1.0 - Production on 星期三 2月 27 17:06:52 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

达到提交点 - 逻辑记录计数 12

导入成功后查看结果:

C:\Documents and Settings\Administrator>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 2月 27 17:07:05 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> select * from test;

HOST       USER_NAME  IP_ADDRESS                     PASS          JUDGE ENDTIME
----------      ----------      ------------------------------         --------           ---------- --------------
ttt             SCOTT      192.168.1.111                  yes               1
qq             JACK         192.168.1.20                    no                1
YY            TOM          192.168.1.20                    no                1
WEB1        HAHA        192.168.1.1                     no                1
DB2           LUCY         192.168.10.10                 no                1
ORACLE     LILY           222.222.222.222             no                1
DCR          CANDY       192.168.100.10                no                1
T3            FLY           192.168.10.33                  no                1

已选择8行。

结果显然与数据文件不一致,最后一个字段有值的数据没有导入!这不是最后想要的结果!但可以确定已经有数据导入,表示表对象和控制文件没问题!

应该是数据文件的格式问题!注意数据文件最后一个字段是日期型数据。查看是不是数据库现有日期类型不支持数据文件的表示格式


  1. SQL> select sysdate from dual;

  2. SYSDATE
  3. --------------
  4. 27-2月 -13

显然和我们数据文件的日期显示不一致,且是中文,这里可以把数据文件的最后字段的数据改成和数据库一样的格式,也可以对数据库的格式和语言进行更改,

因为测试系统是windows才会出现这个错误,在linux可以避免!

如果是linux  可以尝试以下步骤解决:

alter system set nls_date_format='DD-MON-RR';

alter system set nls_language= american scope = spfile;------------得重启数据库

这里由于是windows,这里就不更改语言和重启数据库,直接对数据文件进行更改,修改后的数据文件如下



  1. ttt,SCOTT,192.168.1.111,yes,1,
  2. qq,JACK,192.168.1.20,no,1,
  3. YY,TOM,192.168.1.20,no,1,
  4. WEB1,HAHA,192.168.1.1,no,1,
  5. XXX,ROBIN,111.111.111.111,no,1,08-5月 -08
  6. DB2,LUCY,192.168.10.10,no,1,
  7. ORACLE,LILY,222.222.222.222,no,1,
  8. WORKGROUP,DENNIS,133.133.133.133,no,0,08-5月 -08
  9. DCR,CANDY,192.168.100.10,no,1,
  10. T3,FLY,192.168.10.33,no,1,
  11. T1,LINDA,192.168.10.200,no,1,08-5月 -08
  12. T2,LILEI,192.168.100.31,no,1,08-5月 -08


再导入一次,导入后进行查看结果

 

C:\Documents and Settings\Administrator>sqlldr scott/tiger control=d:\sqlload.txt log=d:\loadlog.txt bad=d:\bad.txt data=d:\data.txt

SQL*Loader: Release 11.2.0.1.0 - Production on 星期三 2月 27 17:48:44 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

达到提交点 - 逻辑记录计数 12

 

C:\Documents and Settings\Administrator>sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on 星期三 2月 27 17:49:21 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> col host for a10
SQL> col user_name for a15
SQL> select * from test;

HOST           USER_NAME       IP_ADDRESS                     PASS          JUDGE ENDTIME
----------        --------------- ------------------------------                --------         ---------- --------------
ttt               SCOTT           192.168.1.111                      yes               1
qq               JACK            192.168.1.20                          no                1
YY              TOM             192.168.1.20                          no                1
WEB1          HAHA            192.168.1.1                           no                1
XXX             ROBIN           111.111.111.111                    no                1 08-5月 -08
DB2             LUCY            192.168.10.10                        no                1
ORACLE       LILY            222.222.222.222                      no                1
WORKGROUP  DENNIS          133.133.133.133                 no                0 08-5月 -08
DCR            CANDY           192.168.100.10                     no                1
T3              FLY             192.168.10.33                         no                1
T1             LINDA           192.168.10.200                       no                1 08-5月 -08
T2             LILEI           192.168.100.31                         no                1 08-5月 -08

已选择12行。

恢复正常:数据和数据文件完全一样!到此整个数据导入完成!主要注意的地方还是数据文件的建立,确认分隔以及和表的结构对应!欢迎指正学习!

 

 

 

阅读(19206) | 评论(2) | 转发(0) |
给主人留下些什么吧!~~

凤凰翡翠2017-08-23 17:09:04

这种方法对大数据初始化导入生产环境非常有用,比较快速

CU博客助理2013-04-09 14:00:03

嘉宾点评: 在我的工作中也经常用到SQLLOAD工具,这里作者采用了一个实例演示如何使用oracle提供的数据库加载工具SQLLOAD将文本文件加载到数据表中,在导入过程中,博主特别提到了时间格式的问题(这个对初学者可能会忽略,导致时间数据无法导读).总的来说,文章井井有条,通俗易懂,希望博主再接再励.
(以上是邀请嘉宾的点评,感谢您参与“原创博文评选”获奖结果即将公布)