Find out Opened Cursors and the related Objects

In this forum you can share stuff related to Oracle 11g, 10g, 9i.
Post Reply
keerti_rose
Posts: 40
Joined: Sat Mar 01, 2008 3:39 pm
Location: India

Find out Opened Cursors and the related Objects

Post by keerti_rose »

Hi Floks,

I have a procedure with 10 Cursors declared in that..Out of 10 i have closed 9 Cursors ...Means i haven't closed one cursor..After that I have given it to some body and they got execute the same and got the results in a proper way...But i want to know the details of that coursor information without opening and looking into that code.

Is there anyway to findout Obect Name,Cursor name which was not closed in that Object?

Please help me in this...Thanks
amirtai
Posts: 138
Joined: Sat Apr 08, 2006 5:54 pm
Location: Canada
Contact:

Post by amirtai »

Hi there

Here are few ways to monitor open cursors, that'll give you some idea.

To monitor open cursors, query v$sesstat where name='opened cursors current'. This will give the number of currently opened cursors, by session:

--total cursors open, by session
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';

If you're running several N-tiered applications with multiple webservers, you may find it useful to monitor open cursors by username and machine:

--total cursors open, by username & machine
select sum(a.value) total_cur, avg(a.value) avg_cur, max(a.value) max_cur,
s.username, s.machine
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'
group by s.username, s.machine
order by 1 desc;

Amir
Post Reply

Who is online

Users browsing this forum: No registered users and 0 guests