`

ORA-00604: 递归 SQL 级别 1 出现错误,ORA-01000: 超出打开游标的最大数

 
阅读更多
@SuppressWarnings("rawtypes")
public void execBq(BatchBean bb, BlockingQueue<Map> bq, boolean closeConnetction) {
        PreparedStatement pst = null;
        try {
            conn.setAutoCommit(false);
            Long beginTime = System.currentTimeMillis();
            pst = conn.prepareStatement(bb.getSql());
            int k = bq.size();
            for (int i = 1; i <= k; i++) {
            Map s = bq.take();
                for (int j = 0; j < bb.getParam().length; j++) {
                    pst.setString(j + 1, (String) s.get(bb.getParam()[j]));
                }
                pst.addBatch();
                if (i % Storage.BUFFER_COUNT == 0 || i == k) {
                    pst.executeBatch();
                    conn.commit();
                    pst.clearBatch();
                    if(i==k){
                   
                    System.out.println("本次更新了" +k% Storage.BUFFER_COUNT+"条数据");
                    }else{
                    System.out.println("本次更新了" +Storage.BUFFER_COUNT+"条数据");
                    }
                      
                   //特别注意这里,这里看似好像是多余的;其实不然;
                    //因为每次执行 executeBatch 都会开启一次游标;到最后的时候//只执行了一次,过程当中我们分批执行了N次;所以应该是关闭N次,重新打开N次;

                   if(pst!=null){
                    System.out.println("关了了再开");
                    pst.close();
                    pst = conn.prepareStatement(bb.getSql());
                    }

                  
                   
                }
            }
            Long endTime = System.currentTimeMillis();
            System.out.println("pst+batch:" + (endTime - beginTime) / 1000 + "秒");
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
            pst.close();
            System.out.println("关闭一个连接");
                if (closeConnetction) {
                    //pst.close();
                    conn.close();
                }

            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }


关键是 这里的 pst.close(); 原本是写在里面的。如果写在里面,多线程的时候一直未关闭;一直保持住;所以会报错;我不关闭连接,但并代表我不关闭 preparstatement

select count(*) from v$open_cursor;
可以监控游标语句;改完之后,我的游标数量的确就没有再超了;

//特别注意这里,这里看似好像是多余的;其实不然;
                    //因为每次执行 executeBatch 都会开启一次游标;到最后的时候//只执行了一次,过程当中我们分批执行了N次;所以应该是关闭N次,重新打开N次;


分享到:
评论

相关推荐

Global site tag (gtag.js) - Google Analytics