The cause of this problem is that in a session, the number of statement or PreparedStatement that are not closed exceeds the maximum number of open cursors that are defined.
Use this command line to query the maximum number of open cursors for a single session of a database definition.
Show Parameter Open_cursors
View the maximum number of open cursors in the system and the maximum number of open expressions allowed
SELECT MAX(A.value) asHighest_open_cur, P.value asMax_open_cur fromV$sesstat A, v$statname B, V$parameter pWHEREa.statistic#=b.statistic# andB.name= 'opened cursors current' andP.name= 'open_cursors' GROUP byP.value;
To view the SID of the number of open cursors in the system
select A.value, S.username, S.sid, s.serial# from V$sesstat A, V$statnam E B, v$session s where a.statistic# = b.statistic# and s.sid = A.sid and b.name = " opened cursors current '
To find SQL that is not closed PreparedStatement by Sid
SELECT Oc.sid, oc.hash_value, oc.sql_text, COUNT(*) how_many From GROUPby sid, Hash_value, ORDER by 4DESC;
Workaround:
Remember to close each time you createpreparedstatement ().
PreparedStatement PS =NULL; ResultSet RS=NULL; Try{PS= Tsn.createpreparedstatement (SQL, 1); RS=Ps.executequery (); } Catch(Exception e) {Throwoaexception.wrapperexception (e); } finally { Try { if(rs! =NULL) {rs.close (); } if(PS! =NULL) {ps.close (); } } Catch(SQLException ex) {ThrowOaexception.wrapperexception (ex); } }
Reference Links:
ORA-01000: Analysis and resolution of the maximum number of open cursors exceeded
On the depth discrimination of finally statement block in Java
java.sql.sqlexception:ora-01000: Maximum number of open cursors exceeded