Troubleshooting Open Cursor Issues - ORA-01000: maximum open cursors exceeded

Troubleshooting Open Cursor Issues - ORA-01000: maximum open cursors exceeded

Find out the session that is causing the error by using the following SQL statement:

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' and s.username is not null;

The output displays the details of all sessions. You can see the maxed out session IDs.
To display which queries are causing maxing out of open cursors, run the following SQL statement:

set lines 200
col USER_NAME FOR A40
select  sid ,USER_NAME,sql_text, count(*) as "OPEN CURSORS" from v$open_cursor where sid in (&SID) group by sid ,USER_NAME,sql_text ;

set lines 200
col USER_NAME FOR A40
select  sid ,USER_NAME,sql_text,sql_id, count(*) as "OPEN CURSORS" from v$open_cursor where sid in (&SID) group by sid ,USER_NAME,sql_text,sql_id ;

The top queries that are opening maximum cursors and are not closing subsequent cursors gracefully are displayed.If some code is running above SQL queries, then check that Java Statement, Resultset, or connection are closing properly or not if they have access to the code.

If the code is not closing the connections, then close all the open connections properly so that you can save memory leaks in the code and save database memory.

To verify if you have set the value of the OPEN_CURSORS parameter high enough, monitor v$sesstat for the maximum opened cursors current, as shown:

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;

If your sessions are running close to the limit, then increase the value of the OPEN_CURSORS parameter.

Queryies:

open_cursor

 

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

 

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

 

--Open cursor and query details

 

SELECT s.machine, oc.user_name, oc.sql_text, count(1)
FROM v$open_cursor oc, v$session s
WHERE oc.sid=s.sid GROUP BY user_name, sql_text, machine
HAVING COUNT(1) > 2 ORDER BY COUNT(1) DESC;