出现ORA-01000: maximum open cursors exceeded，就要检查open_cursors的设置，如果设置太小就要适当增大。但如果已经很大了，如超过1000了，还是报问题，那就需要诊断了。下面来模拟这个错误：
SQL> show parameter open_cursor NAME TYPE VALUE ------------------ ----------- -------- open_cursors integer 300 SQL> alter system set open_cursors=15 scope=both; SQL> show parameter open_cursors; NAME TYPE VALUE ------------------ ----------- -------- open_cursors integer 15
import java.sql.Connection; import java.sql.DriverManager; import java.sql.ResultSet; import java.sql.Statement; public class TestCursor { public static void main(String args[]) throws Exception{ Connection con = null; Statement stmt = null; ResultSet rset = null; try{ Class.forName("oracle.jdbc.driver.OracleDriver"); String url = "jdbc:oracle:thin:@127.0.0.1:1521:ora11"; String user = "test"; String password = "test"; con = DriverManager.getConnection(url, user, password); for(int i=0; i<=300; i++){ stmt = con.createStatement(); rset = stmt.executeQuery ("select * from test"); while (rset.next ()) { rset.getString(1); } } }catch (Exception e){ e.printStackTrace(); } finally{ try{ if (rset != null) rset.close(); if (stmt != null) stmt.close(); if (con != null) con.close(); } catch (Exception e){ e.printStackTrace(); } } } } java.sql.SQLException: ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01000: 超出打开游标的最大数 ORA-00604: 递归 SQL 级别 1 出现错误 ORA-01000: 超出打开游标的最大数 ORA-01000: 超出打开游标的最大数 at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:445) at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:396) at oracle.jdbc.driver.T4C8Oall.processError(T4C8Oall.java:879) at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:450) at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:192) at oracle.jdbc.driver.T4C8Oall.doOALL(T4C8Oall.java:531) at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:193) at oracle.jdbc.driver.T4CStatement.executeForDescribe(T4CStatement.java:873) at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1167) at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1289) at oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:1491) at oracle.jdbc.driver.OracleStatementWrapper.executeQuery(OracleStatementWrapper.java:406) at TestCursor.main(TestCursor.java:19)
Errorstack与oradebug类似，都是dump 错误栈信息，通常Oracle发生错误时前台进程将得到一条错误信息，但某些情况下无法获取详细的错误信息，导致无法准确定位问题，可以采用这种方式得到Oracle错误。
alter system set events '1000 trace name errorstack level 3'; alter system set events '1000 trace name context off';
表示当出现ORA-1000错误时，dump 错误栈和进程栈。 设置ErrorStack主要有4个级别: 1 转储错误堆栈和函数调用堆栈 2 Level 1 + ProcessState 3 Level 2 + Context area(显示所有cursors,着重显示当前cursor) ErrorStack可以在实例级或会话级别设置，也可以在参数文件中设置，这个设置仅当某个特定的错误出现时才被触发。
分析Oracle alter日志: OS Pid: 8588 executed alter system set events '1000 trace name errorstack level 3' Fri Apr 14 10:29:35 2017 Errors in file f:\app\administrator\diag\rdbms\ora11\ora11\trace\ora11_ora_8764.trc: ORA-01000: 超出打开游标的最大数 打开ora11_ora_8764.trc： dbkedDefDump(): Starting a non-incident diagnostic dump (flags=0x0, level=3, mask=0x0) ----- Error Stack Dump ----- ORA-01000: 超出打开游标的最大数 ----- SQL Statement (None) ----- Current SQL information unavailable - no cursor. ----- Session Cursor Dump ----- Current cursor: 0, pgadep=0 Open cursors(pls, sys, hwm, max): 15(0, 3, 15, 15) NULL=0 SYNTAX=0 PARSE=0 BOUND=15 FETCH=0 ROW=0 Cached frame pages(total, free): 4k(11, 11), 8k(0, 0), 16k(1, 1), 32k(0, 0) ----- Session Open Cursors ----- ---------------------------------------- Cursor#1(0x000000001BD91998) state=BOUND curiob=0x000000001BDAD5B0 .......... Cursor#5(0x000000001BD91BD8) state=BOUND curiob=0x000000001CBD94E8 curflg=46 fl2=0 par=0x0000000000000000 ses=0x000007FF4C5DAB70 ----- Dump Cursor sql_id=c99yw1xkb4f1u xsc=0x000000001CBD94E8 cur=0x000000001BD91BD8 ----- LibraryHandle: Address=4b163540 Hash=64b2383a LockMode=N PinMode=0 LoadLockMode=0 Status=VALD ObjectName: Name=select * from test .......... Cursor#6(0x000000001BD91C68) state=BOUND curiob=0x000000001CBD8958 curflg=46 fl2=0 par=0x0000000000000000 ses=0x000007FF4C5DAB70 ----- Dump Cursor sql_id=c99yw1xkb4f1u xsc=0x000000001CBD8958 cur=0x000000001BD91C68 ----- LibraryHandle: Address=4b163540 Hash=64b2383a LockMode=N PinMode=0 LoadLockMode=0 Status=VALD ObjectName: Name=select * from test .......... Cursor#9(0x000000001BD91E18) state=BOUND curiob=0x000000001CBD66A8 curflg=46 fl2=0 par=0x0000000000000000 ses=0x000007FF4C5DAB70 ----- Dump Cursor sql_id=c99yw1xkb4f1u xsc=0x000000001CBD66A8 cur=0x000000001BD91E18 ----- LibraryHandle: Address=4b163540 Hash=64b2383a LockMode=N PinMode=0 LoadLockMode=0 Status=VALD ObjectName: Name=select * from test