2025年sqlldr 导入（sqlldr 导入数据有换行符）

大家好，我是讯享网，很高兴认识大家。

　　一、基本知识

　　Oracle 的  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　　3|Mulder|000020|1|000000005000|000　　4|Scully|000025|2|000000008000|959　　控制文件中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、数据文件　　按控制文件数据格式定义的数据行集，　　例：　　1|Tom|000020|1|000000005000|000　　2|Jerry|000025|2|000000008000|959　　固定格式、可变格式、流记录格式：　　固定格式：　　当数据固定的格式（长度一样）时且是在文件中得到时，要用 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 '| '"　　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  开始执行后，它就自动建立  日志文件。日志文件包含有加载的总结，加载中的错误信息等。　　二、高级选项　　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=

　　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　　三、脚本　　1、将表中数据记录导出为字段值用分隔符'|'分开的。dat文件　　#！/bin/ksh　　　　    名称： unloadtable　　    功能：  本 shell 用于将表中数据记录导出　　                 导出为字段值用分隔符'|'分开的。dat文件　　    编者：　　    日期： 2006.03.18    　　if [ \)# -ne 3 ]

　　then echo "usage：unloadtable tablename username password."

　　exit 0

　　准备工作

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； /" >>/tmp/$1.sel    由于  /  导致多执行一次 select　　echo "from $1； " >>/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　　echosqlplus -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

2025年sqlldr 导入（sqlldr 导入数据有换行符）

相关推荐