Oracle SQL Loader(sqlldr)

SQL*LOADER 是 ORACLE 的数据加载工具，通常用来将操作系统文件迁移到 ORACLE 数据库中。 SQL*LOADER 是大型数据仓库选择使用的加载方法，因为它提供了最快速的途径（ DIRECT ， PARALLEL ）。

使用方法：

一 加载 txt 文件

1 创建数据文件

[oracle@ogg1 ~]$ vim cc.txt

1,2,3

4,5,6

7,8,9

2 创建控制文件

[oracle@ogg1 ~]$ touch ccc.ctl

[oracle@ogg1 ~]$ vim ccc.ctl

load data

infile '/home/oracle/cc.txt'

append

into table c_chen

fields terminated by ','

(col1,col2,col3)

~

3 创建表

SQL> create table c_chen(col1 number,col2 number,col3 number);

Table created.

SQL> select * from c_chen;

no rows selected

4 执行加载

[oracle@ogg1 ~]$ sqlldr chen/chen control=ccc.ctl

SQL*Loader: Release 11.2.0.3.0 - Production on Thu Jul 30 17:27:08 2015

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

Commit point reached - logical record count 3

5 查看加载数据

[oracle@ogg1 ~]$ sqlplus chen/chen

SQL> select * from c_chen;

      COL1       COL2       COL3

---------- ---------- ----------

         1          2          3

         4          5          6

         7          8          9

6 查看加载日志

[oracle@ogg1 ~]$ vim ccc.log

SQL*Loader: Release 11.2.0.3.0 - Production on Thu Jul 30 17:27:08 2015

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

Control File:   ccc.ctl

Data File:      /home/oracle/cc.txt

  Bad File:     cc.bad

  Discard File:  none specified

 (Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Bind array:     64 rows, maximum of 256000 bytes

Continuation:    none specified

Path used:      Conventional

Table C_CHEN, loaded from every logical record.

Insert option in effect for this table: APPEND

   Column Name                  Position   Len  Term Encl Datatype

------------------------------ ---------- ----- ---- ---- ---------------------

COL1                                FIRST     *   ,       CHARACTER

COL2                                 NEXT     *   ,       CHARACTER

COL3                                 NEXT     *   ,       CHARACTER

Table C_CHEN:

  3 Rows successfully loaded.

  0 Rows not loaded due to data errors.

  0 Rows not loaded because all WHEN clauses were failed.

  0 Rows not loaded because all fields were null.

Space allocated for bind array:                  49536 bytes(64 rows)

Read   buffer bytes: 1048576

Total logical records skipped:          0

Total logical records read:             3

Total logical records rejected:         0

Total logical records discarded:        0

Run began on Thu Jul 30 17:27:08 2015

Run ended on Thu Jul 30 17:27:08 2015

Elapsed time was:     00:00:00.24

CPU time was:         00:00:00.00

二 加载 CSV 文件

1 导入 CSV 文件

/*CSV文件默认由","分割*/

Cat testaa.csv'

"1","8880191000006238888","187.50","0001 05411337400"

"2","8880191004003037777","5000.00","0000411 22657300 "

"3","8880191000006237777","54.10","000 181100541100"

。。。。。。

"200000","8880191000005735555","500.00",""

2 控制文件

[oracle@ogg1 ~]$ vim bbb.ctl

load data

infile '/home/oracle/testaa.csv'

append into table ppan

fields terminated by "," optionally enclosed by'"'

(id,pan,txn_amt,mid)

3 创建表

SQL> create table ppan(id number,pana varchar2(19),txn_amta number(12,2),mida varchar2(15));

4 加载数据

[oracle@ogg1 ~]$ sqlldr chen/chen control=bbb.ctl

5 查看数据

SQL> select * from ppan where rownum<=5;

        ID PANa                    TXN_AMTa MIDa

---------- ------------------- ---------- ---------------

         1 8880191000006236666       187.5 666 418110444374

         2 8880191004003038888        5000 111104 157666573

         3 8880191000006232222        54.1 444 418054444374

         4 8880191000002611111        6.99 666 777531555 072

         5 8880191004002573333        1000 555104157226588

SQL> select count(*) from ppan;

  COUNT(*)

----------

    200000

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！