Oracle笔记十七：导入导出

1、导入

1.1、sqlldr

1.1.1、sqlldr介绍

1.1.1.1、sqlldr用法

命令：sqlldr keyword=value keyword1=value1 keyword2=value2 …
常用关键字：

• userid : ORACLE username/password@tnsname
• control : 控制文件
• log : 记录的日志文件
• bad : 坏数据文件，记录错误的未加载数据
• data : 数据文件，data参数只能指定一个数据文件,如果控制文件也通过infile指定了数据文件,并且指定多个,则sqlldr在执行时,先加载data参数指定的数据文件,控制文件中第一个infile指定的数据文件被忽略, 但后续的infile指定的数据文件继续有效
• discard : 丢弃的数据文件,默认情况不产生,必须指定
• discardmax : 允许丢弃数据的最大值 (默认全部)
• skip : (默认0)，跳过记录数,从数据文件中,从第一行开始要计算要跳过的行数,*,对于多表加载的情况,如果有when条件 判断的话，或者直接路径下的多表加载，如果要加载的记录数不同，则该参数无效
• load : Number of logical records to load (默认全部)
• errors : 允许的错误记录数,超过则终止任务 (默认50)
• readsize : 缓冲区大小,默认值:1048576单位字节,最大不超过20m,该参数仅当从数据文件读取时有效
• bindsize : 每次提交记录的缓冲区的大小，字节为单位，默认256000
• rows : 常规路径导入时：指绑定数组中的行数；直接路径导入时：指一次从数据文件只读取的行数；参数同时受bindsize制约,如果rows*每行实际占用大小超出bindsize最大可用值，则rows自动降低达到bindsize最大可用值（每次提交的记录数，默认: 常规路径 64, 直接路径：所有）
• silent : 禁止输出信息
• direct : 使用直通路径方式导入,不走buffer cache,通过direct path api发送数据到服务器端的加载引擎，加载引擎按照数据块的格式处理数据并直接写向数据文件,因此效率较高(默认FALSE)
• parallel : 并行导入，仅在直接路径加载时有效(默认FALSE)
• file : 并行加载时会用到该参数,指定file参数,要加载的内容即只向指定的数据文件写入数据，减少i/o
• multithreading: 是否启用多线程，多cpu为true,单cpu false,直接路径加载时有效

1.1.1.2、传统路径与直接路径模式

传统路径（conventional path）:
　　SQLLDR会利用SQL插入为我们加载数据。
直接路径（direct path）:
　　采用这种模式，SQLLDR不使用SQL；而是直接格式化数据库块，而绕过整个SQL引擎和UNDO生成，同时还可能避开REDO生成。要在一个没有任何数据的库中充分加载数据，最快的方法就是采用并行直接路径加载

1.1.1.3、传统方式写入和直接路径写入

• 传统方式写入:
  • Oracle会重用表里面空闲空间，并且写入会先写入到buffer cache。
• 直接路径写入：
  • Oracle不重用表里面的空闲空间，直接写入到新分配的块，并且数据直接写入到data file，不写入到buffer cache。效率更高。如果表的并行度不为1，那么直接路径写入是默认的写入行为（需要开启会话并行dml）。直接路径写入为元数据（例如空间扩展引起的数据字典数据变化）变更产生REDO, UNDO。而数据变更，redo，undo生成情况如下：
    • 不为数据写入生成undo
    • 如果数据库为非归档或者没有开启force logging，那么直接路径写入时，不为数据写入产生redo，这时与表是否设置nologging属性无关。
    • 如果数据库为归档，但是没有开启force logging，logging的表会产生redo，nologging的表不会产生redo。
    • 如果数据库为归档并且开启了force logging，数据写入会产生redo，无论是否设置logging，nologging。
• APPEND,PARALLE和直接路径写入关系：
  • Append默认也是采用直接路径写入，不要求session enable parallel。
  • Parallel 要求会话级别enable parallel，否则将无法进行直接路径写入。

1.1.2、sqlldr ctl文件

1.1.2.1、sqlldr ctl语法

