初期化パラメータ OPEN_CURSORS とはなんぞや？（2）

Oracle の初期化パラメータ OPEN_CURSORS は1つのセッション（接続）で同時にオープンできるカーソル（プライベートSQL領域へのハンドル）の最大数。例えば、OPEN_CURSORS が300の場合、10セッション張ったら、1セッション辺り300、10セッションで3000のカーソルをオープンすることができるってことか。

初期化パラメータ OPEN_CURSORS とはなんぞや？ - ablog

を実際に検証してみた。

OPEN_CURSORS は 300。

SQL> select value from v$parameter where name ='open_cursors';

VALUE
--------------------------------------------------------------------------------
300

Statement、ResultSet を close せずに無限ループするプログラムを3つ同時に実行してみると、

$ java InfiniteLooperWithCursorOpened2 & java InfiniteLooperWithCursorOpened2 & java InfiniteLooperWithCursorOpened2 &
[1] 488
[2] 489
[3] 490
$ Open cursors: 301
Error code: 1000
SQL state: 72000
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
	at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:804)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
	at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:845)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
	at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1313)
	at InfiniteLooperWithCursorOpened2.main(InfiniteLooperWithCursorOpened2.java:20)
Open cursors: 301
Error code: 1000
SQL state: 72000
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
	at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:804)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
	at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:845)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
	at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1313)
	at InfiniteLooperWithCursorOpened2.main(InfiniteLooperWithCursorOpened2.java:20)
Open cursors: 301
Error code: 1000
SQL state: 72000
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

	at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
	at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
	at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
	at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:210)
	at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:804)
	at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1049)
	at oracle.jdbc.driver.T4CStatement.executeMaybeDescribe(T4CStatement.java:845)
	at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1154)
	at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1313)
	at InfiniteLooperWithCursorOpened2.main(InfiniteLooperWithCursorOpened2.java:20)

[1]   Done                    java InfiniteLooperWithCursorOpened2
[2]-  Done                    java InfiniteLooperWithCursorOpened2
[3]+  Done                    java InfiniteLooperWithCursorOpened2

3プロセスとも、301個目のカーソルをオープンしようとしたところで、ORA-01000 が発生。想定通り。
以下はソースコード。

• InfiniteLooperWithCursorOpened2.java

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class InfiniteLooperWithCursorOpened2 {
	public static void main(String args[]) {
		Connection conn = null;
		Statement stmt = null;
		ResultSet resultSet = null;
		int count = 0;
		try {
			Class.forName ("oracle.jdbc.driver.OracleDriver");
			conn = DriverManager.getConnection
				("jdbc:oracle:thin:@192.168.45.101:1521:orcl","scott","tiger");
			for(;;) {
			    count++;
			    stmt = conn.createStatement();
			    resultSet = stmt.executeQuery("select id, name from emp");
			    /*
			    for(;resultSet.next();) {
				System.out.println(resultSet.getString(1) + " " + resultSet.getString(2));
			    }
			    */
			}
       		} catch (SQLException e) {
		        System.out.println("Open cursors: " + count);
			System.out.println("Error code: " + e.getErrorCode());
			System.out.println("SQL state: " + e.getSQLState());
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} finally {
			try {
				if (resultSet != null) {
		  			resultSet.close();
				}
			} catch (SQLException e){
				e.printStackTrace();
			}
			try {
				if (stmt != null) {
		  			stmt.close();
				}
			} catch (SQLException e){
				e.printStackTrace();
			}
			try {
				if (conn != null) {
		  			conn.close();
				}
			} catch (SQLException e){
				e.printStackTrace();
			}
       		}
	}
}