ORA-01000: maximum open cursors exceeded

 

/** Exception thrown: ORA-01000: Maximum open cursors exceeded*/Connection conn = getConnection();for(int i = 0; i < 10000; i++){ PreparedStatement stmt = conn.prepareStatement(sql); stmt.setLong(i, i); ResultSet rs = stmt.executeQuery(); while(rs.next()) { // fetch result } } 

 

1. How to fix

stmt.close(); 

 

2. Why 

Cursor

http://en.wikipedia.org/wiki/Cursor_(databases)

 

In database packages, a cursor comprises a control structure for the successive traversal (and potential processing) of records in a result set.

Cursors provide a mechanism by which a database client iterates over the records in a database. Using cursors, the client can get, put, and delete database records. Database programmers use cursors for processing individual rows returned by the database system for a query.

 

Most of the times, set based operations can be used instead of cursors.

Fetching a row from the cursor may result in a network round trip each time. This uses much more network bandwidth than would ordinarily be needed for the execution of a single SQL statement like DELETE. Repeated network round trips can severely impact the speed of the operation using the cursor. Some DBMSs try to reduce this impact by using block fetch. Block fetch implies that multiple rows are sent together from the server to the client. The client stores a whole block of rows in a local buffer and retrieves the rows from there until that buffer is exhausted.

 

 

 

http://www.oracle.com/technology/oramag/oracle/08-nov/o68plsql.html

It’s important to know when not to use cursor FOR loops.

 

http://soft.buaa.edu.cn/oracle/bookshelf/Oreilly/prog2/ch09_05.htm

http://www.exforsys.com/tutorials/oracle-9i/oracle-cursors.html

http://www.geekinterview.com/question_details/2515

 

PL/SQL declares a cursor implicitly for all SQL data manipulation statements, including quries that return only one row. However,queries that return more than one row you must declare an explicit cursor or use a cursor FOR loop.

Explicit cursor is a cursor in which the cursor name is explicitly assigned to a SELECT statement via the CURSOR...IS statement. An implicit cursor is used for all SQL statements Declare, Open, Fetch, Close. An explicit cursors are used to process multirow SELECT statements An implicit cursor is used to process INSERT, UPDATE, DELETE and single row SELECT. .INTO statements.

 

 

3. Mysql has such problem ?

If do not close PreparedStatement/ResultSet in a Loop(For oracle, you must close statement, while for mysql both of them are ok), OOM error will be thrown.

 

Exception in thread "main" java.lang.OutOfMemoryError: Java heap spaceat com.mysql.jdbc.MysqlIO.buildResultSetWithRows(MysqlIO.java:2098)at com.mysql.jdbc.MysqlIO.getResultSet(MysqlIO.java:459)at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:2076)at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:1451)at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1787)at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1467) 

 4. Statck overflow

http://stackoverflow.com/questions/103938/resultset-not-closed-when-connection-closed

在与DB打交道时，有几种资源
1. connection (server/client)

2. cursor  (server)

3. memory (server/client)

4. cpu (server)

其中java中的ResultSet , Statement 会影响cursor和memory，而sql语句本身会影响server端的cpu.

最后Connection不用说，一般采用connection pool来管理