oracle12C有sqlloader吗,==[ADM]==12c + SQL*Loader Express Mode

Oracle Database 12C中的SQL*Loader 新增加了Express Mode，借助这个特性，可以在最小化配置的情况下加载数据(比如无需要创建Control file)。下面通过一个简单示例快速感受下：

◆ 创建测试表

SYS% cdb1> conn study/study@pdb1

Connected.

STUDY% pdb1> create table test

( region      char(3),

region_name varchar2(12),

bill_month  number(6),

fee         number(10,2)

);

Table created.

STUDY% pdb1>

◆ 准备测试数据

STUDY% pdb1> host cat test.dat

530,HZ,200501,100.01

530,HZ,200502,800.23

531,JN,200501,5000.81

531,JN,200502,5360.00

532,QD,200501,20670.32

532,QD,200502,22000.08

533,ZB,200501,3050.56

533,ZB,200502,3108.14

STUDY% pdb1>

◆用SQL*Loader Express Mode快速加载数据

STUDY% pdb1> host sqlldr study/study@pdb1 TABLE=test --是不是很简单

SQL*Loader: Release 12.1.0.1.0 - Production on Sun Jun 30 14:05:36 2013

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

Express Mode Load, Table: TEST

Path used: External Table, DEGREE_OF_PARALLELISM=AUTO

Table TEST:

8 Rows successfully loaded.

Check the log files:

test.log

test_%p.log_xt

for more information about the load.

◆数据加载完毕,查看数据

STUDY% pdb1> select * from test;

REG REGION_NAME    BILL_MONTH   FEE

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

530 HZ             200501       100.01

530 HZ             200502       800.23

531 JN             200501       5000.81

531 JN             200502       5360

532 QD             200501       20670.32

532 QD             200502       22000.08

533 ZB             200501       3050.56

533 ZB             200502       3108.14

8 rows selected.

STUDY% pdb1>

◆工作原理可以从日志文件中看到

STUDY% pdb1> host cat test.log

SQL*Loader: Release 12.1.0.1.0 - Production on Sun Jun 30 14:05:36 2013

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

Express Mode Load, Table: TEST

Data File:      test.dat

Bad File:     test_%p.bad

Discard File:  none specified

(Allow all discards)

Number to load: ALL

Number to skip: 0

Errors allowed: 50

Continuation:    none specified

Path used:      External Table

Table TEST, loaded from every logical record.

Insert option in effect for this table: APPEND

Column Name                  Position   Len  Term Encl Datatype

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

REGION                              FIRST     *   ,       CHARACTER

REGION_NAME                          NEXT     *   ,       CHARACTER

BILL_MONTH                           NEXT     *   ,       CHARACTER

FEE                                  NEXT     *   ,       CHARACTER

Generated control file for possible reuse:

OPTIONS(EXTERNAL_TABLE=EXECUTE, TRIM=LRTRIM)

LOAD DATA

INFILE 'test'

APPEND

INTO TABLE TEST

FIELDS TERMINATED BY ","

(

REGION,

REGION_NAME,

BILL_MONTH,

FEE

)

End of generated control file for possible reuse.

created temporary directory object SYS_SQLLDR_XT_TMPDIR_00000 for path /home/oracle

enable parallel DML: ALTER SESSION ENABLE PARALLEL DML

creating external table "SYS_SQLLDR_X_EXT_TEST"

CREATE TABLE "SYS_SQLLDR_X_EXT_TEST"

(

"REGION" CHAR(3),

"REGION_NAME" VARCHAR2(12),

"BILL_MONTH" NUMBER(6),

"FEE" NUMBER(10,2)

)

ORGANIZATION external

(

TYPE oracle_loader

DEFAULT DIRECTORY SYS_SQLLDR_XT_TMPDIR_00000

ACCESS PARAMETERS

(

RECORDS DELIMITED BY NEWLINE CHARACTERSET US7ASCII

BADFILE 'SYS_SQLLDR_XT_TMPDIR_00000':'test_%p.bad'

LOGFILE 'test_%p.log_xt'

READSIZE 1048576

FIELDS TERMINATED BY "," LRTRIM

REJECT ROWS WITH ALL NULL FIELDS

(

"REGION" CHAR(255),

"REGION_NAME" CHAR(255),

"BILL_MONTH" CHAR(255),

"FEE" CHAR(255)

)

)

location

(

'test.dat'

)

)REJECT LIMIT UNLIMITED

executing INSERT statement to load database table TEST

INSERT /*+ append parallel(auto) */ INTO TEST

(

REGION,

REGION_NAME,

BILL_MONTH,

FEE

)

SELECT

"REGION",

"REGION_NAME",

"BILL_MONTH",

"FEE"

FROM "SYS_SQLLDR_X_EXT_TEST"

dropping external table "SYS_SQLLDR_X_EXT_TEST"

Table TEST:

8 Rows successfully loaded.

Run began on Sun Jun 30 14:05:36 2013

Run ended on Sun Jun 30 14:05:43 2013

Elapsed time was:     00:00:06.61

CPU time was:         00:00:00.05

STUDY% pdb1>

◆说明

(1)这里有个需要注意的地方，上面的命令行中的表名大小写一定要和操作系统上对应的文件名大小写一样，比如TABLE=TEST 那么对应的存放数据文件要是TEST.dat,如果TABLE=test,那么对应的存放数据文件要是test.dat  (文件扩展名必须是.dat)

(2)数据文件必须是逗号分隔的SQL*Loader和external tables支持的格式。

(3)更多关于SQL*Loader Express Mode的介绍，参见官方手册。

PS:

导入含date字段的表，出现ORA-01861: literal does not match format string错误的处理

编写s.ctl控制文件，控制导入，文件内容如下：

load data

infile 's.txt'                               --指定外部数据文件

append into table s.recovery_progress        --操作类型及表

fields terminated by ","                     --分隔符

Optionally enclosed by '"'                   --数据中每个字段用'"'隔起时

trailing nullcols                            --表的字段没有对应的值时允许为空

(START_TIME DATE 'yyyy-mm-dd hh24:mi:ss',

TYPE,

ITEM,

UNITS,

SOFAR,

TOTAL,

TIMESTAMP DATE 'yyyy-mm-dd hh24:mi:ss')

$ sqlldr s/s@pdb control=s.ctl                --需手工编辑s.txt文件格式，多余空格会原样插入表字段