Script for monitored Oracle Materialized view refresh Linux

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 $crit_var
echo "MV refresh problem with $1" | mailx -s "MV refresh problem"
echo $crit_var
echo "MVs ok"
exit 0

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 )

Google photo

You are commenting using your Google 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 )

Connecting to %s

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