Where can I find how many cursors are currently opened.
I tried with following query, will it give accurate result ?
select count(*) from v$open_cursor;
1 row selected.
My init.ora shows Open_cursors = 150 , but I am getting error that open cursor exceed .
V$sysstat shows :
select value from v$sysstat where statistic# = 3;
1 row selected.
which one is correct ?
11-21-2000, 02:30 AM
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
11-21-2000, 06:10 AM
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.
11-21-2000, 07:30 AM
v$sesstat stores the information for a session, if you have had 100 cursor during session it will say 100. that is why after one hour the information Patrol gathers is increasing.
The qury you issued in your previous post gathers the total open cursors opened in the system. If you join v$session and v$open_cursor you could see the current open cursor for each user.
Just a note, open_cursors īs value is per user not per system
11-21-2000, 08:36 AM
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?