ããå¤çè¶
åºæå¼
游æ çæ大æ°å¼å¸¸ï¼
ããå¨æ§è¡å¦ä¸ä»£ç æ¶ï¼ç»å¸¸ä¼åºç°ora-01000: maximum open cursors exceededå¼å¸¸
ããfor(int i=0;i<balancelist.size();i++)
ãã{
ããPRepstmt = conn.prepareStatement(sql[i]);
ãã
ããprepstmt.setBigDecimal(1,nb.getRealCost());
ããprepstmt.setString(2, adclient_id);
ããprepstmt.setString(3, daystr);
ããprepstmt.setInt(4, ComStatic.portalId);
ããprepstmt.executeUpdate();
ãã}
ãã1. æ£æ¥æ°æ®åºä¸ç OPEN_CURSORS åæ°å¼ã
ããOracle ä½¿ç¨ init.ora ä¸çåå§ååæ° OPEN_CURSORS æå®ä¸ä¸ªä¼è¯ä¸æ¬¡æå¤å¯ä»¥æ¥æç游æ æ°ã缺çå¼ä¸º 50ãè¦è·å¾æ°æ®åºä¸ OPEN_CURSORS åæ°çå¼ï¼å¯ä»¥ä½¿ç¨ä»¥ä¸æ¥è¯¢ï¼
ããSQL> show parameter open_cursors;
ããNAME TYPE VALUE
ãã------------------------------------ ----------- ---------------
ããopen_cursors integer 1000
ããéè¦çæ¯å° OPEN_CURSORS çå¼è®¾ç½®å¾è¶³å¤å¤§ï¼ä»¥é¿å
åºç¨ç¨åºç¨å°½æææå¼ç游æ ãåºç¨ç¨åºä¸åï¼è¯¥å¼ä¹ä¸åãå³ä¾¿ä¼è¯æå¼ç游æ æ°æªè¾¾ OPEN_CURSORS æå®çæ°éï¼å³è®¾ç½®çå¼é«äºå®é
éè¦çå¼ï¼ï¼ ä¹ä¸ä¼å¢å ç³»ç»å¼éã
ãã2. è·åæå¼ç游æ æ°ã
ããä¸é¢çæ¥è¯¢æéåºæ¾ç¤ºç¨æ·âSCOTTâ为æ¯ä¸ªä¼è¯æå¼ç游æ æ°ã
ããSQL> select o.sid, osuser, machine, count(*) num_curs
ãã2 from v$open_cursor o, v$session s
ãã3 where user_name = 'SCOTT' and o.sid=s.sid
ãã4 group by o.sid, osuser, machine
ãã5 order by num_curs desc;
ããSID OSUSER MACHINE NUM_CURS
ãã-----------------------------------------------------
ãã217 m1 1000
ãã96 m2 10
ãã411 m3 10
ãã50 test 9
ãã请注éï¼v$open_cursor å¯ä»¥è·è¸ªä¼è¯ä¸ PARSED å NOT CLOSED çå¨æ游æ ï¼ä½¿ç¨ dbms_sql.open_cursor() æå¼ç游æ ï¼ãå®ä¸ä¼è·è¸ªæªç»åæï¼ä½å·²æå¼ï¼çå¨æ游æ ãå¨åºç¨ç¨åºä¸ä½¿ç¨å¨æ游æ 并ä¸å¸¸è§ãæ¬æ¨¡å¼çåææ¯æªä½¿ç¨å¨æ游æ ã
ãã3. è·å为游æ æ§è¡ç SQLã
ãã使ç¨å¨ä»¥ä¸æ¥è¯¢ç»æä¸æ¾å°ç SID è¿è¡ä¸é¢çæ¥è¯¢ï¼
ããSQL> select q.sql_text
ãã2 from v$open_cursor o, v$sql q
ãã3 where q.hash_value=o.hash_value and o.sid = 217;
ããSQL_TEXT
ããselect * from empdemo where empid='212'
ããselect * from empdemo where empid='321'
ããselect * from empdemo where empid='947'
ããselect * from empdemo where empid='527'
ãã...
ããç»æå°æ¾ç¤ºæ£å¨è¿æ¥ä¸æ§è¡çæ¥è¯¢ãå®æä¾äºä¸ä¸ªå
¥æç¹ï¼è®©æ¨å¯ä»¥ååè·è¸ªå°æå¼æ¸¸æ çæ¥æºã
ããè¿æ ·çé误å¾è½»æåºç°å¨java代ç ä¸ç主è¦åå æ¯ï¼Java代ç å¨æ§è¡conn.createStatement()åconn.prepareStatement()çæ¶åï¼å®é
ä¸é½æ¯ç¸å½ä¸å¨æ°æ®åºä¸æå¼äºä¸ä¸ªcursorãå°¤å
¶æ¯ï¼åå¦ä½ çcreateStatementåprepareStatementæ¯å¨ä¸ä¸ªå¾ªç¯éé¢çè¯ï¼å°±ä¼é常轻æåºç°è¿ä¸ªé®é¢ãå 为游æ ä¸ç´å¨ä¸åçæå¼ï¼èä¸æ²¡æå
³éã
ããä¸è¬æ¥è¯´ï¼æ们å¨åJava代ç çæ¶åï¼createStatementåprepareStatementé½åºè¯¥è¦æ¾å¨å¾ªç¯å¤é¢ï¼èä¸ä½¿ç¨äºè¿äºStatmentåï¼åæ¶å
³éãæ好æ¯å¨æ§è¡äºä¸æ¬¡executeQueryãexecuteUpdateçä¹åï¼åå¦ä¸éè¦ä½¿ç¨ç»æéï¼ResultSetï¼çæ°æ®ï¼å°±é©¬ä¸å°StatementæPreparedStatementå
³éã
ãã对äºåºç°ORA-01000é误è¿ç§æ
åµï¼å纯çå 大open_cursors并ä¸æ¯å¥½åæ³ï¼é£åªæ¯æ²»æ ä¸æ²»æ¬ãå®é
ä¸ï¼ä»£ç ä¸çéæ£å¹¶æ²¡æ解é¤ã
ããèä¸ï¼ç»å¤§é¨åæ
åµä¸ï¼open_cursorsåªéè¦è®¾ç½®ä¸ä¸ªæ¯è¾å°çå¼ï¼å°±è¶³å¤ä½¿ç¨äºï¼é¤éæé常éå¡çè¦æ±ã
ããåå¦ä½ ä¸ä½¿ç¨è¿æ¥æ± ï¼é£ä¹å°±æ²¡æä»ä¹é®é¢ï¼ä¸æ¦Connectionå
³éï¼æ°æ®åºç©çè¿æ¥å°±è¢«éæ¾ï¼ææç¸å
³Javaèµæºä¹å¯ä»¥è¢«GCåæ¶äºã
ããä½æ¯åå¦ä½ 使ç¨è¿æ¥æ± ï¼é£ä¹è¯·æ³¨éï¼Connectionå
³é并ä¸æ¯ç©çå
³éï¼åªæ¯å½è¿è¿æ¥æ± ï¼æ以PreparedStatementåResultSeté½è¢«ææï¼å¹¶ä¸å®é
å ç¨ç¸å
³çæ°æ®åºç游æ èµæºï¼å¨è¿ç§æ
åµä¸ï¼åªè¦é¿æè¿è¡ï¼å¾å¾å°±ä¼æ¥â游æ è¶
åºæ°æ®åºçåºçæ大å¼âçé误ï¼å¯¼è´ç¨åºæ æ³æ£å¸¸è®¿é®æ°æ®åºã
ããæ£ç¡®ç代ç ï¼å¦ä¸æ示ï¼
ããfor(int i=0;i<balancelist.size();i++)
ãã{
ããprepstmt = conn.prepareStatement(sql[i]);
ããprepstmt.setBigDecimal(1,nb.getRealCost());
ããprepstmt.setString(2, adclient_id);
ããprepstmt.setString(3, daystr);
ããprepstmt.setInt(4, ComStatic.portalId);
ããprepstmt.executeUpdate();
ããprepstmt.close();
ãã}