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

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

/30 * cd /home/oracle/logstail/report; sh zengzhishui_fapiao_tongji.sh>/home/oracle/logstail/report/zengzhishui_fapiao_tongji.log

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

vim zengshishui_fapiao_tongji.sh

. ~/.bash_profile

sqlplus -S /nolog <

--insert zzs current date

conn skuser/Sk1217@87.12.74.179:1588/nskxt

copy to hlwsb_sjfx/hlwsb_sjfx@87.12.74.145: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

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

来源：51CTO

作者：老周21

链接：https://blog.51cto.com/12092988/2545543