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
skip Number of logical records to skip
load Number of logical records to load
errors Number of errors to allow
rows Number of rows in conventional path bind array or between direct
path data saves
bindsize Size of conventional path bind array in bytes
silent Suppress messages during run (header,feedback,errors,discards,pa
rtitions)
direct use direct path
_synchro internal testing
parfile parameter file: name of file that contains parameter specificati
ons
parallel do parallel load
file File to allocate extents from
skip_unusable_indexes disallow/allow unusable indexes or index partiti
ons
skip_index_maintenance do not maintain indexes, affected indexes a
s unusable
commit_discontinued commit loaded rows when load is discontinued
_display_exitcode Display exit code for SQL*Loader execution
readsize Size of Read buffer
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
skip Number of logical records to skip
load Number of logical records to load
errors Number of errors to allow
rows Number of rows in conventional path bind array or between direct
path data saves
bindsize Size of conventional path bind array in bytes
silent Suppress messages during run (header,feedback,errors,discards,pa
rtitions)
direct use direct path
_synchro internal testing
parfile parameter file: name of file that contains parameter specificati
ons
parallel do parallel load
file File to allocate extents from
skip_unusable_indexes disallow/allow unusable indexes or index partiti
ons
skip_index_maintenance do not maintain indexes, affected indexes a
s unusable
commit_discontinued commit loaded rows when load is discontinued
_display_exitcode Display exit code for SQL*Loader execution
readsize Size of Read buffer
首先编写一个控制命令的脚本文件,通常以ctl结尾,内容如下:
emp.ctl
load data
append
into table emp
fields terminated by '|'
(
no float external,
name char(20),
age integer external,
duty char(1),
salary float external,
upd_ts date(14) 'YYYYMMDDHH24MISS'
)
括号里对数据文件里每个数据域进行解释,以此在上载时与目标表进行比对。
除了append外,还有insert、replace、truncate等方式,与append大同小异,不作更多的解释。
再将上载数据组织成数据文件,通常以dat结尾,内容如下:
emp.dat
100000000001|Tom|000020|1|000000005000|20020101000000
100000000002|Jerry|000025|2|000000008000|20020101235959
分隔符要与ctl文件中fields terminated by指定的一致,这个例子中为"|"
ctl和dat文件就绪后可以执行上载,命令为:
sqlldr dbuser/oracle control=emp.ctl data=emp.dat
也可以将dat文件合并在ctl文件中,ctl文件改写为:
emp2.ctl
load data
infile *
append
into table emp
fields terminated by '|'
(
no float external,
name char(20),
age integer external,
duty char(1),
salary float external,
upd_ts date(14) 'YYYYMMDDHH24MISS'
)
begindata
100000000003|Mulder|000020|1|000000005000|20020101000000
100000000004|Scully|000025|2|000000008000|20020101235959
控制文件中infile选项跟sqlldr命令行中data选项含义相同,如使用infile *则表明数据在本控制文件以begin data开头的区域内。
这样命令变成:
sqlldr dbuser/oracle control=emp2.ctl
conventional path
通过常规通道方式上载。
rows:每次提交的记录数
bindsize:每次提交记录的缓冲区
readsize:与bindsize成对使用,其中较小者会自动调整到较大者
sqlldr先计算单条记录长度,乘以rows,如小于bindsize,不会试图扩张rows以填充bindsize;如超出,则以bindsize为准。
命令为:
sqlldr dbuser/oracle control=emp.ctl log=emp.log rows=10000 bindsize=8192000
direct path
通过直通方式上载,不进行SQL解析。
命令为:
sqlldr dbuser/oracle control=emp.ctl log=emp.log direct=true