Script for materialized view refresh in Oracle

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
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 )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s