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:

#!/bin/bash
# Source oracle OS variables, see Chapter 2 for details
. /etc/oraset $1
#
crit_var=$(sqlplus -s < 1;
EOF)
#
if [ $crit_var -ne 0 ]; then
echo $crit_var
echo "MV refresh problem with $1" | mailx -s "MV refresh problem" mailid@gmail.com
else
echo $crit_var
echo "MVs ok"
fi
#
exit 0

Advertisements

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 )

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 )

w

Connecting to %s

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