文本导入数据库表 | SQL Loader

一、sql loader 的特点

oracle自己带了很多的工具可以用来进行数据的迁移、备份和恢复等工作。但是每个工具都有自己的特点。

比如说：
　　1、exp和imp可以对数据库中的数据进行导出和导出的工作，是一种很好的数据库备份和恢复的工具，因此主要用在数据库的热备份和恢复方面。有着速度快，使用简单，快捷的优点；同时也有一些缺点，比如在不同版本数据库之间的导出、导入的过程之中，总会出现这样或者那样的问题，这个也许是oracle公司自己产品的兼容性的问题吧。
　　2、sql loader 工具却没有这方面的问题，它可以把一些以文本格式存放的数据顺利的导入到oracle数据库中，是一种在不同数据库之间进行数据迁移的非常方便而且通用的工具。缺点就速度比较慢，另外对blob等类型的数据就有点麻烦了。

二、语法讲解

本地（访问端）要安装Oracle客户端。

1、根据表接口创建控制文件conl.ctl，包含目标文件infile的路径

以“|”作为分隔，将数据插入到表t_cipan_test的字段(ip,paths,shijian date "yyyy-mm-dd hh24:mi:ss",total,used,free,user_rate,ID RECNUM)中，其中shijian字段进行了date转换

efile92@iomitfdb2:/interface/contabfile/diskspace >cat conl.ctl 
load data 
infile '/interface/contabfile/diskspace/diskspace.txt'  ----这里infile可以加载多个文件。
infile '/interface/contabfile/diskspace/diskspace2.txt'
append into table t_cipan_test
fields terminated by '|'
(ip,paths,shijian date "yyyy-mm-dd hh24:mi:ss",total,used,free,user_rate,ID RECNUM)
efile92@iomitfdb2:/interface/contabfile/diskspace >

更详细的**ctl脚本

OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行  
LOAD DATA  
INFILE "users_data.csv" --指定外部数据文件，可以写多 个 INFILE "another_data_file.csv" 指定多个数据文件  
--这里还可以使 用 BADFILE、DISCARDFILE 来指定坏数据和丢弃数据的文件，  
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() 函数转换  
)  

说 明：
在操作类型 truncate 位置可用以下中的一值：
1) insert     --为缺省方式，在数据装载开始时要求表为空
2) append  --在表中追加新记录
3) replace  --删除旧记录(用 delete from table 语句)，替换成新装载的记录
4) truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录

2、执行命令（在dos、linux、Aix等命令行执行）

语法结构：sqlldr userid=用户名/密码@实例 control=相对路径\文件名

#这里必须制定数据库信息
#必须引用控制文件
#可以不用定义log输出

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

sqlldr userid=BJIOM/Bjiom_0420@rman31 control=/interface/contabfile/diskspace/conl.ctl log=/interface/contabfile/diskspace/conl.log errors=100

三、几种常见的导入场景

1、通过记事本txt导入

控制文件：input.ctl，内容如下： 
　　 load data　　　　　　　　　　 --1、控制文件标识 
　　 infile 'test.txt'　　　　　　 --2、要输入的数据文件名为test.txt 
　　 append into table test　　　　--3、向表test中追加记录 
　　 fields terminated by X'09'　　--4、字段终止于X'09'，是一个制表符（TAB） 
　　 (id,username,password,sj)　　 -----定义列对应顺序 

a、insert，为缺省方式，在数据装载开始时要求表为空 
b、append，在表中追加新记录 
c、replace，删除旧记录，替换成新装载的记录 
d、truncate，同上 

在DOS窗口下使用SQL*Loader命令实现数据的输入 
C:\>sqlldr userid=system/manager control=input.ctl 
默认日志文件名为：input.log 
默认坏记录文件为：input.bad

2、可以把EXCEL文件另存为CSV（逗号分隔）(*.csv),控制文件就改为用逗号分隔

LOAD DATA 
INFILE 'd:\car.csv' 
APPEND INTO TABLE t_car_temp 
FIELDS TERMINATED BY "," 
(phoneno,vip_car)

3、在控制文件中直接导入数据

