只为实现功能，不求效率和可管理性等等。
代码如下：包含有注释和讲解
#!/bin/bash
. /home/testuser/.bash_profile
# 定义异常通知邮件组
TO_MAIL=” ”
# 判断是否已经在运行
test -f /home/testuser/script/run/target_table.run
# 退出代码和超时通知代码
if [ $? -eq 0 ]; then
SYNC_TIME_CHECK=”‘`ls -1 -l –time-style=+%F\ %T /home/testuser/script/run/target_table.run|awk ‘{print $6″ “$7}’`'”
psql -t -h /tmp -p 1921 -U testuser -d edb <<EOF 1>/home/testuser/script/target_table.timeoutstats 2>&1
select ’sync_time_out’ where now()-$SYNC_TIME_CHECK::timestamp without time zone>interval ‘1 hour’;
EOF
TIME_OUT_ERROR=0
TIME_OUT_ERROR=`grep -c “sync_time_out” /home/testuser/script/target_table.timeoutstats`
if [ $TIME_OUT_ERROR -ne 0 ]; then
echo -e “`cat /home/testuser/script/target_table.timeoutstats`\n\n`date +%F%T`\n sync target_table timeout!\n\nPlease Call Digoal!\n”|mutt -s “Sync Target Table timeout!” $TO_MAIL
echo -e “sync timeout”
fi
exit 3
fi
# 生成在运行标记
touch /home/testuser/script/run/target_table.run
# 获取最大ID
MAX_T=”‘`psql -t -h /tmp -p 1921 -U testuser -d edb -c “select max(id) from -e ’s/ //g’`'”
# 同步代码
psql -t -h /tmp -p 1921 -U testuser -d edb <<EOF 1>/home/testuser/script/target_table.stats 2>&1
begin;
select ’start sync: ‘||now();
delete from
where
id < $MAX_T ;
insert into
(col1,col2,col3,col4,col5,col6)
select
col1,col2,col3,col4,col5,col6
from
where
id > ${MAX_T}
;
insert into
(col1,col2,col3,col4,col5,col6)
select
col1,col2,col3,col4,col5,col6
from
where
id > ${MAX_T} ;
commit;
EOF
# 异常捕获发送,异常的话不删除/home/testuser/script/run/target_table.run,防止后续数据出错
ERROR=0
ERROR=`grep -c “ROLLBACK” /home/testuser/script/target_table.stats`
if [ $ERROR -ne 0 ]; then
echo -e “`cat /home/testuser/script/target_table.stats`\n\n`date +%F%T`\n sync target data error!\n\nPlease Call Digoal!\n”|mutt -s “Sync target data error!” $TO_MAIL
echo -e “sync error”
exit 2
fi
# 删除运行标记
sleep 10
rm -f /home/testuser/script/run/target_table.run
#代码完
几个问题补充:
从postgresql到oracle使用的EDB DBLINK，时间字段不能进行传输，甚至连default值都用不了,报ORA-01861错误
01861, 00000, “literal does not match format string”
// *Cause: Literals in the input must be the same length as literals in
// the format string (with the exception of leading whitespace). If the
// “FX” modifier has been toggled on, the literal must match exactly,
// with no extra whitespace.
// *Action: Correct the format string to match the literal.
解决办法是把时间改成char(14)直接写成字符。
还有一种折中的解决办法是在target端，也就是ORACLE端使用行触发器before for each row,生成一个sysdate。