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

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

 

2.1 基本知识

    的  SQL* LOADER  可以将外部格式化的文本数据加载到数据库表中。通常 与 SPOOL导出文本数据方法配合使用。

    1.命令格式

    SQLLDR 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.  数据特性说明。

    comment： ——注释

    例：

    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），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 开头的区域内。 一些选项：FIELDS TERMINATED BY WHITESPACE FIELDS TERMINATED BY x'09' FILLER_1 FILLER， //  指定某一列将不会被装载

    DEPTNO position（1：2）， DNAME position（*：16）， //  指定列的位置SEQNO RECNUM //载入每行的行号

    SKIP n          //  指定导入时可以跳过多少行数据

    3.数据文件

    按控制文件数据格式定义的数据行集，

    例：

    100000000001|Tom|000020|1|000000005000|20020101000000

    100000000002|Jerry|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，

    012my，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

    Conventional-path Load：通过常规通道方式上载。

    特点：commit， always  gen redo logs，  enforce all constraints， fire insert triggers， can load into cluster， other user can make change

    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 Load：

    通过直通方式上载，可以跳过数据库的相关逻辑，不进行  SQL解析，而直接将数 据导入到数据文件中。

    特点：save， 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结合使用防止导出的文本有太多的尾部空格。

    但是如果 LINESIZE 设置太大，会大大降低导出的速度，另外在 WINDOWS下导 出最好不要用 PLSQL导出，速度比较慢，直接用  COMMEND 下的 SQLPLUS命令最 小化窗口执行。对于字段内包含很多回车换行符的应该给与过滤，形成比较规矩的文本 文件。

    通常情况下，我们使用 SPOOL方法，将数据库中的表导出为文本文件，如下述：

    set trimspool on

    set linesize 120 pagesize 2000 newpage 1 heading off    term off spool  路径+文件名

    select col1||'，'||col2||'，'||col3||'，'||col4||'……' from tablename；

    spool off

  2.3 脚本

    1.  将表中数据记录导出为字段值用分隔符'|'分开的。dat文件

    #！/bin/ksh

    ##################################################################

    ##    名称： unloadtable

    ##    功能：  本 shell 用于将表中数据记录导出

    ##                 导出为字段值用分隔符'|'分开的。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

    ##产生 select 语句

    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.col`  |sed  "s/，/||'|'||/g"  |sed  "s/||$//g"|sed  "s/date/\"date\"/g"

>>/tmp/$1.sel

    ##生成 dat文件

    #echo "from $1；\n/" >>/tmp/$1.sel    由于  /  导致多执行一次 select

    echo "from $1；\n" >>/tmp/$1.sel

    sqlplus -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/ksh

    ##################################################################

    ##    名称：loadtable

    ##    功能：本 shell 用于将已经准备好的。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

    sqlplus $2/$3 < /tmp/$1.sql

    sqlldr $2/$3 data=$1.dat control=/tmp/$1.ctl log=/tmp/$1.log

简述数据库导入Oracle SQL*Loader指南:

SQL*Loader是数据库导入外部数据的一个工具。它和DB2的Load工具相似，但有更多的选择，它支持变化的加载模式，可选的加载及多表加载。

如何使用 SQL*Loader 工具

我们可以用的sqlldr工具来导入数据。例如:

sqlldr scott/tiger control=loader.ctl

控制文件(loader.ctl) 将加载一个外部数据文件（含分隔符)loader.ctl如下:

load data 
infile 'c:\data\mydata.csv' 
into table emp 
fields terminated by "," optionally enclosed by '"' 
      ( empno, empname, sal, deptno )

mydata.csv 如下:

10001,"Scott Tiger", 1000, 40 
      10002,"Frank Naude", 500, 20

下面是一个指定记录长度的示例控制文件。"*" 代表数据文件与此文件同名，即在后面使用BEGINDATA段来标识数据。

load data 
infile * 
replace 
into table departments 
( dept position (02:05) char(4), 
deptname position (08:27) char(20) 
) 
begindata 
COSC COMPUTER SCIENCE 
ENGL ENGLISH LITERATURE 
MATH MATHEMATICS 
      POLY POLITICAL SCIENCE

Unloader这样的工具

Oracle 没有提供将数据导出到一个文件的工具。但是，我们可以用SQL*Plus的select 及 format 数据来输出到一个文件：

set echo off newpage 0 space 0 pagesize 0 
feed off head off trimspool on 
spool oradata.txt 
select col1 || ',' || col2 || ',' || col3 
from tab1 
where col2 = 'XYZ'; 
      spool off

另外，也可以使用使用 UTL_FILE PL/SQL 包处理:

rem Remember to update initSID.ora, 
utl_file_dir='c:\oradata' parameter 
declare 
fp utl_file.file_type; 
begin 
fp := utl_file.fopen('c:\oradata','tab1.txt','w'); 
utl_file.putf(fp, '%s, %s\n', 'TextField', 55); 
utl_file.fclose(fp); 
end; 
      /

 

当然你也可以使用第三方工具，如SQLWays ,TOAD for Quest等。

加载可变长度或指定长度的记录：

LOAD DATA 
INFILE * 
INTO TABLE load_delimited_data 
FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' 
TRAILING NULLCOLS 
( data1, 
data2 
) 
BEGINDATA 
11111,AAAAAAAAAA 
22222,"A,B,C,D," 
下面是导入固定位置（固定长度）数据示例： 
LOAD DATA 
INFILE * 
INTO TABLE load_positional_data 
( data1 POSITION(1:5), 
data2 POSITION(6:15) 
) 
BEGINDATA 
11111AAAAAAAAAA 
      22222BBBBBBBBBB

跳过数据行，可以用 “SKIP n”关键字来指定导入时可以跳过多少行数据。如：

LOAD DATA 
INFILE * 
INTO TABLE load_positional_data 
SKIP 5 
( data1 POSITION(1:5), 
data2 POSITION(6:15) 
)
BEGINDATA 
11111AAAAAAAAAA 
      22222BBBBBBBBBB

导入数据时修改数据：

在导入数据到数据库时，可以修改数据。注意，这仅适合于常规导入，并不适合 direct导入方式.如：

LOAD DATA 
INFILE * 
INTO TABLE modified_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 
LOAD DATA 
INFILE 'mail_orders.txt' 
BADFILE 'bad_orders.txt' 
APPEND 
INTO TABLE mailing_list 
FIELDS TERMINATED BY "," 
( addr, 
city, 
state, 
zipcode, 
mailing_addr "decode(:mailing_addr, null, :addr, :mailing_addr)", 
mailing_city "decode(:mailing_city, null, :city, :mailing_city)", 
mailing_state 
      )

 

将数据导入多个表：

如:

LOAD DATA 
INFILE * 
REPLACE 
INTO TABLE emp 
WHEN empno != ' ' 
( empno POSITION(1:4) INTEGER EXTERNAL, 
ename POSITION(6:15) CHAR, 
deptno POSITION(17:18) CHAR, 
mgr POSITION(20:23) INTEGER EXTERNAL 
) 
INTO TABLE proj 
WHEN projno != ' ' 
( projno POSITION(25:27) INTEGER EXTERNAL, 
empno POSITION(1:4) INTEGER EXTERNAL 
      )

导入选定的记录：

如下例： (01) 代表第一个字符, (30:37) 代表30到37之间的字符:

LOAD DATA 
INFILE 'mydata.dat' BADFILE 'mydata.bad' 
DISCARDFILE 'mydata.dis' 
APPEND 
INTO TABLE my_selective_table 
WHEN (01) <>; 'H' and (01) <>; 'T' 
and (30:37) = '19991217' 
( 
region CONSTANT '31', 
service_key POSITION(01:11) INTEGER EXTERNAL, 
call_b_no POSITION(12:29) CHAR 
      )

导入时跳过某些字段：

可用 POSTION(x:y) 来分隔数据在Oracle8i中可以通过指定FILLER字段实现。FILLER 字段用来跳过、忽略导入数据文件中的字段。如：

LOAD DATA 
TRUNCATE INTO TABLE T1 
FIELDS TERMINATED BY ',' 
( field1, 
field2 FILLER, 
field3 
      )

导入多行记录：

可以使用下面两个选项之一来实现将多行数据导入为一个记录:

CONCATENATE: - use when SQL*Loader should combine 
the same number of physical records together 
to form one logical record
CONTINUEIF - use if a condition indicates 
that multiple records should be treated as 
      oneEgby having a '#' character in column 1

SQL*Loader 数据的提交

一般情况下是在导入数据文件数据后提交的。也可以通过指定 ROWS= 参数来指定每次提交记录数。

提高 SQL*Loader　的性能：

(1) 一个简单而容易忽略的问题是，没有对导入的表使用任何索引和/或约束(主键)。如果这样做，甚至在使用ROWS=参数时，会很明显降低数据库导入性能。

(2) 可以添加 DIRECT=TRUE来提高导入数据的性能。当然，在很多情况下，不能使用此参数。

(3) 通过指定　UNRECOVERABLE选项，可以关闭数据库的日志。这个选项只能和 direct 一起使用。

(4) 可以同时运行多个导入任务。

常规导入与direct导入方式的区别：

常规导入可以通过使用 INSERT语句来导入数据。Direct导入可以跳过数据库的相关逻辑(DIRECT=TRUE)，而直接将数据导入到数据文件中。