oracle 导出到别的机器,oracle 使用 copy方式，从别机器的库里面查某个表然后插到自己数据库里面...

本是古典何须时尚 2022-10-21 11:41 8阅读 0赞

背景：领导要求我从一个什么sk的库里面将发票上传的数据给拿到ds这边的库里面，下面话不多说，直接上脚本了

/30 \* cd /home/oracle/logstail/report; sh zengzhishui\_fapiao\_tongji.sh>/home/oracle/logstail/report/zengzhishui\_fapiao\_tongji.log

在这里，因为某些环境的特殊情况，最好将他重定向到个文件，不然有时候文件只给读到15行，不懂为啥，这个是税控数据的信息skuser/Sk12\[email protected\]:1588/nskxt

vim zengshishui\_fapiao\_tongji.sh

. ~/.bash\_profile

sqlplus -S /nolog <

\--insert zzs current date

conn skuser/Sk12\[email protected\]:1588/nskxt

copy to \[email protected\]:1521/wssb insert zzs\_fpscmx using select ‘增值税专票上传数量‘ as ZBMC ,count() as TJ, sysdate - (30/1440) as RPT\_TIME from cb\_fp\_zzszp where jssj > sysdate - (30/1440) union all select ‘增值税普票上传数量‘ as ZBMC,count() as TJ, sysdate - (30/1440) from cb\_fp\_zzspp where jssj > sysdate - (30/1440) union all select ‘电子发票上传数量‘ as ZBMC,count() as TJ, sysdate - (30/1440) from cb\_fp\_dz where jssj > sysdate - (30/1440) union all select ‘卷式发票上传数量‘ as ZBMC,count() as TJ, sysdate - (30/1440) from cb\_fp\_js where jssj > sysdate - (30/1440) union all select ‘机动车票上传数量‘ as ZBMC,count() as TJ, sysdate - (30/1440) from cb\_fp\_jdc where jssj > sysdate - (30/1440) union all select ‘二手车发票上传数量‘ as ZBMC,count() as TJ, sysdate - (30/1440) from cb\_fp\_esc where jssj > sysdate - (30/1440);

commit;

exit;

EOF

![e3ddd914c10c3aba799fdae7d8b1a0fd.png][]

记住了，脚本上 sql一定要紧凑，环境变量别错，个人建议，就是在用oracle 的这个 copy 命令的时候，最好，先用 他那个 create 根据具体查到的创个表，然后 再 insert 进去，省的麻烦看字段报错了

原文：https://blog.51cto.com/12092988/2545543

[e3ddd914c10c3aba799fdae7d8b1a0fd.png]: /images/20221021/b0c732de9843458196745010aa1592b5.png