ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【听海日志】之Oracle SQL*LOADER

【听海日志】之Oracle SQL*LOADER

原创 Linux操作系统 作者:听海★蓝心梦 时间:2012-03-20 21:02:24 0 删除 编辑

一、SQL*LOADER简介

1SQL*LOADER的基本特点

Ø  能装入不同数据类型文件及多个数据文件的数据。

Ø  可装入固定格式,自由定界以及可度长格式的数据。

Ø  可以装入二进制,压缩十进制数据。

Ø  一次可对多个表装入数据。

Ø  连接多个物理记录装到一个记录中。

Ø  对一单记录分解再装入到表中。

Ø  可以用 数对制定列生成唯一的KEY

Ø  可对磁盘或磁带数据文件装入制表中。

Ø  提供装入错误报告。

Ø  可以将文件中的整型字符串,自动转成压缩十进制并装入列表中。

2、控制文件

控制文件是用一种语言写的文本文件,这个文本文件能被SQL*LOADER识别。SQL*LOADER根据控制文件可以找到需要加载的数据。并且分析和解释这些数据。控制文件由三个部分组成:

Ø  全局选件、行、跳过的记录数等。

Ø  INFILE子句指定的输入数据。

Ø  数据特性说明。

3、输入文件

对于 SQL*Loader, 除控制文件外就是输入数据。SQL*Loader可从一个或多个指定的文件中读出数据。如果数据是在控制文件中指定,就要在控制文件中写成INFILE * 格式。当数据是固定的格式(长度一样)时且是在文件中得到时,要用INFILE "fix n",如:

load data

infile 'example.dat' "fix 11"

into table example

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

(col1 char(5),

col2 char(7))

example.dat:

001, cd, 0002,fghi,

00003,lmn,

1, "pqrs",

0005,uvwx,

当数据是可变格式(长度不一样)时且是在文件中得到时,要用INFILE "var n"。如:

load data

infile 'example.dat' "var 3"

into table example

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

(col1 char(5),

col2 char(7))

example.dat:

009hello,cd,010world,im,

012my,name is,

4、坏文件

坏文件包含那些被SQL*Loader拒绝的记录。被拒绝的记录可能是不符合要求的记录。坏文件的名字由 SQL*Loader命令的BADFILE 参数来给定。

5、日志文件及日志信息

当SQL*Loader 开始执行后,它就自动建立日志文件。日志文件包含有加载的总结,加载中的错误信息等。

二、控制文件语法

1、控制文件的格式如下

