How to use Oracle v$ views in Shell Script

It is difficult to use v$ views in Shell script to fetch any Oracle Database internal data, due to the special character $ associated with the views.But it can be achieved by using an backspace.
Below example shows how the v$ views can be used to fetch the open cursors limit and current max cursors reached in the Oracle session.

#Variable declaration
V_FILE_NAME=”Open_Cursor_Check.txt”
V_SES_TAB=”v\$sesstat”
V_STAT_TAB=”v\$statname”
V_PARAM_TAB=”v\$parameter”
V_SEARCH_TXT1=”error”
V_USERID=$FNDNAM
V_LOGIN=$GWYUID
V_DATABASE=$USID
V_PASSWORD=$1

echo “———Open Cursor Limit Check———————-” >> ${V_FILE_NAME}

V_Q_OUTPUT=`sqlplus -s ${V_USERID}/${V_PASSWORD}@${V_DATABASE} <> ${V_FILE_NAME}

V_HIGH_OC=$(echo $V_Q_OUTPUT | awk -F ‘;’ ‘{print $1}’)
echo ‘Max cursors opened so far : ‘$V_HIGH_OC >> ${V_FILE_NAME}

V_MAX_OC=$(echo $V_Q_OUTPUT | awk -F ‘;’ ‘{print $2}’)
echo ‘Max cursors allowed to be opened : ‘$V_MAX_OC >> ${V_FILE_NAME}

if [ $V_HIGH_OC == $V_MAX_OC ]
then
echo “Error : SQL Open Cursors maximum limit is reached” >> ${V_FILE_NAME}
else
echo “SQL Open Cursors has not reached max limit” >> ${V_FILE_NAME}
fi
echo >> ${V_FILE_NAME}

#EoF

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 )

Connecting to %s