Check maximum utilization of open cursor in Oracle

 For error:

ORA-01000: maximum open cursors exceeded

ORA-00604: error occurred at recursive SQL level 1

Check the session which causes the issue:

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;

Check the Query causing to open many cursors:

select  sid ,sql_text, count(*) as "OPEN CURSORS", USER_NAME from v$open_cursor where sid in ($SID);

Check session wise open cursor:

SELECT sid,user_name, COUNT(*) "Cursors per session" FROM v$open_cursor GROUP BY sid,user_name;

Check the maximum current open cursor from all sessions:

select max(Cursorpersession) from (select sid,count(*) cursorpersession from v$open_cursor GROUP BY sid);

Check the currently maximum open cursor:

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;

Check the current value of the open cursor:

SELECT value FROM v$parameter WHERE name = 'open_cursors';

Change the value of open cursor:

alter system set open_cursors = 3000 scope=both;