Errorstack诊断ORA-01000: maximum open cursors exceeded

    出现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