OPTIONS { [SKIP=integer] [ LOAD = integer ]

[ERRORS = integer] [ROWS=integer]

[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )

LOAD[DATA]

[ { INFILE | INDDN } {file | * }

[STREAM | RECORD | FIXED length [BLOCKSIZE size]|

VARIABLE [length] ]

[ { BADFILE | BADDN } file ]

{DISCARDS | DISCARDMAX} integr ]

[ {INDDN | INFILE} . . . ]

[ APPEND | REPLACE | INSERT ]

[RECLENT integer]

[ { CONCATENATE integer |

CONTINUEIF { [THIS | NEXT] (start[: end])LAST }

Operator { 'string' | X 'hex' } } ]

INTO TABLE [user.]table

[APPEND | REPLACE|INSERT]

[WHEN condition [AND condition]...]

[FIELDS [delimiter] ]

(

column {

RECNUM | CONSTANT value |

SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |

[POSITION ( { start [end] | * [ + integer] }

) ]

datatype

[TERMINATED [ BY ] {WHITESPACE| [X] 'character' } ]

[ [OPTIONALLY] ENCLOSE[BY] [X]'charcter']

[NULLIF condition ]

[DEFAULTIF condotion]

}

[ ,...]

)

[INTO TABLE...]

[BEGINDATA]

2、要加载的数据文件

Ø  INFILEINDDN是同义词,它们后面都是要加载的数据文件。如果用*则表示数据就在控制文件内。在INFILE后可以跟几个文件。

Ø  STRAM 表示一次读一个字节的数据。新行代表新物理记录(逻辑记录可由几个物理记录组成)。

Ø  RECORD 使用宿主操作系统文件及记录管理系统。如果数据在控制文件中则使用这种方法。

Ø  FIXED length 要读的记录长度为length字节。

Ø  VARIABLE 被读的记录中前两个字节包含的长度,length 记录可能的长度。缺省为8k字节。

Ø  BADFILE和BADDN同义。Oracle 不能加载数据到数据库的那些记录。

Ø  DISCARDFILE和DISCARDDN是同义词。记录没有通过的数据。

Ø  DISCARDS和DISCARDMAX是同义词。Integer 为最大放弃的文件个数。

3、加载的方法

Ø  APPEND 给表添加行。

Ø  INSERT 给空表增加行(如果表中有记录则退出)。

Ø  REPLACE 先清空表在加载数据。

Ø  RECLEN 用于两种情况:

Ø  SQLLDR不能自动计算记录长度。

Ø  用户想看坏文件的完整记录时。对于后一种,Oracle只能按常规把坏记录部分写到错误的地方。如果看整条记录,则可以将整条记录写到坏文件中。

Ø  指定最大的记录长度:

CONCATENATE 允许用户设定一个整数,表示要组合逻辑记录的数目。

4、建立逻辑记录

Ø  THIS检查当前记录条件,如果为真则连接下一个记录。

Ø  NEXT检查下一个记录条件。如果为真,则连接下一个记录到当前记录来。

Ø  Start: end表示要检查在THISNEXT字串是否存在继续串的列,以确定是否进行连接。如:continueif next(1-3)='WAG' continueif next(1-3)=X'0d03if'

5、指定要加载的表

INTO TABLE 要加的表名。WHEN select WHERE类似。用来检查记录的情况,如:when(3-5)='SSM' and (22)='*"

6、介绍并括起记录中的字段

FIELDS给出记录中字段的分隔符,FIELDS格式为:

FIELDS [TERMIALED [BY] {WHITESPACE | [X] 'charcter'} ][ [ OPTIONALLY] ENCLOSE [BY] [X]'charcter' ]

TERMINATED 读完前一个字段即开始读下一个字段直到介绍。WHITESPACE 是指结束符是空格的意思。包括空格、Tab、换行符、换页符及回车符。如果是要判断但字符,可以用单引号括起,如X'1B'等。OPTIONALLY ENCLOSED 表示数据应由特殊字符括起来。也可以括在TERMINATED字符内。使用OPTIONALLY要同时用TERMINLATEDENCLOSED 指两个分界符内的数据。如果同时用 ENCLOSEDTERMINAED ,则它们的顺序决定计算的顺序。

7、定义列

Ø  column 是表列名。列的取值可以是:

BECHUM 表示逻辑记录数。第一个记录为1,2个记录为2

CONSTANT 表示赋予常数。

SEQUENCE 表示序列可以从任意序号开始,格式为:

SEQUENCE { integer | MAX |COUNT} [,increment]

Ø  POSITION 给出列在逻辑记录中的位置。可以是绝对的,或相对前一列的值。格式为:

POSITION ( {start[end] | * [+integer] } )

Ø  Start 开始位置

* 表示前字段之后立刻开始。

+ 从前列开始向后条的位置数。

8、定义数据类型

可以定义14种数据类型:

CHAR

DATE

DECIMAL EXTERNAL

DECIMAL

DOUBLE

FLOAT

FLOAT EXTERNAL

GRAPHIC EXTERNAL

INTEGER

INTEGER EXTERNAL

SMALLINT

VARCHAR

VARGRAPHIC

 

1.字符类型数据

CHAR[ (length)] [delimiter] length缺省为 1.

2.日期类型数据

DATE [ ( length)]['date_format' [delimiter],使用to_date函数来限制。

3.字符格式中的十进制

DECIMAL EXTERNAL [(length)] [delimiter]

用于常规格式的十进制数(不是二进制=> 一个位等于一个bit)。

4.压缩十进制格式数据

DECIMAL digtial [,precision]

5.双精度符点二进制

DOUBLE

6.普通符点二进制

FLOAT

7.字符格式符点数

FLOAT EXTERNAL [ (length) ] [delimiter]

8.双字节字符串数据

GRAPHIC [ (legth)]

9.双字节字符串数据

GRAPHIC EXTERNAL[ (legth)]

10.常规全字二进制整数

INTEGER

11.字符格式整数

INTEGER EXTERNAL

12.常规全字二进制数据

SMALLINT

13.可变长度字符串

VARCHAR

14.可变双字节字符串数据

VARGRAPHIC

三、写控制文件CTL

数据文件的内容可以在OS下的一个文件或跟在控制文件下的具体数据。数据文件可以是:

1、二进制与字符格式:LOADER可以把二进制文件读(当成字符读)列表中

2、固定格式:记录中的数据、数据类型、 数据长度固定。

3、可变格式:每个记录至少有一个可变长数据字段,一个记录可以是一个连续的字符串。数据段的分界(如姓名、年龄)如用“,”作字段的 ;用,"’作数据括号等

4 LOADER可以使用多个连续字段的物理记录组成一个逻辑记录,记录文件运行情况文件包括以下内容:

1.) 运行日期:软件版本号

2.) 全部输入,输出文件名;对命令行的展示信息,补充信息

