输入sqlldr 后面不接任何参数 将显示所有的命令行参数的简单描述及其默认值(当你忘记某些参数时 也可以通过这个方式快速查询)
[plain]
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 )
load number of logical records to load (Default all)
errors number of errors to allow (Default )
rows number of rows in conventional path bind array or beeen direct path data saves
(Default: Conventional path Direct path all)
bindsize size of conventional path bind array in bytes (Default )
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 (Default FALSE)
skip_index_maintenance do not maintain indexes mark affected indexes as unusable (Default FALSE)
mit_discontinued mit loaded rows when load is discontinued (Default FALSE)
readsize size of read buffer (Default )
external_table use external table for loadNOT_USED GENERATE_ONLY EXECUTE (Default NOT_USED)
columnarrayrows number of rows for direct path column array (Default )
streamsize size of direct path stream buffer in bytes (Default )
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 )
date_cache size (in entries) of date conversion cache (Default )
no_index_errors abort load on any index errors (Default FALSE)
为了不用每次都在命令行中输入同样的参数 你也可以使用参数文件或把参数写在控制文件的OPTIONS字句里 当然命令行参数的优先级最高 可以覆盖参数文件和控制文件里的参数配置
SQL*Loader默认是导入到本地数据库 但也支持导入到远程数据库 只要在username后面加上 @远程数据库连接字符串 即可 如下所示
>sqlldr CONTROL=ulcase ctl
Username: scott@inst
Password: password
下面是命令行参数的具体解释
BAD 坏文件名 ( bad)
CONTROL 控制文件名 ( ctl)
DATA 数据文件名( dat) 等同于控制文件里的INFILE 可以指定多个数据文件
DIRECT true表示使用直接路径加载 false表示使用传统路径加载
DISCARD 丢失文件名( dsc)
DISCARDMAC 最多允许多少条记录可以被丢失 如果超过该数字 将停止加载
ERRORS 最多预习多少条记录insert失败 如果超过该数字 将停止加载
EXTERNAL_TABLE 是否使用外部表加载方式
FILE 该参数只在直接路径并行加载时有用
LOAD 最多允许多少条记录可以被加载
LOG 日志文件名( log)
PARALLEL true or false 直接路径加载是否使用并行模式
PARFILE 参数文件名( par)
ROWS 指定达到多少条记录时提交
SKIP 指定忽略最开始的多少条记录
lishixinzhi/Article/program/Oracle/201311/17310生成导入的文本文件
[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ cat getobject.sql
set echo off
set term off
set line 1000 pages 0
set feedhack off
set heading off
spool ldr_object.txt
select a.owner||',"'||a.object_name||'",'||a.object_id||','||
to_char(a.created,'yyyy-mm-dd hh24:mi:ss')|| ','||a.status
from dba_objects a,(select rownum rn from dual connect by
rownum<=10) b
spool off
set heading on
set feedback on
set term on
set echo on
[oracle@localhost ~]$
执行
SYS@PENG SQL>
@getobject.sql
[oracle@localhost ~]$ ll
总计 31244
-rw-r--r-- 1 oracle
oinstall
83 12-07 04:49 analyze_index.sql
-rw-r--r-- 1 oracle
oinstall
369 12-27 06:37 getobject.sql
-rw-r--r-- 1 oracle
oinstall
195 12-27 06:21 ldr_object.ctl
-rw-r--r-- 1 oracle oinstall 31941175 12-27 07:39
ldr_object.txt
[oracle@localhost ~]$ wc -l ldr_object.txt
502524
ldr_object.txt
生成的文件50万行。
创建导入的表,用户scott
SCOTT@PENG SQL>
drop table objects purge
SQL>create table objects(
2 owner varchar2(30),
3 object_name
varchar2(50),
4 object_id number,
5 status varchar2(10),
6 created date)
Table created.
Elapsed: 00:00:00.18
SQL>create index idx_obj_owner_name on
objects(owner,object_name)
Index created.
Elapsed: 00:00:00.33
SQL>
创建控制文件：ldr_object.ctl
load data
infile ldr_object.txt
truncate into table objects
fields terminated by "," optionally enclosed by '"'
(owner,
object_name,
object_id,
created date 'yyyy-mm-dd hh24:mi:ss',
status
)
[oracle@localhost ~]$ ll
总计 31244
-rw-r--r-- 1 oracle
oinstall
83 12-07 04:49 analyze_index.sql
-rw-r--r-- 1 oracle
oinstall
369 12-27 06:37 getobject.sql
-rw-r--r-- 1 oracle
oinstall
195 12-27 07:47 ldr_object.ctl
-rw-r--r-- 1 oracle oinstall 31941175 12-27 07:39
ldr_object.txt
[oracle@localhost ~]$
[oracle@localhost ~]$ sqlldr control=ldr_object.ctl errors=10
Username:scott
Password:
SQL*Loader: Release 10.2.0.4.0 - Production on Mon Dec 27 07:54:02
2010
Copyright (c) 1982, 2007, Oracle. All rights
reserved.
Commit point reached - logical record count 64
Commit point reached - logical record count 128
Commit point reached - logical record count 192
查日志文件
Table OBJECTS:
502520 Rows
successfully loaded.
4 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:
82560 bytes(64 rows)
Read buffer bytes: 1048576
Total logical records
skipped:
0
Total logical records
read:
502524
Total logical records
rejected:
4
Total logical records
discarded:
0
Run began on Mon Dec 27 07:47:55 2010
Run ended on Mon Dec 27 07:50:24 2010
Elapsed time
was:
00:01:26.31
CPU time
was:
00:00:12.52
提高导入时一次加载的行数
[oracle@localhost ~]$ sqlldr scott/triger control=ldr_object.ctl
errors=10 rows=640
查看日志
value used for ROWS parameter changed from 640 to 198
Table OBJECTS:
502520 Rows
successfully loaded.
4 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:
255420 bytes(198
rows)
Read buffer bytes: 1048576
Total logical records
skipped:
0
Total logical records
read:
502524
Total logical records
rejected:
4
Total logical records
discarded:
0
Run began on Mon Dec 27 07:59:44 2010
Run ended on Mon Dec 27 08:01:20 2010
Elapsed time
was:
00:00:54.78
CPU time
was:
00:00:08.25
提高sindsize的大小到10m 和一次加载5000条记录
[oracle@localhost ~]$ sqlldr scott/triger control=ldr_object.ctl
errors=10 rows=5000
bindsize=10485760
SQL*Loader: Release 10.2.0.4.0 - Production on Mon Dec 27
08:07:09 2010
Copyright (c) 1982, 2007, Oracle. All rights
reserved.
specified value for readsize(1048576) less than
bindsize(10485760)
Commit point reached - logical record count 5000
Commit point reached - logical record count 10000
Commit point reached - logical record count 15000
查看日志
Table OBJECTS:
502520 Rows successfully loaded.
4 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:
6450000 bytes(5000 rows)
Read buffer bytes:10485760
Total logical records
skipped:
0
Total logical records
read:
502524
Total logical records
rejected:
4
Total logical records
discarded:
0
Run began on Mon Dec 27 08:07:09 2010
Run ended on Mon Dec 27 08:08:09 2010
Elapsed time
was:
00:00:33.91
CPU time
was:
00:00:05.47
使用默认参数，开启直接路径加载
[oracle@localhost ~]$ sqlldr scott/triger control=ldr_object.ctl
errors=10 direct=true
SQL*Loader: Release 10.2.0.4.0 - Production on Mon Dec 27
08:15:02 2010
Copyright (c) 1982, 2007, Oracle. All rights
reserved.
Load completed - logical record count 502524.
[oracle@localhost ~]$
查看日志
Table OBJECTS:
502520 Rows successfully loaded.
4 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.
Date conversion cache disabled due to
overflow (default size: 1000)
Bind array size not used in direct path.
Column array rows
:5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records
skipped:
0
Total logical records
read:
502524
Total logical records
rejected:
4
Total logical records
discarded:
0
Total stream buffers loaded by SQL*Loader main
thread:
122
Total stream buffers loaded by SQL*Loader load
thread:
67
Run began on Mon Dec 27 08:15:02 2010
Run ended on Mon Dec 27 08:15:23 2010
Elapsed time
was:
00:00:12.22
CPU time
was:
00:00:02.33
再次提高，使用之前的参数和直接路径加载
[oracle@localhost ~]$ sqlldr scott/triger control=ldr_object.ctl
errors=10 rows=5000 bindsize=10485760 direct=true
SQL*Loader: Release 10.2.0.4.0 - Production on Mon Dec 27
08:19:45 2010
Copyright (c) 1982, 2007, Oracle. All rights
reserved.
Save data point reached - logical record count 5000.
Save data point reached - logical record count 10000.
Save data point reached - logical record count 15000.
Save data point reached - logical record count 20000.
查看日志
Table OBJECTS:
502520 Rows successfully loaded.
4 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.
Date conversion cache disabled due to
overflow (default size: 1000)
Bind array size not used in direct path.
Column array rows
:5000
Stream buffer bytes: 256000
Read buffer bytes: 1048576
Total logical records
skipped:
0
Total logical records
read:
502524
Total logical records
rejected:
4
Total logical records
discarded:
0
Total stream buffers loaded by SQL*Loader main
thread:
131
Total stream buffers loaded by SQL*Loader load
thread:
53
Run began on Mon Dec 27 08:19:45 2010
Run ended on Mon Dec 27 08:20:07 2010
Elapsed time
was:
00:00:11.52
CPU time
was:
00:00:02.57
从最开始的
Elapsed time
was:
00:01:26.31
CPU time
was:
00:00:12.52
到最后的
Elapsed time
was:
00:00:11.52
CPU time
was:
00:00:02.57
效果非常明显。
1、准备工作：创建需要导入数据的表结构，如果已有相关的表，此步省略！SQL>create table test
2 (
3host VARCHAR2(30),
4user_name VARCHAR2(30),
5ip_address VARCHAR2(15),
6passVARCHAR2(4) default 'no' not null,7judge NUMBER default 0 not null,8endtime DATE
9 )
表已创建。
2、编写sqlload导入数据的控制文件，这里测试的控制文件如下，可以根据自己需要添加相关的控制参数，测试的话复制保存为txt文件即可！
LOAD DATA
INFILE 'd:\data.txt'
INTO TABLE test
TRUNCATE
fields terminated by ','
trailing nullcols
(HOST,USER_NAME,IP_AddrESS,PASS,JUDge,endTIME)控制文件还有其他参数，根据自己需求调整和测试：
附部分控制参数：具体用法以官方文档为准
OPTIONS (skip=1,rows=128) -- sqlldr 命令显示的选项可以写到这里边来,skip=1 用来跳过数据中的第一行LOAD DATA
INFILE "users_data.csv" --指定外部数据文件，可以是不同格式的数据文件，如csv、txt都支持可以写多个 INFILE "another_data_file.csv" 指定多个数据文件truncate -- *** 作类型，用 truncate table 来清除表中原有记录，根据情况而定是否需要清楚原有表中数据INTO TABLE users --要插入记录的表Fields terminated by "," --数据中每行记录用 "," 分隔Optionally enclosed by '"'--数据中每个字段用 '"' 框起，比如字段中有 "," 分隔符时trailing nullcols --表的字段没有对应的值时允许为空(
virtual_column FILLER,--这是一个虚拟字段，用来跳过由 PL/SQL Developer 生成的第一列序号user_id number, --字段可以指定类型，否则认为是 CHARACTER 类型, log 文件中有显示user_name,
login_times,
last_login DATE "-MM-DD HH24:MI:SS" -- 指定接受日期的格式，相当用 to_date() 函数转换)
insert --为缺省方式，在数据装载开始时要求表为空append --在表中追加新记录
replace --删除旧记录(用 delete from table 语句)，替换成新装载的记录truncate --删除旧记录(用 truncate table 语句)，替换成新装载的记录3、创建需要导入的数据，注意数据格式必须和表结构严格对应，否则导入失败！测试数据如下：有部分数据最后字段为空，所以控制文件中需要加trailing nullcols 参数！
ttt,SCOTT,192.168.1.111,yes,1,
,JACK,192.168.1.20,no,1,
,TOM,192.168.1.20,no,1,
WEB1,HAHA,192.168.1.1,no,1,
XXX,ROBIN,111.111.111.111,no,1,08-AUG-08
DB2,LUCY,192.168.10.10,no,1,
ORACLE,LILY,222.222.222.222,no,1,
WORKGROUP,DENNIS,133.133.133.133,no,0,08-AUG-08DCR,CANDY,192.168.100.10,no,1,
T3,FLY,192.168.10.33,no,1,
T1,LINDA,192.168.10.200,no,1,08-AUG-08
T2,LILEI,192.168.100.31,no,1,08-AUG-08
4、导入数据-导入时也有相关的参数进行控制
附部分导入参数：各参数可以再命令行下输入sqlldr查看C:\Documents and Settings\Administrator>sqlldrSQL*Loader: Release 11.2.0.1.0 - Production on 星期三 2月 27 17:13:24 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
用法: SQLLDR keyword=value [,keyword=value,...]
有效的关键字:
userid -- ORACLE 用户名/口令
control -- 控制文件名
log -- 日志文件名
bad -- 错误文件名
data -- 数据文件名
discard -- 废弃文件名
discardmax -- 允许废弃的文件的数目 (全部默认)skip -- 要跳过的逻辑记录的数目 (默认 0)
load -- 要加载的逻辑记录的数目 (全部默认)errors -- 允许的错误的数目 (默认 50)rows -- 常规路径绑定数组中或直接路径保存数据间的行数(默认: 常规路径 64, 所有直接路径)
bindsize -- 常规路径绑定数组的大小 (以字节计) (默认 256000)silent -- 运行过程中隐藏消息 (标题,反馈,错误,废弃,分区)direct -- 使用直接路径 (默认 FALSE)parfile -- 参数文件: 包含参数说明的文件的名称parallel -- 执行并行加载(默认 FALSE)file -- 要从以下对象中分配区的文件
skip_unusable_indexes -- 不允许/允许使用无用的索引或索引分区 (默认 FALSE)skip_index_maintenance -- 没有维护索引, 将受到影响的索引标记为无用 (默认 FALSE)commit_discontinued -- 提交加载中断时已加载的行 (默认 FALSE)readsize -- 读取缓冲区的大小 (默认 1048576)external_table -- 使用外部表进行加载NOT_USED, GENERATE_ONLY, EXECUTE (默认 NOT_USED)
columnarrayrows -- 直接路径列数组的行数 (默认 5000)streamsize -- 直接路径流缓冲区的大小 (以字节计) (默认 256000)multithreading -- 在直接路径中使用多线程
resumable -- 启用或禁用当前的可恢复会话 (默认 FALSE)resumable_name -- 有助于标识可恢复语句的文本字符串resumable_timeout -- RESUMABLE 的等待时间 (以秒计) (默认 7200)date_cache -- 日期转换高速缓存的大小 (以条目计) (默认 1000)no_index_errors -- 出现任何索引错误时中止加载 (默认 FALSE)如下命令：control=指定控制文件和路径 log=导入日志文件保存 bad=错误信息 data=数据文件开始导入：
C:\Documents and Settings\Administrator>sqlldr scott/tiger control=d:\sqlload.txt log=d:\loadlog.txt bad=d:\bad.txt data=d:\data.txtSQL*Loader: Release 11.2.0.1.0 - Production on 星期三 2月 27 17:06:52 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
达到提交点 - 逻辑记录计数 12
导入成功后查看结果：
C:\Documents and Settings\Administrator>sqlplus scott/tigerSQL*Plus: Release 11.2.0.1.0 Production on 星期三 2月 27 17:07:05 2013Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>select * from test
HOST USER_NAME IP_ADDRESS PASS JUDGE ENDTIME---------- ---------- ------------------------------ -------- ---------- --------------ttt SCOTT 192.168.1.111 yes 1 JACK 192.168.1.20no1TOM 192.168.1.20no1WEB1HAHA192.168.1.1 no1DB2 LUCY 192.168.10.10 no1ORACLE LILY 222.222.222.222 no1DCR CANDY 192.168.100.10no1T3FLY 192.168.10.33 no1已选择8行。
结果显然与数据文件不一致，最后一个字段有值的数据没有导入！这不是最后想要的结果！但可以确定已经有数据导入，表示表对象和控制文件没问题！
应该是数据文件的格式问题！注意数据文件最后一个字段是日期型数据。查看是不是数据库现有日期类型不支持数据文件的表示格式SQL>select sysdate from dual
SYSDATE
--------------
27-2月 -13
显然和我们数据文件的日期显示不一致，且是中文，这里可以把数据文件的最后字段的数据改成和数据库一样的格式，也可以对数据库的格式和语言进行更改，因为测试系统是windows才会出现这个错误，在linux可以避免！
如果是linux 可以尝试以下步骤解决：
alter system set nls_date_format='DD-MON-RR'alter system set nls_language= american scope = spfile------------得重启数据库这里由于是windows，这里就不更改语言和重启数据库，直接对数据文件进行更改，修改后的数据文件如下ttt,SCOTT,192.168.1.111,yes,1,
,JACK,192.168.1.20,no,1,
,TOM,192.168.1.20,no,1,
WEB1,HAHA,192.168.1.1,no,1,
XXX,ROBIN,111.111.111.111,no,1,08-5月 -08DB2,LUCY,192.168.10.10,no,1,
ORACLE,LILY,222.222.222.222,no,1,
WORKGROUP,DENNIS,133.133.133.133,no,0,08-5月 -08DCR,CANDY,192.168.100.10,no,1,
T3,FLY,192.168.10.33,no,1,
T1,LINDA,192.168.10.200,no,1,08-5月 -08
T2,LILEI,192.168.100.31,no,1,08-5月 -08
再导入一次，导入后进行查看结果
C:\Documents and Settings\Administrator>sqlldr scott/tiger control=d:\sqlload.txt log=d:\loadlog.txt bad=d:\bad.txt data=d:\data.txtSQL*Loader: Release 11.2.0.1.0 - Production on 星期三 2月 27 17:48:44 2013Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
达到提交点 - 逻辑记录计数 12
C:\Documents and Settings\Administrator>sqlplus scott/tigerSQL*Plus: Release 11.2.0.1.0 Production on 星期三 2月 27 17:49:21 2013Copyright (c) 1982, 2010, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL>col host for a10
SQL>col user_name for a15
SQL>select * from test
HOST USER_NAME IP_ADDRESS PASS JUDGE ENDTIME------------------------- -------------------------------------- ---------- --------------ttt SCOTT 192.168.1.111 yes 1 JACK192.168.1.20 no1 TOM 192.168.1.20 no1WEB1 HAHA192.168.1.1 no1XXX ROBIN 111.111.111.111no1 08-5月 -08DB2 LUCY192.168.10.10no1ORACLE LILY222.222.222.222 no1WORKGROUP DENNIS 133.133.133.133 no0 08-5月 -08DCRCANDY 192.168.100.10 no1T3 FLY 192.168.10.33 no1T1 LINDA 192.168.10.200 no1 08-5月 -08T2 LILEI 192.168.100.31 no1 08-5月 -08已选择12行。
恢复正常：数据和数据文件完全一样！到此整个数据导入完成！主要注意的地方还是数据文件的建立，确认分隔以及和表的结构对应！
欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）