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.

declare
–use dba_mview_logs for 9i and 10g
cursor cur is select log_table from dba_snapshot_logs;
num_rows number;
cnt number := 0;
begin
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’);
end;

Leave a Reply

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

WordPress.com Logo

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

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s