Errorstack诊断ORA-01000: maximum open cursors exceeded

快来打我* 2022-06-17 02:47 223阅读 0赞

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