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

Oracle Open Cursor Check Queries

Query to check if Open Cursors has reached Max limit :
SELECT  max(a.value) as highest_open_cur, p.value as max_open_cur 
FROM v$sesstat a, v$statname b, v$parameter p 
WHERE  a.statistic# = b.statistic#  
and b.name = ‘opened cursors current’ 
and p.name= ‘open_cursors’ 
group by p.value;
 
Query to get count and list of SQL Queries causing Open Cursors :
select  sid , sql_text, count(*) open_cursors,user_name
from v$open_cursor b
group by sid ,sql_text,user_name 
order by 3 desc;
 
Query to get list of Sessions(SID) with Open Cursors Count:
select a.value, s.username, s.sid, s.serial# 
from v$sesstat a, v$statname b, v$session s 
where a.statistic# = b.statistic#  
and s.sid=a.sid 
and b.name = ‘opened cursors current’ 
and s.username is not null
order by 1 desc;