How to find materialized view log entries?

We have faced a situation where replication with materialized view needs to be checked before the shutdown outage between source and destination.
This is a little script where you can find if there is entries in the MV logs.

–use dba_mview_logs for 9i and 10g
cursor cur is select log_table from dba_snapshot_logs;
num_rows number;
cnt number := 0;
for x in cur loop
execute immediate ‘select count(*) from ‘||x.log_table into num_rows;
if ( num_rows > 0 ) then
dbms_output.put_line(x.log_table||’ has ‘||num_rows||’ rows’);
cnt := cnt + 1;
end if;
end loop;
dbms_output.put_line(cnt||’ materialized view logs have entries’);

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.