3.) 对每个装入信息报告:如表名,装入情况;对初始装入,加截入或更新装入的选择情况,栏信息

4.) 数据错误报告:错误码;放弃记录报告

5.) 每个装X报告:装入行;装入行数,可能跳过行数;可能拒绝行数;可能放弃行数等

6.) 统计概要:使用空间(包大小,长度);读入记录数,装入记录数,跳过记录数;拒绝记录数,放弃记录数;运行时间等。

四、sql load的一点小总结

1SQL*LOAD执行命令

sqlldr userid=lgone/tiger control=a.ctl

LOAD DATA

INFILE 't.dat'     --- 要导入的文件.

INFILE 'tt.date'     --- 导入多个文件

INFILE *     ---  要导入的内容就在control文件里下面的BEGINDATA后面就是导入的内容

INTO TABLE table_name      --- 指定装入的表

BADFILE 'c:bad.txt'      ---指定坏文件地址

24种装入表的方式

APPEND       ---原先的表有数据 就加在后面

INSERT        ---装载空表 如果原先的表有数据 sqlloader会停止 默认值

REPLACE      ---原先的表有数据 原先的数据会全部删除

TRUNCATE     ---指定的内容和replace的相同 会用truncate语句删除现存数据

指定的TERMINATED可以在表的开头 也可在表的内部字段部分

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

装载这种数据: 10,lg,"""lg""","lg,lg"

在表中结果: 10 lg "lg" lg,lg

TERMINATED BY X '09'            --- 以十六进制格式 '09' 表示的

TERMINATED BY WRITESPACE    --- 装载这种数据: 10 lg lg

TRAILING NULLCOLS             --- 表的字段没有对应的值时允许为空

3表的字段

(

col_1 , col_2 ,col_filler FILLER       --- FILLER关键字此列的数值不会被装载: lg,lg,not 结果 lg lg

)

当没声明FIELDS TERMINATED BY ','

(

col_1 [interger external] TERMINATED BY ',' ,

col_2 [date "dd-mon-yyy"] TERMINATED BY ',' ,

col_3 [char] TERMINATED BY ',' OPTIONALLY ENCLOSED BY 'lg'

)

当没声明FIELDS TERMINATED BY ','用位置告诉字段装载数据

(

col_1 position(1:2),

col_2 position(3:10),

col_3 position(*:16),            --- 这个字段的开始位置在前一字段的结束位置

col_4 position(1:16),

col_5 position(3:10) char(8)      --- 指定字段的类型

)

BEGINDATA         --- 对应开始的INFILE * 要导入的内容就在control文件里

10,Sql,what

20,lg,show

注意begindata后的数值前面不能有空格

4普通装载

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

(DEPTNO,

DNAME,

LOC

)

BEGINDATA

10,Sales,"""USA"""

20,Accounting,"Virginia,USA"

30,Consulting,Virginia

40,Finance,Virginia

50,"Finance","",Virginia     --- loc列将为空

60,"Finance",,Virginia       --- loc列将为空

5Terminated加载

FIELDS TERMINATED BY WHITESPACE FIELDS TERMINATED BY x'09' 的情况

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY WHITESPACE

-- FIELDS TERMINATED BY x'09'

(DEPTNO,

DNAME,

LOC

)

BEGINDATA

10 Sales Virginia

6指定不装载那一列

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

( DEPTNO,

FILLER_1 FILLER,     ---下面的 "Something Not To Be Loaded" 将不会被装载

DNAME,

LOC

)

BEGINDATA

20,Something Not To Be Loaded,Accounting,"Virginia,USA"

7position的列子

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

( DEPTNO position(1:2),

DNAME position(*:16),        --- 这个字段的开始位置在前一字段的结束位置

LOC position(*:29),

ENTIRE_LINE position(1:29)

)

BEGINDATA

10Accounting Virginia,USA

8TRAILING NULLCOLS的使用

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY ','

TRAILING NULLCOLS      --- 其实下面的ENTIRE_LINEBEGINDATA后面的数据中是没有直接对应的列的值的如果第一行改为 10,Sales,Virginia,1/5/2000,, 就不用TRAILING NULLCOLS

(DEPTNO,

DNAME "upper(:dname)",     --- 使用函数

LOC "upper(:loc)",

LAST_UPDATED date 'dd/mm/yyyy',

ENTIRE_LINE ":deptno||:dname||:loc||:last_updated"

)

