How To Troubleshoot Error ORA-01000
(maximum open cursors exceeded)

Summary:
For every SQL statement execution in Oracle Database, certain area in the memory is allocated. Oracle PL/SQL allows you to name this area. This private SQL area is called context area or cursor. These cursors take up space in the shared pool, which is essential memory component of Oracle Database, specifically in the library cache. To keep a renegade session from filling up the library cache or clogging the CPU with millions of parse requests, the OPEN_CURSORS database parameter must be set to limit the cursors.
The OPEN_CURSORS parameter sets the maximum number of cursors that each session can have open, per session. For example, if the value of OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time.Sometimes, the number of cursors in the database exceeds the maximum limit, and as a result, the following error is thrown:
java.sql.SQLException: ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded ORA-00604: error occurred at recursive SQL level 1

Steps to troubleshoot the open_cursors issue like above are:

1.	Login to the SYS schema (or any schema with DBA privilege) of the database.
 
2.	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.
 
3.	Find out the session which is causing ORA-01000: maximum open cursors exceeded , run the following SQL statement:

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

The top queries that are opening maximum cursors and are not closing subsequent cursors gracefully are displayed.
 
Important Point: 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. 
 
4.	To see if you've set OPEN_CURSORS high enough, monitor v$sesstat for the maximum opened cursors current. If your sessions are running close to the limit, up the value of OPEN_CURSORS. 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;

 Solution :
1.	If your sessions are running close to the limit, then increase the value of the OPEN_CURSORS and tuned the SESSION_CACHED_CURSORS, CURSOR_SHARING, all SGA AND PGA memory (cursor is using SHARED_POOL) parameters.

2.	 In EU12 environment the value  OPEN_CURSORS is 300. So we list the top 10 sessions which are currently opening most cursors.
select * from ( select ss.value, sn.name, ss.sid   from v$sesstat ss, v$statname sn   where ss.statistic# = sn.statistic#  and sn.name like '%opened cursors current%'   order by value desc) where rownum < 11 ;

VALUE	NAME	SID	
19	opened cursors current	621	
16	opened cursors current	1074	
15	opened cursors current	485	
15	opened cursors current	921	
15	opened cursors current	626	
14	opened cursors current	13	
14	opened cursors current	1083	
14	opened cursors current	1082	
14	opened cursors current	1078	
14	opened cursors current	923	

Now we check what make session 621 open to many cursors?

3.	To check the STATUS of the session id 621 

select sid, status, event, seconds_in_wait state "wait(s)" , blocking_session "blk_sesn", prev_sql_id  "SQL_ID"  from v$session where sid=621;
SID	STATUS	EVENT	STATE_WAITS	blk_sesn	SQL_ID	
621	INACTIVE	SQL*Net message from client	2679		2b50zw675uq7m	

4.	Since the status of the cursor is INACTIVE so we can we kill the session by using the below command , which will close the cursors but in case the  status is ACTIVE do not kill:

alter system kill session 'sid,serial#' immediate;