oracle使用utl_file和sqlloard实现A表数据迁移至B表数据(字段数和顺序不一样)
数据库版本:11.2.0.4;系统版本:CentOS Linux 6.8
使用utl_file方式从A表导出部分字段至文本,再使用sqlload方式加载进B表;
表test字段: tid,tname,tphone,taddr,tlog
表test_t字段: tid,tphone,tname
--创建directory
$ sqlplus / as sysdba
SQL> create directory UTL_DATA as '/data';
SQL> grant read,write on directory utl_data to test;
注:因11g以后使用directory替换utl_file授权test用户可执行utl_file
SQL> grant execute on utl_file to test;
$ sqlplus test/123
SQL> declare
v_filehandle UTL_FILE.FILE_TYPE;
begin
v_filehandle:=utl_file.fopen('UTL_DATA','output_test.txt','w');
utl_file.putf(v_filehandle,'---export data from table test:', systimestamp);
utl_file.new_line(v_filehandle);
for i in (select * from test.test where rownum <= 100) loop
utl_file.putf(v_filehandle,'%s,%s,%s\n',i.tid,i.tphone,i.tname);
end loop;
utl_file.fclose(v_filehandle);
end;
/
PL/SQL procedure successfully completed.
使用sqlloard导入目标表test_t
--创建控制文件
# vi /data/test.txt
test.txt内容如下:
LOAD DATA
INFILE '/data/output_test.txt'
INTO TABLE test_t
TRUNCATE
fields terminated by ','
trailing nullcols
(tid,tphone,tname)
SQL> sqlldr test/123 control=/data/test.txt log=/data/log.txt bad=/data/log_bad.txt data=/data/output_test.txt
注:control=指定控制文件和路径 log=导入日志文件保存 bad=错误信息 data=数据文件