BEGINDATA

10,Sales,Virginia,1/5/2000

20,Accounting,Virginia,21/6/1999

30,Consulting,Virginia,5/1/2000

40,Finance,Virginia,15/3/2001

9使用自定义的函数

create or replace

function my_to_date( p_string in varchar2 ) return date

as

type fmtArray is table of varchar2(25);

l_fmts fmtArray := fmtArray( 'dd-mon-yyyy', 'dd-month-yyyy',

'dd/mm/yyyy',

'dd/mm/yyyy hh24:mi:ss' );

l_return date;

begin

for i in 1 .. l_fmts.count

loop

begin

l_return := to_date( p_string, l_fmts(i) );

exception

when others then null;

end;

EXIT when l_return is not null;

end loop;

if ( l_return is null )

then

l_return :=

new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *

p_string, 'GMT', 'EST' );

end if;

return l_return;

end;

/

LOAD DATA

INFILE *

INTO TABLE DEPT

REPLACE

FIELDS TERMINATED BY ','

TRAILING NULLCOLS

(DEPTNO,

DNAME "upper(:dname)",

LOC "upper(:loc)",

LAST_UPDATED "my_to_date( :last_updated )"    --- 使用自定义的函数

)

BEGINDATA

10,Sales,Virginia,01-april-2001

20,Accounting,Virginia,13/04/2001

30,Consulting,Virginia,14/04/2001 12:02:02

40,Finance,Virginia,987268297

50,Finance,Virginia,02-apr-2001

60,Finance,Virginia,Not a date

10合并多行记录为一行记录

LOAD DATA

INFILE *

concatenate 3        --- 通过关键字concatenate 把几行的记录看成一行记录

INTO TABLE DEPT

replace

FIELDS TERMINATED BY ','

(DEPTNO,

DNAME "upper(:dname)",

LOC "upper(:loc)",

LAST_UPDATED date 'dd/mm/yyyy'

)

BEGINDATA

10,Sales,     --- 其实这3行看成一行10,Sales,Virginia,1/5/2000

Virginia,

1/5/2000

这列子用 continueif list="," 也可以告诉sqlldr在每行的末尾找逗号找到逗号就把下一行附加到上一行

LOAD DATA

INFILE *

continueif this(1:1) = '-'      --- 找每行的开始是否有连接字符 - 有就把下一行连接为一行

-10,Sales,Virginia,

1/5/2000 就是一行 10,Sales,Virginia,1/5/2000其中1:1 表示从第一行开始并在第一行结束还有continueif next continueif list最理想

INTO TABLE DEPT

replace

FIELDS TERMINATED BY ','

(DEPTNO,

DNAME "upper(:dname)",

LOC "upper(:loc)",

LAST_UPDATED date 'dd/mm/yyyy'

)

BEGINDATA // 但是好象不能象右面的那样使用

-10,Sales,Virginia, -10,Sales,Virginia,

1/5/2000 1/5/2000

-40, 40,Finance,Virginia,13/04/2001

Finance,Virginia,13/04/2001

11载入每行的行号

load data

infile *

into table t

replace

( seqno RECNUM         --- 载入每行的行号

text Position(1:1024))

BEGINDATA

fsdfasj       --- 自动分配一行号给载入表tseqno字段此行为1

fasdjfasdfl    --- 此行为 2 ...

五、实例

SQL*LOADERORACLE的数据加载工具,通常用来将操作系统文件迁移到ORACLE数据库中。SQL*LOADER是大型数据仓库选择使用的加载方法,因为它提供了最快速的途径(DIRECTPARALLEL)。现在,我们抛开其理论不谈,用实例来使您快速掌握SQL*LOADER的使用方法。

SQL*LOADER只能导入纯文本,所以我们现在开始以实例来讲解其用法。

1整理数据源

假设表已经存在:

test@ORCL> create table t_sqlload(

resultid varchar2(500),

website varchar2(500),

ipport varchar2(500),

status varchar2(500))

/

数据源result.txt,欲倒入ORACLEtest用户下。result.txt内容:

1,默认 Web 站点,192.168.2.254:80:,RUNNING

2,other,192.168.2.254:80:test.com,STOPPED

3,third,192.168.2.254:81:thirdabc.com,RUNNING

从中,我们看出4列,分别以逗号分隔,为变长字符串。

2、制定控制文件

控制文件result.ctl内容:

load data

infile 'result.txt'

into table t_sqlload

(resultid char terminated by ',',

website char terminated by ',',

ipport char terminated by ',',

status char terminated by whitespace)

说明:

