oracle_golden_gate_table_crosscheck [My wiki]

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
=====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:

<sxh bash>
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;
</sxh>

2) Slower - But generate consistant data up to specific point of time:

<sxh bash>
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;
</sxh>