SQLLOAD加载数据常用功能

最新推荐文章于 2023-12-08 11:25:58 发布

cuiwangxie1183

最新推荐文章于 2023-12-08 11:25:58 发布

阅读量756

点赞数

文章标签：数据库操作系统

SQLLOAD加载数据常用功能

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

一 SQLLOAD介绍

SQL*LOADER是ORACLE的数据加载工具，通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法，因为它提供了最快速的途径（DIRECT，PARALLEL）。对应大量格式化的平文件数据可以快速导入到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