Infile指数据源文件,这里我们省略了默认的 discardfile result.dsc  badfile  result.bad

into table resultxt默认是INSERT,也可以into table resultxt APPEND为追加方式,或REPLACE

terminated by ','指用逗号分隔

terminated by whitespace结尾以空白分隔

注:infileresult.ctl一般就放在sqlldr的当前目录

3、此时我们执行加载:

[oracle@localhost scripts]$ sqlldr userid=test/admin control=result.ctl log=result.out

SQL*Loader: Release 11.2.0.1.0 - Production on чǚһ 3Ղ 19 16:10:58 2012

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

Commit point reached - logical record count 3

已经成功!我们可以通过日志文件来分析其过程:resulthis.out内容如下:

[oracle@localhost scripts]$ more result.out

SQL*Loader: Release 11.2.0.1.0 - Production on чǚһ 3Ղ 19 16:10:58 2012

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

 

Control File:   result.ctl

Data File:      result.txt

  Bad File:     result.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 T_SQLLOAD, loaded from every logical record.

Insert option in effect for this table: INSERT

 

   Column Name                  Position   Len  Term Encl Datatype

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

RESULTID                            FIRST     *   ,       CHARACTER            

WEBSITE                              NEXT     *   ,       CHARACTER           

IPPORT                               NEXT     *   ,       CHARACTER           

STATUS                               NEXT     *  WHT      CHARACTER           

 

Table T_SQLLOAD:

  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:                  66048 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 чǚһ 3Ղ  19 16:10:58 2012

Run ended on чǚһ 3Ղ  19 16:11:01 2012

 

Elapsed time was:     00:00:02.78

CPU time was:         00:00:00.01

4并发操作

sqlldr userid=/ control=result1.ctl direct=true parallel=true

sqlldr userid=/ control=result2.ctl direct=true parallel=true

sqlldr userid=/ control=result2.ctl direct=true parallel=true

当加载大量数据时,最好使用nologging抑制日志的产生:

SQL>ALTER TABLE RESULTXT nologging;

这样不产生REDO LOG,可以提高效率。然后在CONTROL文件中load data上面加一行:unrecoverable此选项必须要与DIRECT共同应用。

在并发操作时,ORACLE声称可以达到每小时处理100GB数据的能力!其实,估计能到110G就算不错了,开始可用结构相同的文件,但只有少量数据,成功后开始加载大量数据,这样可以避免时间的浪费。

5有关SQLLDR的问题

控制文件input.ctl,内容如下:

load data          --1、控制文件标识

infile 'test.txt'         --2、要输入的数据文件名为test.txt

append into table test    --3、向表test中追加记录

fields terminated by X'09'  --4、字段终止于X'09',是一个制表符(TAB

(id,username,password,sj)  --5定义列对应顺序

其中append为数据装载方式,还有其他选项:

ainsert,为缺省方式,在数据装载开始时要求表为空

bappend,在表中追加新记录

creplace,删除旧记录,替换成新装载的记录

dtruncate,同上

6Excel数据导Oracle

EXCEL文件另存为CSV(逗号分隔)(*.csv)控制文件就改为用逗号分隔

LOAD DATA

INFILE 'result.csv'

APPEND INTO TABLE t

FIELDS TERMINATED BY ","

(no_id, username, ph_number)

Csv文件内容:

[oracle@localhost scripts]$ more result.ctl

load data

infile 'result.csv'

into table t

(

no_id char terminated by ',',

username char terminated by ',',

ph_number char terminated by ',')

执行sqlldr开始导入:

[oracle@localhost scripts]$ sqlldr userid=test/admin control=result.ctl log=result2.out

SQL*Loader: Release 11.2.0.1.0 - Production on чǚһ 3Ղ 19 17:22:39 2012

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

Commit point reached - logical record count 14

数据验证:

test@ORCL> select * from t;

NO_ID             USERNAME            PH_NUMBER

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

10001               测试01              13562485246

10002               zjaxi02                13562485247

10003               zjaxi03                13562485248

10004               zjaxi04                13562485249

10005               zjaxi05                13562485250

10006               zjaxi06                13562485251

10007               zjaxi07                13562485252

10008               zjaxi08                13562485253

10009               zjaxi09                13562485254

10010               zjaxi10                13562485255

10011               zjaxi11                13562485256

10012               zjaxi12                13562485257

10013               zjaxi13                13562485258

10014               zjaxi14                13562485259

 

14 rows selected.

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

请登录后发表评论 登录
全部评论

注册时间:2009-02-18

  • 博文量
    256
  • 访问量
    1199419