=====Overview=====
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;