Oracle设置指定分隔符对数据进行导入与导出

oracle导出数据:

sqlplus -s zhangsan/123456@12.34.56.78/scott<<EOF
set echo off; 
set wrap off; 
set feedback off; 
set heading off; 
set pagesize 0;
set linesize 4096; 
set numwidth 50; 
set verify off;
set term off;
set trimspool on;
spool /home/user/123.txt
select column1 ||'|'||,column2 ||'|'||,column3 ||'|'|| from emp ;
EOF

Oracle导入数据

导入数据来借助sqlldr工具来实现,其中control为一个控制文件,log为日志文件,bad为脏数据,discard为拒绝文件。

sqlldr zhangsan/123456@12.34.56.78/scott
control=/home/user/tmp/1236973-20200316164512.ctl 
log=/home/user/tmp/1236973-20200316164512.log 
bad=/home/user/tmp/1236973-20200316164512.bad 
discard=/home/user/tmp/1236973-20200316164512.dis 
direct=false parallel=false errors=1000 bindsize=10000000 readsize=10000000 rows= 100000.

以下为ctl文件的具体内容

/home/user/tmp/1236973-20200316164512.ctl
LOAD DATA 
INFILE "/home/user/123.txt"
TRUNCATE INTO TABLE emp
FIELDS
TERMINATED BY '|'
TRAILING NULLCOLS
(
column1 char(20),
column char(20),
column char(20)
)
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页