Oracle DBlink 比较两边对象和数据

对象比较

select a.object_type,
       a.ds_count,
       b.dt_count,
       ds_count - dt_count minus_count
  from (select object_type, count(*) ds_count
          from dba_objects@dbverify
         where generated = 'N'
           and object_name not like 'BIN$%'
           and owner in ('SAPSR3')
         group by object_type) a
  left join (select object_type, count(*) dt_count
               from dba_objects
              where generated = 'N'
                and object_name not like 'BIN$%'
                and owner in ('SAPSR3')
              group by object_type) b
    on a.object_type = b.object_type
 order by abs(ds_count - dt_count) desc;

数据比较

create or replace procedure proc_tab_rowcnt authid current_user is
  lv_own  varchar2(30);
  lv_tab  varchar2(30);
  lv_ds_num  number := 0;
  lv_num  number := 0;
  lv_dt_num  number := 0;
  lv_diff_num number := 0;
  lv_sql  varchar2(300) := '';
  lv_sql2 varchar2(300) := '';
  lv_sql3 varchar2(300) := '';
  lv_sql4 varchar2(300) := '';
  lv_sql5 varchar2(300) := '';
  lv_sql6 varchar2(300) := '';
  type cur is ref cursor;
  t_cur cur;
begin
lv_sql6 := 'truncate table DSG.YX_TAB_ROWCNT';
execute immediate lv_sql6;
insert into dsg.yx_tab_rowcnt(table_owner,table_name,chk_flag)
select owner, table_name,0 from dsg.real_sync_tables order by owner, table_name;
commit;

while 1=1
loop
lv_sql3 := 'select count(*) from yx_tab_rowcnt where chk_flag = '||chr(39)||'0'||chr(39);
execute immediate lv_sql3 into lv_num;
if lv_num = 0 then
 return;
end if;
lv_sql := 'select table_owner,table_name from yx_tab_rowcnt where chk_flag = '||chr(39)||'0'||chr(39)||' order by table_owner,table_name';
     open t_cur for lv_sql;

     fetch t_cur into lv_own,lv_tab;
     lv_sql4 := 'update yx_tab_rowcnt set chk_flag = '||chr(39)||'1'||chr(39)||', begin_time = sysdate where table_owner = '||chr(39)||lv_own||chr(39)||' and table_name = '||chr(39)||lv_tab||chr(39);
     execute immediate lv_sql4;
     commit;

     lv_sql2 := 'select /*+parallel(t,8)*/ count(*) from '||lv_own||'."'||lv_tab||'" t';
     lv_sql5 := 'select /*+parallel(t,8)*/ count(*) from '||lv_own||'."'||lv_tab||'"@dbverify t';
     execute immediate lv_sql5 into lv_ds_num;
     execute immediate lv_sql2 into lv_dt_num;
     lv_diff_num := lv_ds_num - lv_dt_num;
     lv_sql4 := 'update yx_tab_rowcnt set ds_count = '||lv_ds_num||',dt_count = '||lv_dt_num||',diff_count = '||lv_diff_num||', chk_flag = '||chr(39)||'2'||chr(39)||', end_time = sysdate where table_owner = '||chr(39)||lv_own||chr(39)||' and table_name = '||chr(39)||lv_tab||chr(39);
     execute immediate lv_sql4;
     commit;
     close t_cur;
     end loop;

     exception
     WHEN no_data_found
     THEN
       BEGIN
         dbms_output.put_line('End of File reached. Closing file');
       END;
end proc_tab_rowcnt;




CREATE TABLE "DSG"."YX_TAB_ROWCNT" 
   (	"TABLE_OWNER" VARCHAR2(30) NOT NULL ENABLE, 
	"TABLE_NAME" VARCHAR2(30) NOT NULL ENABLE, 
	"DS_COUNT" NUMBER, 
	"DT_COUNT" NUMBER, 
	"DIFF_COUNT" NUMBER, 
	"CHK_FLAG" CHAR(1) DEFAULT '0', 
	"BEGIN_TIME" DATE, 
	"END_TIME" DATE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS" ;