分享一个使用get

分享一个使用get

使用get_hash_value获取每个字段的值，再sum起来比对，如果表有lob字段，则会先排除掉lob字段再比对其它字段

这个脚本有两个问题：

1.如果字段所有的值长度加起来超过4000会报错，比对不了，这种情况一般比较少

2.二进制数据不支持，例如blob

脚本需要修改：

1.DS_CONNECT 和DT_CONNECT配置为需要比对的源端和目标端的连接串

2.COMP_TYPE如果是1则是比对用户，你将需要比对的用户写到COMP_USER中

如果是2则是比对指定表，你需要手工将 用户名.表名 写入到tab.txt中

3.FLAG配置一下比对的标识符，随便写，标识比对的哪个库

4.配置一下NLS_LANG防止有中文表或者中文字段

其它：

比对的结果会在当前目录下，tab.info.xxx是详细结果  tab.err.xxx是比对不一致的表 tab.ok.xxx是比对一致的表，上次比对的结果保存在history目录下

如果比对过程中出现报错，可以检查.c.done和.c.done.bak这两个个隐藏文件

.hash.sql和.hash.sql.bak是具体执行的sql语句

#!/bin/bash
. ~/.bash_profile##### ERROR:: integer expression expected : table no columns without unsupport datatype columns
##### ERROR:: ORA-01489                   : column values concat larger then 40000, unsupportexport WORKDIR=$(cd `dirname $0`/; pwd)                 ##modify
##USER and PWD
export FLAG=wzjj_lob
export DS_CONNECT=xx/xxxx@source
export DT_CONNECT=xx/xxxx@target
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK###COMP_TYPE:1-user 2-table
###if COMP_TYPE=1,please modify COMP_USER
###if COMP_TYPE=2,plase write OWNER.TABLE_NAME to $TABLE_FILE
export COMP_TYPE=1
export COMP_USER="('BUSINESS_QRY','TEST')"
export TABLE_FILE=$WORKDIR/tab.txt####init tab.txt
if [ $COMP_TYPE -eq 1 ]
thensqlplus -S $DS_CONNECT <<EOF >$WORKDIR/tab.txt
set pagesize 0 feedback off verify off heading off echo off long 9999 longchunksize 9999 line 5000
select owner||'.'||table_name from dba_tables where owner in $COMP_USER
/
EOF
fi########
LOGFILE_OPERTIME=`date +%Y%m%d%H%M`ALL_ROW=`cat $TABLE_FILE |wc -l`####check oracle version
VERSION=`sqlplus -S $DS_CONNECT <<EOF
set pagesize 0 feedback off verify off heading off echo off long 9999 longchunksize 9999 line 5000
select substr(value,1,instr(value,'.')-1) from  v\\\$parameter where name='compatible'
/
EOF
`
#echo $VERSIONif [ $VERSION -gt 11 ]
thenTSQL=TSQL12C
elseTSQL=TSQL11G
fi#####################################################################################TSQL11G(){
EXEC_SQL=`sqlplus -S $DS_CONNECT <<EOF
set pagesize 0 feedback off verify off heading off echo off long 9999 longchunksize 9999 line 5000
select 'select nvl(sum(dbms_utility.get_hash_value('||replace(to_char(
wm_concat(
decode(data_type,'LONG','','LONG RAW','','BLOB','','CLOB','','NCLOB','''0''','"'||COLUMN_NAME||'"')
)),',','||')
||',0,power(2,30))),0) from "'||owner||'"."'||table_name||'";'
from dba_tab_columns where owner||'.'||table_name='$1'  group by owner,table_name
/
EOF
`
echo "$EXEC_SQL" >$WORKDIR/.hash.sql
}TSQL12C(){
EXEC_SQL=`sqlplus -S $DS_CONNECT <<EOF
set pagesize 0 feedback off verify off heading off echo off long 9999 longchunksize 9999 line 5000
select 'select nvl(sum(dbms_utility.get_hash_value('||to_char(listagg(
decode(data_type,'LONG','','LONG RAW','','BLOB','','CLOB','','NCLOB','''0''','"'||COLUMN_NAME||'"')
,'||'))
||',0,power(2,30))),0) from "'||owner||'"."'||table_name||'";'
from dba_tab_columns where owner||'.'||table_name='$1'  group by owner,table_name
/
EOF
`
echo "$EXEC_SQL" >$WORKDIR/.hash.sql
}SOURCE_COUNT(){
SOURCE_NUM=`sqlplus -S $DS_CONNECT <<EOF
set pagesize 0 feedback off verify off heading off echo off numf 999999999999999999999999999999999
@$WORKDIR/.hash.sql
EOF
`
echo s $SOURCE_NUM >> $WORKDIR/.c.done
}TARGET_COUNT(){
TARGET_NUM=`sqlplus -S $DT_CONNECT <<EOF
set pagesize 0 feedback off verify off heading off echo off numf 9999999999999999999999999999999999
@$WORKDIR/.hash.sql
EOF
`
echo t $TARGET_NUM >> $WORKDIR/.c.done
}#######################################################################################################
##init file & backup filemkdir -p $WORKDIR/history
mv -f $WORKDIR/tab.info* $WORKDIR/history 2>/dev/null
mv -f $WORKDIR/tab.err* $WORKDIR/history 2>/dev/null
mv -f $WORKDIR/tab.ok* $WORKDIR/history 2>/dev/nulldate
> $WORKDIR/tab.ok.$LOGFILE_OPERTIME
> $WORKDIR/tab.err.$LOGFILE_OPERTIME
> $WORKDIR/.c.done.bak
> $WORKDIR/.c.done
> $WORKDIR/.hash.sql.bakprintf "%-12s %-10s %-40s %-18s %-18s %-6s %-20s %-20s\n"  "ID" "FLAG" "OWNER.TABLE" "DS_HASH"  "DT_HASH" "STATUS" "START_TIME" "END_TIME" |tee -a $WORKDIR/tab.info.$LOGFILE_OPERTIME
echo "------------------------------------------------------------------------------------------------------------------------------------------------------------" |tee -a $WORKDIR/tab.info.$LOGFILE_OPERTIME
ID=1##########################################################for 
while read TABLEINFO 
docat $WORKDIR/.c.done >>$WORKDIR/.c.done.bak  2>/dev/nullcat $WORKDIR/.hash.sql >>$WORKDIR/.hash.sql.bak 2>/dev/null> $WORKDIR/.c.done$TSQL $TABLEINFOSTART_OPERTIME=`date +"%Y-%m-%d:%H:%M:%S"`SOURCE_COUNT $TABLEINFO &TARGET_COUNT $TABLEINFO &#	ALL_ROW=`echo $TABLE_INFO|awk '{print NF}'`while truedoif [ `cat $WORKDIR/.c.done |wc -l ` -eq 2 ]thenSOURCE_NUM=`cat $WORKDIR/.c.done |awk '$1=="s" {print $2}'`TARGET_NUM=`cat $WORKDIR/.c.done |awk '$1=="t" {print $2}'`END_OPERTIME=`date +"%Y-%m-%d:%H:%M:%S"`if [ $SOURCE_NUM -ne $TARGET_NUM ]thenCOMP_STATUS=2echo $TABLEINFO >> $WORKDIR/tab.err.$LOGFILE_OPERTIMEelseCOMP_STATUS=1echo $TABLEINFO >> $WORKDIR/tab.ok.$LOGFILE_OPERTIMEfiprintf "%-12s %-10s %-40s %-18s %-18s %-6s %-20s %-20s\n" [$ID/$ALL_ROW] $FLAG $TABLEINFO $SOURCE_NUM $TARGET_NUM $COMP_STATUS $START_OPERTIME $END_OPERTIME |tee -a $WORKDIR/tab.info.$LOGFILE_OPERTIMEbreakelsesleep 0.1fidoneID=$(($ID+1))done <$TABLE_FILE
date

脚本执行完结果如下：