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;