Execute this script which will give the output u required.
REM Opened Cursors by session
REM Vinod Kumar M.G.
START titredba "Aud043" -
"Number of opened cursors by session"
COL sid FOR 999 HEAD "SID" TRUNC
COL user_ FOR A29 HEAD "OS USER" TRUNC
COL username FOR A30 HEAD "ORA USER" TRUNC
COL name FOR A39 HEAD "STATISTICS" TRUNC
COL value FOR 99999990 HEAD "OPENED|CURSORS" TRUNC
break on report
compute sum of value on report
SELECT v.sid ,
FROM v$sesstat s,
WHERE s.sid = v.sid
AND 3 = s.statistic#
AND p.addr = v.paddr
Thanks for the help. Is V$sesstat holds cumilative statistics or current statistics. we have BMC Patrol installed on our DB server. When I start the patrol agent, it connects to Oracle DB to gather statistics, at specified intervals it gathers statistics. When I run your query first time I found 2 open cursors for this Patrol user. After One hour again I run that query and found that this time 20 open cursors for this user.
I am not able to figureout what is really going wrong, and where to find the info.
Please correct me if I am wrong, if v$sesstat show 100 , that means at one particular point of time that user have had 100 open cursors.
Whenever I join V$session with V$open_cursor I only found 1 open cursor for this user Patrol, at the same time when I query V$sesstat value for this particualr user always growing, evry 5 five minutes it is increased by 1 (for every 5 minutes info is gathered from this particular user, that time it will run some pl/sql programs, once after gathering information it supposed to close all the cursors). In this case I feel It is not closing all the previously opened cursors, Am I rcorrect?