分享一个使用get_hash_value比对数据脚本

使用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, unsupport

export 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 ]
then

sqlplus -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 $VERSION

if [ $VERSION -gt 11 ]
then
	TSQL=TSQL12C
else
	TSQL=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 file



mkdir -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/null

date
> $WORKDIR/tab.ok.$LOGFILE_OPERTIME
> $WORKDIR/tab.err.$LOGFILE_OPERTIME
> $WORKDIR/.c.done.bak
> $WORKDIR/.c.done
> $WORKDIR/.hash.sql.bak

printf "%-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 
do
	
	cat $WORKDIR/.c.done >>$WORKDIR/.c.done.bak  2>/dev/null
	cat $WORKDIR/.hash.sql >>$WORKDIR/.hash.sql.bak 2>/dev/null
	> $WORKDIR/.c.done
	
	$TSQL $TABLEINFO
	
	START_OPERTIME=`date +"%Y-%m-%d:%H:%M:%S"`
	SOURCE_COUNT $TABLEINFO &
	TARGET_COUNT $TABLEINFO &
	
#	ALL_ROW=`echo $TABLE_INFO|awk '{print NF}'`
	
	while true
	do
	if [ `cat $WORKDIR/.c.done |wc -l ` -eq 2 ]
	then
		SOURCE_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 ]
		then
			COMP_STATUS=2
			echo $TABLEINFO >> $WORKDIR/tab.err.$LOGFILE_OPERTIME
		else
			COMP_STATUS=1
			echo $TABLEINFO >> $WORKDIR/tab.ok.$LOGFILE_OPERTIME
		fi
	
		printf "%-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_OPERTIME
		break
	else
		sleep 0.1
	fi
	done
	ID=$(($ID+1))

done <$TABLE_FILE
date

脚本执行完结果如下：