##OPTIONS内容同sqlldr的关键字
OPTIONS ( { [SKIP=integer] [ LOAD = integer ]
[ERRORS = integer] [ROWS=integer]
[BINDSIZE=integer] [SILENT=(ALL|FEEDBACK|ERROR|DISCARD) ] )

LOAD[DATA]
##INFILE 和INDDN是同义词，它们后面都是要加载的数据文件。如果用 * 则表示数据就在控制文件内。在INFILE 后可以跟几个文件
[ { INFILE | INDDN } {file | * }
STREAM 表示一次读一个字节的数据。新行代表新物理记录（逻辑记录可由几个物理记录组成）
RECORD 使用宿主操作系统文件及记录管理系统。如果数据在控制文件中则使用这种方法
FIXED length 要读的记录长度为length字节
[STREAM | RECORD | FIXED length [BLOCKSIZE size]|
VARIABLE [length] ]
[ { BADFILE | BADDN } file ]
{DISCARDS | DISCARDMAX} integr ]
insert–为缺省方式，在数据装载开始时要求表为空
append–在表中追加新记录
replace–删除旧记录(用 delete from table 语句)，替换成新装载的记录
truncate–删除旧记录(用 truncate table 语句)，替换成新装载的记录
[APPEND | REPLACE | INSERT | TRUNCATE]
INTO TABLE [user.]table
[WHEN condition [AND condition]…]
数据中每行记录用 “,” 分隔
[Fields terminated by “,” ]
数据中每个字段用 ‘"’ 框起，比如字段中有 “,” 分隔符时
[Optionally enclosed by ‘"’]
表的字段没有对应的值时允许为空
[trailing nullcols]
(
column {
RECNUM | CONSTANT value |
SEQUENCE ( { integer | MAX |COUNT} [, increment] ) |
[POSITION ( { start [end] | * [ + integer] }
) ]
datatype
[TERMINATED [ BY ] {WHITESPACE| [X] ‘character’ } ]
[ [OPTIONALLY] ENCLOSE[BY] [X]‘charcter’]
[NULLIF condition ]
[DEFAULTIF condotion]
}
[ ,…]
)
[INTO TABLE…]
[BEGINDATA]

注意

• 使用定位域而不要使用分隔域，分隔域要求装载器搜索数据以查找分隔符。定位域比较快，因为装载器只需要做简单的指针运算

1.1.3、实例

1.1.3.1、实例一：传统模式

sqlldr user/password@dbname
control=/home/oracle/data/install.ctl
log=/home/oracle/data/export.log
bad=/home/oracle/data/export.bad
rows=10000
readsize=20680000
bindsize=20680000

1.1.3.2、实例二：直接模式

采用此模式：不可有序列以及索引。
sqlldr user/password@dbname
control=/home/oracle/data/export.ctl
log=/home/oracle/data/export.log
bad=/home/oracle/data/export.bad
silent=header,feedback
direct=true

并行
sqlldr user/password@dbname
control=/home/oracle/APS_LOAD/ctl/AP_CONTRACT.CTL
direct=true
parallel=true
LOG=/home/oracle/APS_LOAD/log/KaTeX parse error: Expected group after '_' at position 22: …day/AP_CONTRACT_̲yesterday.log
bad=/home/oracle/APS_LOAD/bad/DUE_BILL_$yesterday.bad
rows=10000
readsize=20000000
bindsize=20000000
DISCARD=/home/oracle/APS_LOAD/bad/discard_ts.dis

1.1.3.3、实例三：控制文件

load data　　　　　　　　　　　　--控制文件标识
infile ‘d:/pr.csv’ “str X’0A’”　　　　　--需要导入的数据文件，换行符作为结束"str X’0A’“中0A的生成方式：select utl_raw.cast_to_raw(chr(13)||chr(10)) from dual;
into table pr_tmp 　　　　　　　　--加载到表
fields terminated by “,” 　　　　　　--以“ , ”分隔
OPTIONALLY ENCLOSED BY '”’ 　--表示""之间的是一个字段
TRAILING NULLCOLS　　　　　　--指定空的单元格用null填充
(ID char(256) , 　　　　　　　　　　--修改对字段长度的限制，默认是255
MID char(256),
KS char(512),
DES char(4000)
);

1.1.3.4、实例四：控制文件

load data --控制文件标识
infile ‘d:/pr.csv’ --需要导入的数据文件
into table pr_tmp --加载到表
FIELDS TERMINATED BY WHITESPACE
WHITESPACE（包括空格、Tab、换行符、换页符及回车符）FIELDS TERMINATED BY x’09’（分割符号为tab）
TRAILING NULLCOLS --指定空的单元格用null填充
(ID ,
T_ID FILLER, – FILLER 关键字 此列的数值不会被装载
T_DATE_1 “CASE WHEN :T_DATE_1 is null THEN TO_DATE(‘2999-12-31’,‘yyyy-mm-dd’) END”,
–函数判断
T_DATE date ‘yyyy-mm-dd’, --日期类型特别说明，并且要指定其格式
T_NAME POSITION(3:6) “UPPER(:T_NAME)”, --截位，并转换为大写
T_SEX position(*:8) , --该字段的开始位置在前一字段的结束位置
ENTIRE_LINE “UPPER(:T_NAME||:T_SEX)”, --拼接并转换为大写
TS “sysdate”, --获取系统时间
Datanum sequence(max,1) --(max大小写不区分) ，sqlldr将自动找到列中的最大值
Linenum RECNUM --载入每行的行号
);

1.1.3.5、实例五：控制文件

OPTIONS (skip=1,rows=128) – sqlldr 命令显示的 选项可以写到这里边来,skip=1 用来跳过数据中的第一行
LOAD DATA
INFILE * – 因为数据同控制文件在一 起，所以用 * 表示
append – 这里用 了 append 来操作，在表 users 中附加记录
INTO TABLE users
when LOGIN_TIMES<>‘8’ – 还可以用 when 子句选择导入符合条件的记录
Fields terminated by “,”
trailing nullcols
(
virtual_column FILLER, --跳过 由 PL/SQL Developer 生成的第一列序号
user_id “user_seq.nextval”, --这一列直接取序列的下一值，而不用数据中提供的值
user_name “'Hi '||upper(:user_name)”,–,还能用SQL函数或运算对数据进行加工处理
login_times terminated by “,”, NULLIF(login_times=‘NULL’) --可为列单独指定分隔符
last_login DATE “YYYY-MM-DD HH24:MI:SS” NULLIF (last_login=“NULL”) – 当字段为"NULL"时就是 NULL
)
BEGINDATA --数据从这里开始
,USER_ID,USER_NAME,LOGIN_TIMES,LAST_LOGIN
1,1,Unmi,3,2009-1-5 20:34
2,2,Fantasia,5,2008-10-15
3,3,隔叶黄 莺,8,2009-1-2
4,4,Kypf