1、控制文件test.ctl的内容
-- The format for executing this file with SQL Loader is:
-- SQLLDR control= Be sure to substitute your
-- version of SQL LOADER and the filename for this file.
LOAD DATA
INFILE *
BADFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.BAD'
DISCARDFILE 'C:\Documents and Settings\Jackey\桌面\WMCOUNTRY.DSC'
INSERT INTO TABLE EMCCOUNTRY
Fields terminated by ";" Optionally enclosed by '"'
(
COUNTRYID NULLIF (COUNTRYID="NULL"),
COUNTRYCODE,
COUNTRYNAME,
CONTINENTID NULLIF (CONTINENTID="NULL"),
MAPID NULLIF (MAPID="NULL"),
CREATETIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (CREATETIME="NULL"),
LASTMODIFIEDTIME DATE "MM/DD/YYYY HH24:MI:SS" NULLIF (LASTMODIFIEDTIME="NULL")
)
BEGINDATA
1;"JP";"Japan";1;9;"09/16/2004 16:31:32";NULL
2;"CN";"China";1;10;"09/16/2004 16:31:32";NULL
3;"IN";"India";1;11;"09/16/2004 16:31:32";NULL
4;"AU";"Australia";6;12;"09/16/2004 16:31:32";NULL

2、执行导入命令
C:\>sqlldr userid=system/manager control=test.ctl

四、sql loader 帮助

efile92@iomitfdb2:/interface/contabfile/diskspace >sqlldr
SQL*Loader: Release 11.2.0.4.0 - Production on Fri May 31 18:41:56 2019

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

Usage: SQLLDR keyword=value [,keyword=value,...]

Valid Keywords:

    userid -- ORACLE username/password           
   control -- control file name                  
       log -- log file name                      
       bad -- bad file name                      
      data -- data file name                     
   discard -- discard file name                  
discardmax -- number of discards to allow          (Default all)
      skip -- number of logical records to skip    (Default 0)
      load -- number of logical records to load    (Default all)
    errors -- number of errors to allow            (Default 50)
      rows -- number of rows in conventional path bind array or between direct path data saves
               (Default: Conventional path 64, Direct path all)
  bindsize -- size of conventional path bind array in bytes  (Default 256000)
    silent -- suppress messages during run (header,feedback,errors,discards,partitions)
    direct -- use direct path                      (Default FALSE)
   parfile -- parameter file: name of file that contains parameter specifications
  parallel -- do parallel load                     (Default FALSE)
      file -- file to allocate extents from      
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions  (Default FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable  (Default FALSE)
commit_discontinued -- commit loaded rows when load is discontinued  (Default FALSE)
  readsize -- size of read buffer                  (Default 1048576)
external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE  (Default NOT_USED)
columnarrayrows -- number of rows for direct path column array  (Default 5000)
streamsize -- size of direct path stream buffer in bytes  (Default 256000)
multithreading -- use multithreading in direct path  
 resumable -- enable or disable resumable for current session  (Default FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE  (Default 7200)
date_cache -- size (in entries) of date conversion cache  (Default 1000)
no_index_errors -- abort load on any index errors  (Default FALSE)

PLEASE NOTE: Command-line parameters may be specified either by
position or by keywords.  An example of the former case is 'sqlldr
scott/tiger foo'; an example of the latter is 'sqlldr control=foo
userid=scott/tiger'.  One may specify parameters by position before
but not after parameters specified by keywords.  For example,
'sqlldr scott/tiger control=foo logfile=log' is allowed, but
'sqlldr scott/tiger control=foo log' is not, even though the
position of the parameter 'log' is correct.
efile92@iomitfdb2:/interface/contabfile/diskspace >

五、实例

获取磁盘空间信息，按照既定格式入库。

efile92@iomitfdb2:/interface/contabfile/diskspace >cat diskspace.sh
#!bin/bash
sh /interface/contabfile/diskspace/diskspacenas18.sh
sh /interface/contabfile/diskspace/diskspacenas19.sh

cd  /interface/contabfile/diskspace
cat  diskspacenas18.txt>>diskspace.txt
cat  diskspacenas19.txt>>diskspace.txt

keyword=interface
IP=132.78.140.118
exectime=`date "+%Y-%m-%d %H:%M:%S"`
outstr=0;
#IP|时间|磁盘总空间|使用大小|空闲大小|使用率

outstr=`df -g|grep -i $keyword|awk '{print  $2"G","|"($2-$3)"G","|"$3"G","|"$4}'` 
outstr=$IP"|"$keyword"|"$exectime"|"$outstr"|"
echo $outstr >> diskspace.txt

echo `date`执行结束

sqlldr userid=BJIOM/Bjiom_0420@rman31  control=/interface/contabfile/diskspace/conl.ctl log=/interface/contabfile/diskspace/conl.log

exitefile92@iomitfdb2:/interface/contabfile/diskspace >cat conl.ctl 
load data 
infile '/interface/contabfile/diskspace/diskspace.txt' 
append into table t_cipan_test
fields terminated by '|'
(ip,paths,shijian date "yyyy-mm-dd hh24:mi:ss",total,used,free,user_rate,ID RECNUM)
efile92@iomitfdb2:/interface/contabfile/diskspace >

posted @ 2018-05-23 16:42 航松先生阅读(970) 评论(0) 收藏举报

刷新页面返回顶部