Script for materialized view refresh in Oracle

DBMS_mview is used for refresh the Materialized view. You can also scheduled it in Oracle Scheduler

Example for manually refresh:

exec dbms_mview.refresh('SALES_MV','C');

C - Complete refresh
F - Fast refresh

Script for Scheduling the refresh of Materialized view in Linux platform:

#!/bin/bash
if [ $# -ne 1 ]; then
echo "Usage: $0 SID"
exit 1
fi
#
HOSTNAME='uname -a | awk '{print$2}''
MAILX='/bin/mailx'
MAIL_LIST='abc@gmail.com'
ORACLE_SID=$1
jobname=SALES_MV
# Source oracle OS variables, see Chapter 2 for details.
. /etc/oraset $ORACLE_SID
#
sqlplus -s <<EOF
mv_maint/foo
WHENEVER SQLERROR EXIT FAILURE
exec dbms_mview.refresh('SALES_MV','C');
EOF
#
if [ $? -ne 0 ]; then
echo "not okay"
$MAILX -s "Problem with MV refresh on $HOSTNAME $jobname" $MAIL_LIST <<EOF
$HOSTNAME $jobname MVs not okay.
EOF
else
echo "okay"
$MAILX -s "MV refresh OK on $HOSTNAME $jobname" $MAIL_LIST <<EOF
$HOSTNAME $jobname MVs okay.
EOF
fi
#
exit 0