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:
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”