Script for Linux platform for monitoring the Materialized view refresh activity.
It will check the view user_mviews for refresh activity and send mail to the mentioned mail id in case materialized view is not refreshed in last 24 hours.
You can manually check Materialized view refresh activity from this below Query:
SELECT owner, mview_name,count(*) FROM user_mviews WHERE sysdate-last_refresh_date > 1 group by mview_name,owner;
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
select last_refresh_Date from user_mviews where mview_name='SALES_REPORT';
Script for Linux:
# Source oracle OS variables, see Chapter 2 for details
. /etc/oraset $1
crit_var=$(sqlplus -s < 1;
if [ $crit_var -ne 0 ]; then
echo "MV refresh problem with $1" | mailx -s "MV refresh problem" email@example.com
echo "MVs ok"