ITPub博客

首页 > 数据库 > Oracle > SQLLOAD加载数据常用功能

SQLLOAD加载数据常用功能

原创 Oracle 作者:bestpaydata 时间:2015-12-24 09:01:51 0 删除 编辑


SQLLOAD加载数据常用功能

 --性能优化和故障处理小组

SQLLOAD介绍

SQL*LOADERORACLE的数据加载工具,通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法,因为它提供了最快速的途径(DIRECTPARALLEL)。对应大量格式化的平文件数据可以快速导入到Oracle


二 常用参数

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 be      tween direct path data saves

(Default: Conventional path 64, Direct path all)                 

bindsize -- size of conventional path bind array in bytes  (Defa  ult 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 104857  6)

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  (De fault 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)

 

 

control文件格式

load data --1、控制文件标识

infile 'test.txt' --2、要输入的数据文件名为test.txt

append into table test --3、向表test中追加记录

fields terminated by '|' --4、字段终止于'|'(自定义)

OPTIONALLY ENCLOSED BY '\''--5、字段用’’括住(自定义)

(id,username,password,sj) ---6、定义列对应顺序

 

a、insert,为缺省方式,在数据装载开始时要求表为空

b、append,在表中追加新记录

c、replace,删除旧记录,替换成新装载的记录

d、truncate,同上

 

四 常用功能

数据源:

[oracle@rhel66 ~]$ cat b.data
20151206093347100217|486002089|2015-12-06 09:34:39|1.5|success
20151206103628100218|486002090|2015-12-06 10:36:39|3.0|failure
20151206110015100219|486002091|2015-12-06 11:03:08|2.0|failure

表结构:

create table T_B_TXXCON_XX
(
  EEEEE_ID         VARCHAR2(32) ,
  RETAIL_FFFFF_ID  VARCHAR2(32),
  TRANSACTION_TIME DATE,
  PRICE            NUMBER(16),
  FFFFF_RESULT     VARCHAR2(8),
  CREATE_DATE      DATE,
  CURTYPE          CHAR(3) default 156,
  BPBP_FFFFF_NO    VARCHAR2(32)
)

1、以‘|’ 为字段分割  字段可能含有‘’

create table   t(id number(22),name varchar2(22),dat date);

[oracle@yzf sqlldr]$ cat t.data

2|'486002089'|2015-12-06 09:34:39

1|111111111|2015-11-11 11:34:39

[oracle@yzf sqlldr]$ cat t.ctl

LOAD DATA

    INFILE "t.data"

    APPEND

    INTO TABLE T

    Fields terminated by "|"      trailing nullcols

(id ,name ,dat DATE "YYYY-MM-DD HH24:MI:SS")

[oracle@yzf sqlldr]$  sqlldr dsg/dsg  control=t.ctl


2、取当前日期入库,比如sysdate

[oracle@rhel66 ~]$ cat cs2.ctl
LOAD DATA
    INFILE "/home/oracle/b.data"
    APPEND
    INTO TABLE T_B_TXXCON_XX
    Fields terminated by "|"
    trailing nullcols
(bpbp_FFFFF_no,retail_FFFFF_id,transaction_time DATE "YYYY-MM-DD HH24:MI:SS",price,FFFFF_result,EEEEE_ID "SEQ_BPBP_TRECON_FFFFF.NEXTVAL",create_date sysdate)

3、取数据库中的序列入库:seqence.nextval

先把文件中对应的列一一排列入库,然后再写文件中没有的列

比如先写

文件中的行:

20151206093347100217|486002089|2015-12-06 09:34:39|1.5|success

数据库中对应的字段:

bpbp_FFFFF_no,retail_FFFFF_id,transaction_time DATE "YYYY-MM-DD HH24:MI:SS",price,FFFFF_result

一一对应如下:

bpbp_FFFFF_no      20151206093347100217

retail_FFFFF_id       86002089

transaction_time DATE "YYYY-MM-DD HH24:MI:SS"  2015-12-06 09:34:39

price  1.5

FFFFF_result  success

 

[oracle@rhel66 ~]$ cat cs2.ctl
LOAD DATA
    INFILE "/home/oracle/b.data"
    APPEND
    INTO TABLE T_B_TXXCON_XX
    Fields terminated by "|"
    trailing nullcols
(bpbp_FFFFF_no,retail_FFFFF_id,transaction_time DATE "YYYY-MM-DD HH24:MI:SS",price,FFFFF_result,EEEEE_ID "SEQ_BPBP_TRECON_FFFFF.NEXTVAL",create_date sysdate)

4、截取某一列的作为另一列入库,主要是从主键的前八位作为交易日期

第一种方案(建议采用此种方案):

vi cs2.ctl :

LOAD DATA

    INFILE "/home/oracle/b.data"

    APPEND

INTO TABLE T_B_TXXCON_XX

Fields terminated by "|"

    trailing nullcols

(bpbp_FFFFF_no,retail_FFFFF_id,transaction_time DATE "YYYY-MM-DD HH24:MI:SS",price ":price*100",FFFFF_result,EEEEE_ID "SEQ_BPBP_TRECON_FFFFF.NEXTVAL",create_date "to_date(substr(:bpbp_FFFFF_no,1,8),'YYYY-MM-DD HH24:MI:SS')")

数据加载
[oracle@rhel66 ~]$ sqlldr xxxxx/1234 control=cs2.ctl
SQL*Loader: Release 11.2.0.3.0 - Production on Wed Dec 23 09:18:15 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Commit point reached - logical record count 18


第二种方案(不采用,只是说明方法)

create or replace  function  g_fun (i number)
  return number as ii number;
begin
  ii:=i*100;
  return ii;
  end;

g_fun(:bpbp_FFFFF_no)

 

5、列的计算,比如价格从元转换为分后再入库:price*100后再入库

price ":price*100" 自动转换为nuber

vi cs2.ctl :

LOAD DATA

    INFILE "/home/oracle/b.data"

    APPEND

    INTO TABLE T_BPBP_TRECON_FFFFF

    Fields terminated by "|"

    trailing nullcols

(bpbp_FFFFF_no,retail_FFFFF_id,transaction_time DATE "YYYY-MM-DD HH24:MI:SS",price ":price*100",FFFFF_result,EEEEE_ID "SEQ_BPBP_TRECON_FFFFF.NEXTVAL",create_date "to_date(substr(:bpbp_FFFFF_no,1,8),'YYYY-MM-DD HH24:MI:SS')")

数据加载
[oracle@rhel66 ~]$ sqlldr xxxxx/1234 control=cs2.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Wed Dec 23 09:18:15 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 18


6、其他函数

其他函数用法一样,比如trim

PROCESS_RATE             "(TRIM(:PROCESS_RATE          ))"

--gt

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30109892/viewspace-1935669/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2015-01-19

  • 博文量
    126
  • 访问量
    985806