因部分项目需求,需要将Oracle中单表的数据定期导入至MySQL,注意此处对数据的时效性要求不高。要求高可以利用goldengate
首先,需要将visit表中的vid,userid,goodid,time4个字段导出来
export ORACLE_HOME=/usr/lib/oracle/11.2/client64 export TNS_ADMIN=$ORACLE_HOME export PATH=$PATH:$ORACLE_HOME/bin export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib export NLS_LANG="AMERICAN_AMERICA.UTF8" MYDB=visit #MySQL数据库名 MYUSER=vis_my_user #MySQL用户名 MYPASS=is_my_pass #MySQL密码 ORA_NAME=visit #Oracle实例名 ORA_USER=visuser #Oracle用户名 ORA_PASS=vispass #Oracle密码 id=`mysql -u$MYUSER -p$MYPASS $MYDB -N -e "select max(vid) from visit;"` #MySQL -N参数可以忽略第一行输出 sqlplus -S ${ORA_USER}/${ORA_PASS}@${ORA_NAME} << EOF set echo off; set feedback off; set newpage none; set verify off; set pagesize 0; set term off; set trims on; set linesize 600; set heading off; set timing off; set numwidth 38; SPOOL /tmp/visit_result.txt; #导出结果位置,以下为查询语句 select vid || ',' || userid || ',' || goodsid || ',' || time from VISIT WHERE to_char(time,'yyyymmdd') >= to_char(SYSDATE-30,'yyyymmdd') AND vid > $id order by CREATETIME asc; SPOOL OFF; exit EOF mysql -u$MYUSER -p$MYPASS $MYDB -e "load data local infile '/tmp/visit_result.txt' into table visit FIELDS TERMINATED BY ',' (userid,goodsid,time,vid);" #此处为将Oracle数据导入MySQL
转载于:https://blog.51cto.com/fengwan/1892725