DECLARE CURSOR c1 IS select dbname,dblink from monitor.dblink; v_dbname VARCHAR2(30); v_dblink VARCHAR2(30); v_sql VARCHAR2(200); v_value VARCHAR2(30);
BEGIN OPEN c1; LOOP FETCH c1 INTO v_dbname,v_dblink; EXIT WHEN c1%NOTFOUND; BEGIN v_sql:= 'select concat(owner,table_name) from monitor.exp_tab_mon where dbname='||q'('v_dbname')'||' minus select concat(owner,table_name) from dba_tables@'||v_dblink;execute immediate v_sql into v_value; exception when no_data_found then null; end; dbms_output.put_line('TABLE DIFF RESULT==> ' || v_dbname ||' = '|| NVL(v_value, '0'));END LOOP;end;/