2.1 基本知识
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
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 可以将外部格式化的文本数据加载到数据库表中。通常 与 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数据库备份与恢复之六:LogMiner
2008-12-07 21:23 9676.1 LogMiner 的用途 Oracle ... -
Oracle数据库备份与恢复之五:Flashback
2008-12-07 21:22 931Flashback在开发环境(有 ... -
Oracle数据库备份与恢复之四:RMAN(备份与恢复管理器) 续
2008-12-07 21:17 16004.7.3 表空间时间点 ... -
Oracle数据库备份与恢复之四:RMAN(备份与恢复管理器)
2008-12-07 21:14 2660RMAN是ORACLE提供的一个备 ... -
Oracle数据库备份与恢复之三:OS备份/用户管理的备份与恢复
2008-12-07 21:09 1106用户管理的备份与恢复也称 OS物理备份,是指通过数据库命令设置 ... -
Oracle数据库备份与恢复之一:exp/imp(导出与导入装库与卸库)
2008-12-07 21:01 18251.1 基本命令 1. 获取帮助 $ ... -
如何对Oracle数据库文件进行恢复与备份
2008-12-07 20:55 1470任何数据库在长期 ...
相关推荐
Oracle数据库的备份与恢复 77 Oracle分区功能提高应用程序性能 79 Oracle数据库处理时间基本准则 81 入侵Oracle数据库常用操作命令 82 Oracle数据库优化及其应用程序研究 83 Instance实例和数据库 85 Oracle数据缓冲...
内容包括: 第一部分: Oracle体系架构 ...第十七章:逻辑备份与恢复 第十八章:物化视图。 由于文件过大,只提供百度网盘下载地址和提取码,请放心下载。 信誉第一,如有任何问题,可以给我发私信或者评论区留言
软件功能强大,持修复因各种原因造成的数据库无法打开或数据库删除后没有备份的问题,从而实现对Oracle数据库的抢修恢复,最大限度减少数据丢失。是用户实现Oracle数据库抢修恢复的好帮手。需要的朋友快来下载吧! ...
(六)Oracle 数据库备份的解决方案 89 二、闪回 90 (一)什么是闪回 Flashback? 90 (五)闪回查询:Flashback Query 91 (六)闪回版本查询:Flashback Version Query 92 (七)闪回表:Flashback Table 93 (八...
DataPump 、SqlLoader两种方式备份还原oracle数据库
第二部分 数据库管理 第5章 开发过程管理 97 5.1 成功三要素 97 5.2 培植过程 97 5.3 管理过程 98 5.3.1 定义环境 98 5.3.2 角色的定义 99 5.3.3 交付使用 100 5.3.4 Oracle8i中引入的开发环境特性 102 5.3.5 确定...
ORACLE__SQL语句教学 引言 3 ...第二十四章 备份 恢复 SQLLoader 104 第二十五章 数据库设计范式 106 第二十六章 数据库设计工具 107 第二十七章 对象关系数据库系统 112 第二十八章 其他数据库 113
2.3.2 Oracle8备份和恢复 2.4 Oracle8的对象和新数据库对象 2.4.1 嵌套对象 2.4.2 可变数组 2.4.3 类型中的类型 2.4.4 方法和类型 2.4.5 对象视图 2.5 小结 2.6 本章小结 第3章 结构 3.1 术语 3.2 为什么要学习结构 ...
Oracle数据库教程 数据库基础,Oracle入门,查询...PL SQL,游标、函数,存储过程,触发器,事务(数据库系统概论),用户管理,备份 回复SQLLoader,数据库设计范式,数据库设计工具,对象关系数据库系统,其他数据库
2.3.2 Oracle8备份和恢复 2.4 Oracle8的对象和新数据库对象 2.4.1 嵌套对象 2.4.2 可变数组 2.4.3 类型中的类型 2.4.4 方法和类型 2.4.5 对象视图 2.5 小结 2.6 本章小结 第3章 结构 3.1 术语 3.2 为什么要学习结构 ...
信永国际 中文超详细Oracle教程 第一章 数据库基础 ...第二十四章 备份 恢复 SQLLoader 第二十五章 数据库设计范式 第二十六章 数据库设计工具 第二十七章 对象关系数据库系统 第二十八章 其他数据库
详细的教程, 第一章 数据库基础 第二章 Oracle 入门 ...第二十四章 备份 恢复 SQLLoader 第二十五章 数据库设计范式 第二十六章 数据库设计工具 第二十七章 对象关系数据库系统 第二十八章 其他数据库
第一章 数据库基础 第二章 Oracle入门 第三章 查询基础 ...第二十四章 备份 恢复 SQLLoader 第二十五章 数据库设计范式 第二十六章 数据库设计工具 第二十七章 对象关系数据库系统 第二十八章 其他数据库
第一章 数据库基础 第二章 Oracle入门 第三章 查询基础 ...第二十四章 备份 恢复 SQLLoader 第二十五章 数据库设计范式 第二十六章 数据库设计工具 第二十七章 对象关系数据库系统 第二十八章 其他数据库
Bob Bryla是Oracle 9i和10g的认证专家,他在数据库设计、数据库应用程序开发、培训和Oracle数据库管理等方面拥有20多年的工作经验,他也足Dodgeville的Land'End公司的首席Internet数据库设计师和Oracle DBA. ...
详细的教程, 第一章 数据库基础 第二章 Oracle 入门 ...第二十四章 备份 恢复 SQLLoader 第二十五章 数据库设计范式 第二十六章 数据库设计工具 第二十七章 对象关系数据库系统 第二十八章 其他数据库
§1.1 Oracle数据库结构 23 §1.1.1 Oracle数据字典 23 §1.1.2 表空间与数据文件 24 §1.1.3 Oracle实例(Instance) 24 §1.2 Oracle文件 26 §1.2.1 数据文件 26 §1.2.2 控制文件 26 §1.2.3 重做日志文件 26 §...
3.1 了解oracle数据库管理工具 3.2 规划oracle数据库的安装 3.3 使用oui安装oracle软件 3.4 小结 3.5 本章测试题 .第4章 创建oracle数据库 4.1 使用database configuration assistant创建数据库 4.2 ...