Oracle SQL Loader(sqlldr)

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","000105411337400"

"2","8880191004003037777","5000.00","000041122657300"

"3","8880191000006237777","54.10","000181100541100"

。。。。。。

"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 666418110444374

         2 8880191004003038888       5000 111104157666573

         3 8880191000006232222       54.1 444418054444374

         4 8880191000002611111       6.99 666777531555072

         5 8880191004002573333       1000 555104157226588

 

SQL> select count(*) from ppan;

 

  COUNT(*)

----------

    200000

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

转载于:http://blog.itpub.net/29785807/viewspace-1757667/