Feeds:
Posts
Comments

Posts Tagged ‘ORA-1000’

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:
http://www.orafaq.com/node/758
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1089433252941
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:553222846752

Read Full Post »