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","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"，共同學習，共同成長！！！