oracle中loder,Oracle Sql Loader的学习使用

最近由于遇到oracle控制文件的使用，虽然不是很复杂，但是从来没有用过，专门花点时间看看。点击

1，概述：

Sql Loader: 一个批量工具，将文件数据导入到数据库。可以导入一个表或者多个表，甚至可以在导入时修改数据。

2，使用

a,你电脑需要装Oracle,不然你是找不到Sqlldr 这个命令的。

在控制输入台输入 sqlldr：

会列出相关的参数介绍。

>sqlldr

.

.

.

Usage: SQLLDR keyword=value [,keyword=value,...]Valid Keywords:

userid--ORACLE username/password

control --control file name

log --log file name

bad --bad file name

data --data file name

discard --discard file name

discardmax --number of discards to allow (Default all)

skip --number of logical records to skip (Default 0)

load --number of logical records to load (Default all)

errors --number of errors to allow (Default 50)

rows --number of rows in conventional path bind array or between direct

path data saves

(Default: Conventional path 64, Direct path all)

bindsize--size of conventional path bind array in bytes (Default 256000)

silent --suppress messages during run (header,feedback,errors,discards,

partitions)

direct--use direct path (Default FALSE)

parfile --parameter file: name of file that contains parameter specifications

parallel --do parallel load (Default FALSE)

file --file to allocate extents from

skip_unusable_indexes --disallow/allow unusable indexes or index partitions

(DefaultFALSE)

skip_index_maintenance--do not maintain indexes, mark affected indexes as

unusable (DefaultFALSE)

commit_discontinued--commit loaded rows when load is discontinued (Default

FALSE)

readsize--size of read buffer (Default 1048576)

external_table --use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE

(DefaultNOT_USED)

columnarrayrows--number of rows for direct path column array (Default 5000)

streamsize --size of direct path stream buffer in bytes (Default 256000)

multithreading --use multithreading in direct path

resumable --enable or disable resumable for current session (Default FALSE)

resumable_name --text string to help identify resumable statement

resumable_timeout --wait time (in seconds) for RESUMABLE (Default 7200)

date_cache --size (in entries) of date conversion cache (Default 1000)

PLEASE NOTE: Command-line parameters may be specified either by position or bykeywords.

An exampleof the former case is ‘sqlldr scott/tiger foo‘; an example ofthe latteris ‘sqlldr control=foo userid=scott/tiger‘.One may specify parameters byposition before

butnot after parameters specified by keywords.For example, ‘sqlldr scott/tiger control=foo

logfile=log‘ is allowed, but ‘sqlldr scott/tiger control=foo log‘ is not, even though the

positionof the parameter ‘log‘ iscorrect.

b, sqlldr 将文本文件的导入到数据库

这里看个简单例子。看看sqlldr到底怎么工作的。

1,准备数据文件，例如input.txt.这个文件将导入到数据库中。

首先查看我们数据库的表格式。

create tablestudent(

SNAMEVARCHAR(20),

SAGEINTEGER,

SEMAILVARCHAR(20),

SPHONEVARCHAR(20),

SADDRESSVARCHAR(20)

)

input.txt 文件

12,12,abc@gmail.com,12,address13,13,abc@gmail.com,13,address14,14,abc@gmail.com,14,address15,15,abc@gmail.com,15,address16,16,abc@gmail.com,16,address17,17,abc@gmail.com,17,address18,18,abc@gmail.com,18,address19,19,abc@gmail.com,19,address

2，控制文件input.ctl

loaddata

infile‘input.txt‘appendinto table student --这里用的Append.

fields terminated by"," --这里表示逗号分割。

(SNAME,SAGE,SEMAIL,SPHONE,SADDRESS)

这里用的Append, 追加数据，还有几个其他的参数：

a，insert，为缺省方式，在数据装载开始时要求表为空

b，append，在表中追加新记录

c ，replace，删除旧记录，替换成新装载的记录

d，truncate，同上

3，sqlldr 调用控制文件

sqlldr username/[email protected] control =input.ctl //input.ctl 为控制文件

在这里需要提下，这里是会生成日志文件，默认为文件名文件名+.log. 当前为 input.log

