ORCLE 数据库备份与恢复之二：SQL*Loader

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

 

http://tech.it168.com/a2008/0822/201/000000201606_2.shtml

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

转载于:http://blog.itpub.net/21806941/viewspace-662934/