操作环境
客户端 Oracle 10g
服务端 Oracle 11g
1、数据准备
1.1、在sqlplus下将相关表将相关待导入数据导出为txt文件
--sqlplus脚本
Set linesize 3000 --每行长3000
Set pagesize 0 --指定不分页
Set heading off --不输出标题行
Set feedback off --不输出反馈信息
Set echo off --不显示命令本身
Set termout off --不显示终端信息
Set trimout On --截断终端显示空格
Set trimspool On --截断输出至日志空格
spool c:\a.txt --指定屏幕输出到相关文件
spool off --关闭指定输出
以上脚本控制sqlplus控制台输出打印效果, 同时指定屏幕的打印输出将转录入到何文件中
实际脚本
sqlplus zhsj/zhsj@zhsj
Set linesize 3000
Set pagesize 0
Set heading off
Set feedback off
Set echo off
Set termout off
Set trimout On
Set trimspool On
spool c:\a.txt
select employee_id||'|'||employee_name||'|'||employee_salary from t_employee;
spool off
以上会将t_employee表中的 employee_id、employee_name、employee_salary 三列数据以如下形式写入c:\a.txt 文件中
1|znp|80000.00
2|zhsj|40000.00
3|xxx|30000.00
数据准备完成后编写sqlldr要用到的ctl文件，用于将txt文件中的数据导入到数据库中，脚本如下 （--部分实际脚本中须删险)
-- a.ctl
Load data --加载数据
infile a.txt --加载数据文件名称(现在是相对路径,可以写成绝对路径c:\bak.txt)
badfile a.bad --失败数据写入文件
append -- append 追加 truncate truncate delete 删除
into table t_employee_bak --插入操作表
FIELDS TERMINATED BY '|' --根据指定标识符隔断各字段值 OPTIONALLY ENCLOSED BY '"' 过滤掉指定的标识符,如"等
(employee_id, --指定插入字段及数据
employee_name,
employee_salary)
准备完成后，在cmd下输入
sqlldr -userid zhsj/zhsj@zhsj control=c:\a.ctl
执行，在t_employee_bak表中可以见到a.txt中的数据已导入
在执行过程如果出现数据没有导入的情况，可能原因会有以下几种情况
1、在导出的数据文件中存在同时打印输出的命令行，将命令行删除后重新保存文件即可将数据正常插入到数据库中
2、虽然文本中已是正确的数据但仍然无法导入,此时在执行的SQL脚本中增加一个间隔符的输出，即
select employee_id||'|'||employee_name||'|'||employee_salary from t_employee;
改为
select employee_id||'|'||employee_name||'|'||employee_salary||'|' from t_employee;
此时将会正常导入，这是因为列值存在空数据导致间隔计算问题
以上为txt格式数据文件导出
对于异质数据库，可以采用sqldeveloper等工具将相关数据库中数据以csv格式导出，此时在编写执行脚本时须变更如下
-- a.ctl
Load data --加载数据
infile a.txt --加载数据文件名称(现在是相对路径,可以写成绝对路径c:\bak.txt)
badfile a.bad --失败数据写入文件
append -- append 追加 truncate truncate delete 删除
into table t_employee_bak --插入操作表
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' --根据指定标识符隔断各字段值 OPTIONALLY ENCLOSED BY '"' 过滤掉指定的标识符,如"等
(employee_id, --指定插入字段及数据
employee_name,
employee_salary)
因为csv格式文件在用txt打开可以看到数据文件格式为"1","znp","80000.00"，需要将' " '标识符去掉，才可以正常导入
大数据量时csv格式文件会比txt格式文件大3倍左右，且导入时可能出现问题，所以尽量采用txt文件方式导入