如果执行失败了，会生成bad file. 如果在当前执行中错误，会生成input.bad file。

下面指定Log 和bad 文件，当然可以加上路径

sqlldr userid=username/password@database control=input.ctl log=input.log bad=input.bad SILENT=(HEADER, FEEDBACK)

SILENT=(HEADER, FEEDBACK) 控制端不显示信息，例如下面的信息将不再控制端显示。只在日志文件中

Record 4: Rejected - Error on tableEMP

ORA-00001: unique constraint violated

当然是可以显示指定的。

loaddata

infile input.txtbadfile t.bad

discardfile t.dsc

appendinto tablestudent

fields terminatedby","

(SNAME,SAGE,SEMAIL,SPHONE,SADDRESS)

看看日志文件：input.log

SQL*Loader: Release 10.2.0.1.0 - Production on Tue May 20 17:36:52 2014Copyright (c)1982, 2005, Oracle. Allrights reserved.

ControlFile: input1.ctl

DataFile: input1.ctl

BadFile: input1.bad

DiscardFile: none specified

(Allowalldiscards)Number to load: ALL

Number to skip: 0Errors allowed:50Bind array:64 rows, maximum of 256000bytes

Continuation: none specified

Path used: ConventionalTable STUDENT, loaded fromevery logical record.Insert option in effect for this table: APPENDColumn Name Position LenTerm Encl Datatype------------------------------ ---------- ----- ---- ---- ---------------------

SNAME FIRST * , CHARACTERSAGENEXT * , CHARACTERSEMAILNEXT * , CHARACTERSPHONENEXT * , CHARACTERSADDRESSNEXT * , CHARACTER

TableSTUDENT:1Row successfully loaded.0 Rows not loaded due todata errors.0 Rows not loaded because all WHENclauses were failed.0 Rows not loaded because all fields were null.Space allocated for bind array: 82560 bytes(64rows)Read buffer bytes: 1048576Total logical records skipped:0Total logical recordsread: 1Total logical records rejected:0Total logical records discarded:0Run beganon Tue May 20 17:36:52 2014Run endedon Tue May 20 17:36:52 2014Elapsed time was:00:00:00.05CPU time was:00:00:00.04View Code

4，查看数据库

到此一个简单的例子完成，从一个文本文件导入到数据库。

文件可以为不同格式文件，.dat,.csv都可以的。

C,sqlldr直接在控制文件中导入数据。

loaddata

infile*appendinto tablestudent

fields terminatedby","

(SNAME,SAGE,SEMAIL,SPHONE,SADDRESS)

begindata20,20,abc@gmail.com,20,address --这里是数据

D,当文件数据是以绝对位置分开的，我们可以直接截取。当然，截取的开始与结束必须小心了。

loaddata

infile t.dat

appendinto tablestudent

(SNAME position(01:20),

SAGE position(21:23) ,

SEMAIL position(41:60),

SPHONE position(61:80),

SADDRESS position(81:100)

)

t.dat 文件

Jack 12 abc@gmail.com 134998879Singapore

Jack212 abc@gmail.com 134998879Singapore

Jack312 abc@gmail.com 134998879Singapore

Jack412 abc@gmail.com 134998879Singapore

Jack512 abc@gmail.com 134998879Singapore

Jack612 abc@gmail.com 134998879Singapore

Jack712 abc@gmail.com 134998879 Singapore

还数据在Load to database 的时候，load的数据是可以改变的。

LOADDATA

INFILE*

INTO TABLEmodified_data

( rec_no "my_db_sequence.nextval",

region CONSTANT‘31‘,

time_loaded "to_char(SYSDATE,‘HH24:MI‘)",

data1 POSITION(1:5) ":data1/100",

data2 POSITION(6:15) "upper(:data2)",

data3 POSITION(16:22)"to_date(:data3, ‘YYMMDD‘)"

)

BEGINDATA

11111AAAAAAAAAA991201

22222BBBBBBBBBB990112

这里有很多命令的解释

这里有很多问题的回答(FAQ)

简单实现几个例子，稍后有时间添加多点理论知识，再边学习边完善了。

原文：http://www.cnblogs.com/lideng/p/3739380.html