2.1 基本知识
Oracle 的
SQL* LOADER 可以将
外部格式化的文本数据加载到数据库表中。通常 与 SPOOL导出
文本数据方法配合使用。
1
.
命
令格式
SQL
LDR keyword=value [,keyword=value,...]
例:
$
sqlldr user/pwd control=emp.ctl data=emp.dat bad=emp.bad log=emp.log
2
.
控
制文件
SQL*LOADER
根据控制文件可
以找到需要加载的数据。并且分析和解释这些数
据。
控制
文件由三个部分组成,具体参数
参考帮助文档:
1.
全局选件,行
,跳过的记录数等;
2.
INFILE 子
句指定的输入数据;
3.
数据特性说明。
comm
ent: --注释
例:
load
data infile *
append
--除了 append外,还有
insert、replace、truncate等方式
into
table emp
fields
terminated b y ‘|’
(
no
float external, name char(20),
age
integer external,
duty
char(1),
s
alary float external,
upd_ts
date(14) ‘YYYYMMDDHH24MISS’
)
begindata
100000000003|Mulder|000020|1|000000005000|20020101000000
100000000004|Scully|000025|2|000000008000|20020101235959
控制
文件中 infile选项跟 s
qlldr 命令行中 data 选项
含义相同,如使用 infile
*则
表明
数
据在本控制文件以 begin
data 开
头的区域内。 一些
选项:
FIELDS
TERMINATED BY WHITESPACE FIELDS TERMINATED BY x'09'
FIL
LER_1 FILLER, // 指定
某一列将不会被装载
DE
PTNO position(1:2), DNAME position(*:16), // 指定列的位
置
SE
QNO RECNUM //载
入每行的行号
SKIP
n // 指定
导入时可以跳过多少行数据
3
.数据文件
按
控制文件数据格式定义的数据行集,例:
100000000001|Tom|000020|1|000000005000|20020101000000
100000000002|J
erry|000025|2|000000008000|20020101235959
固
定格式、可变格式、流记录格式:
固定格式
:
当数据
固定的格式(长度
一样)时且是在文
件中得到时,要用 INFILE
"fix n"
load
data
infile
'example.dat' "fix 11"
into
table example
fields
terminated b y ',' 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 b y ',' optionally enclosed by '"'
(col1
char(5), col2 char(7)) example.dat:
009hello,cd,010world,im,
012m
y,name is,
流记
录格式:
// Stream-recored format:
load
data infile 'xx.dat' "str '|\n'"
into
table xx field terminated b y ',' optionally enclosed by '"'
(col1
char(5), col2 char(7))
example.dat:
hello,
ccd,|
world,
bb,|
4
.
坏文件
bad=emp.bad
坏文
件包含那些被 SQL*Loader拒绝
的记录。被拒绝的记录可能是不符合要求的记
录。
5
.
日志文件及日志信息
log=emp.log
当 SQL*Loader
开
始执行后,它就自
动建立
日志
文件。日志文件包含有加载的总 结,加载中的错
误信息等。
2.2 高级选项
1
. Conventional Path Load
与
Direct Path Load
C
o
n
v
e
n
t
i
o
n
a
l
-
p
at
h Load:
通过
常规通道方式上载。
特点:com
mit, always gen redo logs, enforce all constraints, fire insert triggers, can load into cluster, other user can make change
rows:每
次提交的记录数
binds
ize:
每次提交记录的缓冲区
reads
ize:与 bindsize 成对
使用,其中
较小者会自动调整到较大者
s
qlldr 先
计算单条记录长度,乘以 rows,如小于 bindsize,不会
试图扩张 rows以填充 bindsize;如
超出,则以 bindsize 为准
。 命
令为:
$
sqlldr dbuser/oracle control=emp.ctl log=emp.log rows=10000 bindsize=8192000
D
i
r
ec
t-
Path Load:
通过
直通方式上载,可
以跳过数据库的相关逻辑,不进行
SQL解析,而直接
将数 据
导入到数据文件中。
特点:s
ave, conditionly gen redo logs, enforce PK UK NN, not fire triggers, can not load into cluster, other user can not make change命令为:
$
sqlldr dbuser/oracle control=emp.ctl log=emp.log direct=true
2
. SPOOL
导出文本数据方法
导入
的数据文件可以用 SPOOL导出
文本数据方法生成。
SQL*PLUS环境设置
SET
NEWPAGE NONE HEADING OFF SPACE 0 PAGESIZE 0
SET
TRIMOUT ON TRIMSPOOL ON LINESIZE 2500
注:LINESIZE 要稍微
设置大些,免得
数据被截断,它应
和相应的 TRIMSPOOL结合
使用防止导出的文本有太多的尾部空格。
但是
如果 LINE
SIZE 设
置太大,会
大大降低导出的速度,另
外在 WINDOWS下导 出
最好不要用 PLSQL导出,速度
比较慢,直
接用
COMMEND 下的 SQLPLUS命
令最 小
化窗口执行。对于字段内包含很多回车换行符的应该给与过滤,形成比较规矩的文本 文件。
通常
情况下,我们使用 SPOOL方法
,将数据库中的表导出为文本文件,如下述:
s
et trimspool on
s
et linesize 120 pagesize 2000 newpage 1 heading off term off spool 路径+文
件名
s
elect col1||','||col2||','||col3||','||col4||'..' from tablename;
s
pool off
2.3 脚本
1
.
将表中数据记录导出为字段值用分隔符
'|'
分开的
.
d
at
文件
#!/bin/ks
h
##################################################################
##
名称:
unloadtable
##
功能:
本 s
hell 用于
将表中数据记录导出
##
导
出为字段值用分隔符'|'分开的.dat文
件
##
编者:
##
日期:
2006.03.18
##################################################################
if
[ $# -ne 3 ]
then
echo
"usage:unloadtable tablename username password."
exit
0
fi
##准备工
作
echo
"set heading off " >/tmp/$1.col
echo
"set pagesize 0" >>/tmp/$1.col
echo
"set linesize 800 " >>/tmp/$1.col
echo
"set feedback off " >>/tmp/$1.col
echo
"set tab off " >>/tmp/$1.col
echo
"select column_name||',' from user_tab_columns where lower(table_name)='$1' order by
column_id;
" >> /tmp/$1.col
##产生 s
elect 语
句
echo
"set heading off " >/tmp/$1.sel
echo
"set pagesize 0" >>/tmp/$1.sel
echo
"set linesize 800 " >>/tmp/$1.sel
echo
"set feedback off " >>/tmp/$1.sel
echo
"set tab off " >>/tmp/$1.sel
echo
"select " >>/tmp/$1.sel
echo
`sqlplus -s $2/$3
>>/tmp/$1.s
el
##生成 dat文
件
#echo
"from $1;\n/" >>/tmp/$1.sel 由于
/ 导致
多执行一次 s
elect
echo
"from $1;\n" >>/tmp/$1.sel
s
qlplus -s $2/$3 < /tmp/$1.sel >$1_tmp.dat
#awk
'{if(FNR!=1) print $0}' $1_tmp.dat >$1.dat FNR 选
项使得第一条记录选不出
awk
'{print $0}' $1_tmp.dat >$1.dat
rm
-f $1_tmp.dat
2
.
将数据导入到相应表中
#!/bin/ks
h
##################################################################
##
名称:loadtable
##
功能:本 s
hell 用
于将已经准备好的.dat数
据文件导入相应的表中
##
.dat 文件
各个字段值用分隔符'|'分开
。
##
编者:
##
日期:
2006.03.18
##################################################################
if
[ $# -ne 3 ]
then
echo
"usage:loadtable tablename username password."
exit
0
fi
##准备工
作
echo
"set heading off " >/tmp/$1.colsql
echo
"set pagesize 0" >>/tmp/$1.colsql
echo
"set linesize 800 " >>/tmp/$1.colsql
echo
"set feedback off " >>/tmp/$1.colsql
echo
"set tab off " >>/tmp/$1.colsql
echo
"select column_name||',' from user_tab_columns where lower(table_name)='$1' order by
column_id;
" >> /tmp/$1.colsql
##产生
ctl文件
echo
"load data" >/tmp/$1.ctl
echo
"infile *" >>/tmp/$1.ctl
echo
"into table $1" >>/tmp/$1.ctl
echo
"fields terminated by '|'" >>/tmp/$1.ctl
echo
`sqlplus -s $2/$3 < /tmp/$1.colsql` |sed "s/,$/)/g" |sed "s/^/(/g" >>/tmp/$1.ctl
##开始导入数据
echo
"truncate table $1;" >/tmp/$1.sql
s
qlplus $2/$3 < /tmp/$1.sql
s
qlldr $2/$3 data=$1.dat control=/tmp/$1.ctl log=/tmp/$1.log
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21806941/viewspace-662934/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21806941/viewspace-662934/