I have created a basic query which will check all tables by rowcount. This allows me to save some time than comparing CSV files :)
1) Faster - But relies on no traffic between the production and standby:
set pagesize 450 set lines 400 col table_name for a40 select local.table_name, local.count, remote.count, decode(local.count - remote.count, 0 , 'OK','NOT OK') from ( SELECT owner, table_name, ',', to_number( extractvalue( xmltype( dbms_xmlgen.getxml('select count(*) c from &&owner .'||table_name)),'/ROWSET/ROW/C')) count FROM dba_tables where owner = '&owner') local, ( SELECT owner, table_name, ',', to_number( extractvalue( xmltype( dbms_xmlgen.getxml('select count(*) c from &&owner .'||table_name||'@gg_table_check')),'/ROWSET/ROW/C')) count FROM dba_tables@gg_table_check where owner = '&owner') remote where local.table_name = remote.table_name and local.owner = remote.owner;
2) Slower - But generate consistant data up to specific point of time:
set pagesize 450 set lines 400 col table_name for a40 select source.table_name, source.local_count, source.remote_count, decode(source.local_count - source.remote_count,0,'OK','NOT OK') from ( SELECT table_name, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from &&owner .'||table_name)),'/ROWSET/ROW/C')) as local_count, to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select count(*) c from &&owner .'||table_name||'@gg_table_check')),'/ROWSET/ROW/C')) as remote_count FROM dba_tables where owner = '&owner' ) source order by source.local_count asc;