使用minus比对非LOB和LOB表的两个小脚本

来源：互联网发布：u3d编程是什么语言编辑：程序博客网时间：2024/05/16 23:02

脚本写的比较简单，使用minus比较用户下的表数据，第一个脚本是比对非LOB字段的，第二个脚本比对有LOB字段的表。

基本原理就是

select * from xxxx@xx

minu

select * from xxxxx;

这样的语句。其中比对LOB字段使用dbms_lob.getlength获取lob字段的长度进行比对。

首先创建一个dblink，这里就不说了

第一个比对不含LOB的表：

sqlplus ggs/ggs <<EOFset feedback off pagesize 0 heading off verify off linesize 600 trimspool onspool tab.sqlcol a for a20col b for a30select '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.txtspool minus_res.txt@tab.sqlspool offEOFexit

第二个比对含有LOB字段的表：

sqlplus ggs/ggs <<EOFset line 4000set pages 0set heading offset echo offset long 99999spool lob1.sqlselect '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.txtspool lob1_res.txt@lob1.sqlspool offEOF

0 0