批处理sqlldr数据迁移--oracletxt导入导出（转）

批处理sqlldr数据迁移--oracletxt导⼊导出（转）
sqlldr数据迁移--oracle txt导⼊导出
⼀、sqlldr导⼊txt
1.预备
a).txt⽂件
这⾥要保存成⽆签名的UTF-8
b).oracle建表
2.编写控制⽂件input_test.ctl
LOAD DATA
CHARACTERSET 'UTF8' --字符集设定
INFILE 'd:\input_test.txt' --要导⼊的⽂本数据路径,可写多个
REPLACE into TABLE input_test --清空原有数据再导⼊⽅式追加导⼊⽤append into table t_name
fields terminated by X'09' --以制表符分隔
trailing nullcols --允许空列导⼊
(col1,col2)
注:
infile 'd:\input_test.txt'表⽰需要装载的数据⽂件的路径
append into table test 数据载⼊的表:
(1)append 表⽰表中有数据，加在后⾯
(2)INSERT 表⽰装⼊空表，有数据则停⽌。

默认值
(3)REPLACE 原先表中如果有数据，会被删除
(4)TRUNCATE 如果要载⼊的数据与现在的数据相同，载⼊的数据替换现存的数据。

fields terminated by ',‘
表⽰数据⽤是','分隔的,⽤by X'09',即16进制的"09"代表TAB制表符,常⽤于excel转换的tab制表符⽂件的数据的导⼊。

常⽤分隔符还有'|'
多语种可设置字符集编码为:CHARACTERSET 'UTF8'
3.DOS下执⾏
sqlldr system/psw@db, control=c:\input\input_test.ctl log=c:\input\input_test.log bad=c:\input\input_test.bad
有效的关键字:
userid -- ORACLE username/password
control – 控制⽂件
log – 记录的⽇志⽂件
bad – 坏数据⽂件
data – 数据⽂件
discard – 丢弃的数据⽂件
discardmax – 允许丢弃数据的最⼤值 (全部默认)
skip -- Number of logical records to skip (默认0)
load -- Number of logical records to load (全部默认)
errors – 允许的错误记录数 (默认50)
rows -- Number of rows in conventional path bind array or between direct path data saves（每次提交的记录数，默认: 常规路径 64, 所有直接路径）
bindsize -- Size of conventional path bind array in bytes(默认256000)
每次提交记录的缓冲区的⼤⼩(字节为单位，默认256000)
silent --禁⽌输出信息 (header,feedback,errors,discards,partitions)
direct – 使⽤直通路径⽅式导⼊ (默认FALSE)
parfile -- parameter file: name of file that contains parameter specifications
parallel -- 并⾏导⼊ (默认FALSE)
file -- File to allocate extents from
skip_unusable_indexes -- disallow/allow unusable indexes or index partitions(默认FALSE)
skip_index_maintenance -- do not maintain indexes, mark affected indexes as unusable(默认FALSE)
readsize -- Size of Read buffer (默认1048576)
与bindsize成对使⽤，其中较⼩者会⾃动调整到较⼤者。

sqlldr先计算单条记录长度，乘以rows，如⼩于bindsize，不会试图扩张rows以填充bindsize；如超出，则以bindsize为准。

external_table -- use external table for load; NOT_USED, GENERATE_ONLY, EXECUTE(默认NOT_USED)
columnarrayrows -- Number of rows for direct path column array(默认5000)
streamsize -- Size of direct path stream buffer in bytes(默认256000)
multithreading -- use multithreading in direct path
resumable -- enable or disable resumable for current session(默认FALSE)
resumable_name -- text string to help identify resumable statement
resumable_timeout -- wait time (in seconds) for RESUMABLE(默认7200)
date_cache -- size (in entries) of date conversion cache(默认1000)
4.写成.bat批处理
上述3步已完成了txt导⼊,在windows下还可将sqlldr命令写成批处理⽂件,双击执⾏.
@echo off
echo input_test
pause --暂停,建议加⼊，以免错误双击执⾏
@rem
sqlldr system/psw@db, control=c:\input\input_test.ctl log=c:\input\input_test.log bad=c:\input\input_test.bad
@rem
@rem sqlldr system/psw@db, control=c:\input\input_test.ctl rows=100000
pause
⼆、sqlldr导出txt
利⽤spool 导出txt
1.写output.sql
sqlplus system/psw@db as sysdba --连接oracle
CHARACTERSET al32UTF8 --设置编码集
set trimspool on --打开池
spool c:\output\output.txt --池输出路径
set pagesize 0 --页设置
set heading off --关闭表头
set linesize 32767 --最⼤⾏显
select '#'||col1||'#,#'||col2||'#,#'||col3||'#‘ --设置需要的列格式。

此例，列间以以逗号分隔，列内容⽤#引起。

from test_data;
exit; --退出sqlplus
spool off --关闭池
pause
注：上述命令在dos下直接敲命令执⾏亦可。

2.写成.bat批处理
再写bat调⽤上⾯的outputsql⽂件，如下：
cd/
set NLS_LANG=.AL32UTF8 --设置字符集utf8
chcp 65001 --转换编码页utf8
@echo off
echo data output
pause
@rem
sqlplus system/psw@db as sysdba @c:\dmp_sql\output.sql @rem
pause。