oracle数据库sqlloader,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"，共同学习，共同成长！！！