Posts Tagged ‘oracle open cursors’

A note to self post for queries to use to check cursor counts presently in use and to troubleshoot ORA-1000: maximum open cursors exceeded:

select sum(a.value), b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = ‘opened cursors current’
group by b.name;

This can be altered to focus in on a single user:

select sum(a.value), c.sql_text
from v$sesstat a, v$statname b, v$open_cursor c
where a.statistic# = b.statistic#
and b.name = ‘opened cursors current’
and c.sid=a.sid
and c.user_name=’MYUSERNAME’
group by c.sql_Text

Note that using a query that doesn’t specify ‘opened cursors current’ such as one that pulls data from v$open_cursor will provide cached cursors as well thus giving a misleading answer.

More info:

Read Full Post »

From Natalka Roshak’s blog, a good query to check for cursors currently open:

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’;

Also from her blog…one that just show the single highest count side by side with the parameter setting:

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;

To view open cursors and their associated statements:

select user_name, sql_text, a.sid, address, hash_value, b.status
from v$open_cursor a, v$session b
where a.sid=b.sid
and a.address=b.sql_address
and a.hash_value=b.sql_hash_value;

Cursor summary (also shows cached):

select sql_text, count(sql_text)
from v$open_cursor
group by sql_text ;

More on open cursors:


Read Full Post »