Golden Gate Add SCHEMATRANDATA Verification

I just conjured up a small script to verify whether the GG Command

add schematrandata <schema name>

worked correctly.
By the way, please keep in mind that once you executed the above command, all future objects would be automatically covered. That is why I highly recommend setting logging at the schema level rather than the individual table level.

Basically the below query should be able to give you info

select * from table(logmnr$always_suplog_columns('name of the schema in upper case','table name in upper case'));

Please be aware that dba_log_groups will not show any entry if you had added schema level logging . Only if you had added logging at a table level using add trandata would there be an entry in dba_log_groups.

A small pl/sql snippet to go through an entire schema and report back is below

declare
l_schema varchar2(20) := upper('schema name') ;
l_table_name varchar2(200);
x_ctr number(5);
x_refcur sys_refcursor;
begin
open x_refcur for 'select table_name from dba_tables where owner='''||l_schema||''' order by table_name';
loop
fetch x_refcur into l_table_name;
exit when x_refcur%NOTFOUND;

execute immediate 'select count(*) from table(logmnr$always_suplog_columns(:1,:2))'
into x_ctr using l_schema,l_table_name ;
if x_ctr =0 then

dbms_output.put_line('Analyzing '||l_table_name||' ..**ERROR**');
else
dbms_output.put_line('Analyzing '||l_table_name||' ..OK');
end if;

end loop;
end;
/