脚本写的比较简单,使用minus比较用户下的表数据,第一个脚本是比对非LOB字段的,第二个脚本比对有LOB字段的表。
基本原理就是
select * from xxxx@xx
minu
select * from xxxxx;
这样的语句。其中比对LOB字段使用dbms_lob.getlength获取lob字段的长度进行比对。
首先创建一个dblink,这里就不说了
第一个比对不含LOB的表:
sqlplus ggs/ggs <<EOF
set feedback off pagesize 0 heading off verify off linesize 600 trimspool on
spool tab.sql
col a for a20
col b for a30
select 'select '||''''||owner||''' A,'||''''|| table_name||''' B'||',t.* from '||owner||'.'||table_name||'@dbverify t' ||' minus '||'select '||''''||owner||''' A,'||''''|| table_name||''' B'||',t.* from '||owner||'.'||table_name ||' t;' from dba_tables t where owner in ('TEST') and table_name not in (select table_name from dba_lobs where owner='TEST') and table_name not in (select table_name from long_type);
spool off;
!grep -v 'SQL>' tab.sql >tab.txt
!sed 's/[ ]*$//g' tab.txt >tab.sql
!rm -rf tab.txt
spool minus_res.txt
@tab.sql
spool off
EOF
exit
第二个比对含有LOB字段的表:
sqlplus ggs/ggs <<EOF
set line 4000
set pages 0
set heading off
set echo off
set long 99999
spool lob1.sql
select 'select '||column_name_path ||
' from ' || owner || '.' || table_name || '@dbverify minus '||'select '||column_name_path ||
' from ' || owner || '.' || table_name || ';'
from (select owner,
table_name,
column_name_path,
row_number() over(partition by table_name order by table_name, curr_level desc) column_name_path_rank
from (select owner,
table_name,
cnm,
rank,
level as curr_level,
ltrim(sys_connect_by_path(cnm, ','),
',') column_name_path
from (select owner,
table_name,
decode(data_type,'CLOB','dbms_lob.getlength('''||column_name||''')','BLOB','dbms_lob.getlength('''||column_name||''')',column_name) cnm,
-- column_name,
row_number() over(partition by table_name order by table_name, column_name) rank from dba_tab_columns
where (owner,table_name) in (select owner,table_name from dba_lobs where owner='HIS45') order by table_name,
column_name)
connect by table_name = prior table_name
and rank - 1 = prior rank))
where column_name_path_rank = 1;
spool off
! grep '^select ' lob1.sql > lob1.txt
!sed 's/[ ]*$//g' lob1.txt >lob1.sql
!rm -rf lob1.txt
spool lob1_res.txt
@lob1.sql
spool off
EOF