jdbc - java.sql.SQLException: - ORA-01000: maximum open cursors exceeded -

jdbc - java.sql.SQLException: - ORA-01000: maximum open cursors exceeded -

i getting above ora-01000 sql exception. have queries related it.

maximum open cursors related no of jdbc connections, or related statement objects , resultset objects have created single connections? since using pool of connections. is there in database can specify no of statement/resultset objects connections? is advisable utilize instance variable statement/resultset object instead of method local statement/resultset object in single threaded environment?

does executing prepare stmt on loop cause issue? (of course of study should have used sqlbatch) note: pstmt closed 1 time loop over.

{ //method seek starts string sql = "insert tblname (col1, col2) values(?, ?)"; pstmt = obj.getconnection().preparestatement(sql); pstmt.setlong(1, subscriberid); (string language : additionallangs) { pstmt.setint(2, integer.parseint(language)); pstmt.execute(); } } //method/try ends { //finally starts pstmt.close() } //finally ends

what happen if conn.createstatement() , conn.preparestatement(sql) called multiple times on single connection object?

edit1: 6. utilize of weak/soft reference statement object reference help anymore on leakage?

edit2: 7. there best way; can find in project , statement.close() not happened properly? understand not memory leak. need find statement reference(close() not performed) eligible garbage collection? tool available? or have manually analyze it?

please create me aware more it.

solution to find opened cursor in oracle db username -velu

go oralce machine , start sqlplus sysdba.

[oracle@db01 ~]$ sqlplus / sysdba

then run

select a.value, s.username, s.sid, s.serial# v$sesstat a, v$statname b, v$session s a.statistic# = b.statistic# , s.sid=a.sid , b.name = 'opened cursors current' , username = 'velu';

if possible please read reply @ end..

ora-01000, maximum-open-cursors error, extremely mutual error in oracle database development. in context of java, happens when application attempts open more resultsets there configured cursors on database instance.

common causes are:

configuration mistake

you have more threads in application querying database cursors on db. 1 case have connection , thread pool larger number of cursors on database. you have many developers or applications connected same db instance (which include many schemas) , using many connections.

solution:

increasing number of cursors on database (if resources allow) or decreasing number of threads in application.

cursor leak

the applications not closing resultsets (in jdbc) or cursors (in stored procedures on database) solution: cursor leaks bugs; increasing number of cursors on db delays inevitable failure. leaks can found using static code analysis, jdbc or application-level logging, , database monitoring. background

this section describes of theory behind cursors , how jdbc should used. if don't need know background, can skip , go straight 'eliminating leaks'.

what cursor?

a cursor resource on database holds state of query, position reader in resultset. each select statement has cursor, , pl/sql stored procedures can open , utilize many cursors require. can find out more cursors on orafaq.

a database instance typically serves several different schemas, many different users each multiple sessions. this, has fixed number of cursors available schemas, users , sessions. when cursors open (in use) , request comes in requires new cursor, request fails ora-010000 error.

finding , setting number of cursors

the number configured dba on installation. number of cursors in use, maximum number , configuration can accessed in administrator functions in oracle sql developer. sql can set with:

alter scheme set open_cursors=1337 sid='*' scope=both; relating jdbc in jvm cursors on db

the jdbc objects below tightly coupled next database concepts:

jdbc connection client representation of database session , provides database transactions. connection can have single transaction open @ 1 time (but transactions can nested) a jdbc resultset supported single cursor on database. when close() called on resultset, cursor released. a jdbc callablestatement invokes stored procedure on database, written in pl/sql. stored procedure can create 0 or more cursors, , can homecoming cursor jdbc resultset.

jdbc thread safe: quite ok pass various jdbc objects between threads.

for example, can create connection in 1 thread; thread can utilize connection create preparedstatement , 3rd thread can process result set. single major restriction cannot have more 1 resultset open on single preparedstatement @ time. see does oracle db back upwards multiple (parallel) operations per connection?

note database commit occurs on connection, , dml (insert, update , delete's) on connection commit together. therefore, if want back upwards multiple transactions @ same time, must have @ to the lowest degree 1 connection each concurrent transaction.

closing jdbc objects

a typical illustration of executing resultset is:

statement stmt = conn.createstatement(); seek { resultset rs = stmt.executequery( "select full_name emp" ); seek { while ( rs.next() ) { system.out.println( "name: " + rs.getstring("full_name") ); } } { seek { rs.close(); } grab (exception ignore) { } } } { seek { stmt.close(); } grab (exception ignore) { } }

note how clause ignores exception raised close():

if close resultset without seek {} grab {}, might fail , prevent statement beingness closed we want allow exception raised in body of seek propagate caller. if have loop over, example, creating , executing statements, remember close each statement within loop.

in java 7, oracle has introduced autocloseable interface replaces of java 6 boilerplate nice syntactic sugar.

holding jdbc objects

jdbc objects can safely held in local variables, object instance , class members. improve practice to:

use object instance or class members hold jdbc objects reused multiple times on longer period, such connections , preparedstatements use local variables resultsets since these obtained, looped on , closed typically within scope of single function.

there is, however, 1 exception: if using ejbs, or servlet/jsp container, have follow strict threading model:

only application server creates threads (with handles incoming requests) only application server creates connections (which obtain connection pool) when saving values (state) between calls, have careful. never store values in own caches or static members - not safe across clusters , other weird conditions, , application server may terrible things data. instead utilize stateful beans or database. in particular, never hold jdbc objects (connections, resultsets, preparedstatements, etc) on different remote invocations - allow application server manage this. application server not provides connection pool, caches preparedstatements. eliminating leaks

there number of processes , tools available helping observe , eliminating jdbc leaks:

during development - catching bugs far best approach:

development practices: development practices should cut down number of bugs in software before leaves developer's desk. specific practices include:

pair programming, educate without sufficient experience code reviews because many eyes improve one unit testing means can exercise , of code base of operations test tool makes reproducing leaks trivial use existing libraries connection pooling rather building own

static code analysis: utilize tool first-class findbugs perform static code analysis. picks many places close() has not been correctly handled. findbugs has plugin eclipse, runs standalone one-offs, has integrations jenkins ci , other build tools

at runtime:

holdability , commit

if resultset holdability resultset.close_cursors_over_commit, resultset closed when connection.commit() method called. can set using connection.setholdability() or using overloaded connection.createstatement() method.

logging @ runtime.

put log statements in code. these should clear , understandable customer, back upwards staff , teammates can understand without training. should terse , include printing state/internal values of key variables , attributes can trace processing logic. logging fundamental debugging applications, have been deployed.

you can add together debugging jdbc driver project (for debugging - don't deploy it). 1 illustration (i have not used it) log4jdbc. need simple analysis on file see executes don't have corresponding close. counting open , closes should highlight if there potential problem

monitoring database. monitor running application using tools such sql developer 'monitor sql' function or quest's toad. monitoring described in this article. during monitoring, query open cursors (eg table v$sesstat) , review sql. if number of cursors increasing, , (most importantly) becoming dominated 1 identical sql statement, know have leak sql. search code , review. other thoughts can utilize weakreferences handle closing connections?

weak , soft references ways of allowing reference object in way allows jvm garbage collect referent @ time deems fit (assuming there no strong reference chains object).

if pass referencequeue in constructor soft or weak reference, object placed in referencequeue when object gc'ed when occurs (if occurs @ all). approach, can interact object's finalization , close or finalize object @ moment.

phantom references bit weirder; purpose command finalization, can never reference original object, it's going hard phone call close() method on it.

however, thought effort command when gc run (weak, soft , phantomreferences allow know after fact object enqueued gc). in fact, if amount of memory in jvm big (eg -xmx2000m) might never gc object, , still experience ora-01000. if jvm memory little relative program's requirements, may find resultset , preparedstatement objects gced after creation (before can read them), fail program.

tl;dr: weak reference mechanism not way manage , close statement , resultset objects.

java jdbc