×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络，最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

@Florida

coming inside

changshan(偷偷乐)
本文发表在 rolia.net 枫下论坛This may happen in several ways:
1) If the communication only through the Java JDBC.
possibly, check the java code for those resultset.close(), statement.close(), and conn.close(). whenever exception catch need to call all the close() in correct order
2) In the DB side, you may need to check the instance init parameters for the maxium open cursor:
use sqlplus: logon as the specific DBA user, type "show parameters cursor"


NAME TYPE VALUE
------------------------------------ ----------- ------------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 300
session_cached_cursors integer 0

the default open_cursor is 300, this depends on how many cursors one user can open concurrently

another inportant thing is the cursor_sharing, if it's "EXACT", it's not good, thich means "select a from tab1 where b=c" and "select a from tab1 where b=d" will open two mem location in the shared pool area.they do not share the parsed sql code. this may waste open cursor number too. ask your dba change it to "FORCE" to avoid develper non-binding variable sql and even case difference


hope that helps!更多精彩文章及讨论，请光临枫下论坛 rolia.net
(#2106451@0)
Last Updated: 2005-2-2
This post has been archived. It cannot be replied.
Report

Replies, comments and Discussions:

  • 工作学习 / IT技术讨论 / 求教:JAVA 程序执行,出错:ORA-01000 maximum open cursors exceeded. 据说是RESULTSET没关,问题是程序太大,执行很久才出错, -tongcd(MockingBird); 2005-1-31 {174} (#2103895@0)
    也没有EXCEPTION,想打听以下能否在ORACLE DATABASE一边找到什么STATEMENT执行完以后没关RESULTSET,有没有什么SCRIPT在数据库上一执行就知道所有SESSION未关的RESULTSET?多谢,焦头烂额之中
    • 大家交个朋友先，这个问题是java garbage collection问题造成的，给我PM我告诉你解决办法 -pumpkinpie(南瓜饼); 2005-1-31 (#2103954@0)
      • 好 -tongcd(MockingBird); 2005-1-31 (#2103958@0)
        • 见内 -pumpkinpie(南瓜饼); 2005-1-31 {376} (#2103963@0)
          问题关键在于，close resultset 和statement是没用的，每次建立connection访问orale cursor时打开cursor不关闭，即使session结束后也不close,这样重复打开无法关闭就会造成too many cursor open.关键之处在于物理close connection,不要简单把connection返回到connection pool.写一个reconnection method,关闭后再打开，把打开的connection返回给connection pool.如果还不清楚，具体细节我可以把代码发给你
          • 那问题是CONNECTION是从WEBSERVER JNDI 中LOOKUP 来的,CONNECTION.CLOSE()我理解也是返回给WEBSERVER的CONNECTION MANAGER,并且我肯定没有CONNECTION LEAK -tongcd(MockingBird); 2005-2-1 {42} (#2103966@0)
            没有EXCEPTION,具体如何做呢?请继续提示,谢谢
            • 简而言之，resultset.close(),statement.close().connection.close(), reconnection().return connection.你如果需要oracle 的script 查open cursor代码我明天发给你。告诉我你的msn,我加你。 -pumpkinpie(南瓜饼); 2005-1-31 (#2103972@0)
              • CHECK YOUR PM, PLEASE -tongcd(MockingBird); 2005-2-1 (#2103975@0)
          • 请教一个问题。Connection pool一个很重要的优势就是不需要重新建立连接（这部分系统开销比较大），如果你这样开了关关了开岂不是失去了Connection pool一个很大的优势吗？ -fatbean(胖豆 思想的海飞丝); 2005-2-1 (#2104321@0)
            • fake close. (phiscal conn is wrapped already) -ra_95(闹钟-如果还有明天); 2005-2-1 (#2104342@0)
              • 你说的是类似于recycle的东西吧，就是把这个Connection标记为available放回pool供下一个请求者使用？可是#2103963说的就是physical connection -fatbean(胖豆 思想的海飞丝); 2005-2-1 (#2104363@0)
                • 见内 -pumpkinpie(南瓜饼); 2005-2-2 {224} (#2106607@0)
                  做过试验，真实project环境下实现过，如果使用oracle 自己的cursor,不管你是否在store procedure里关闭否，jdbc调用过后一律不关闭，因为jdbc conn 返回给connection pool时候并不物理断开connection.而只有真正物理断开连接，才能自动关闭。
                  • depends on oracle jdbc thin driver level. since 2, it provides an small internal connection pool; but life cycle is fast. -ra_95(闹钟-如果还有明天); 2005-2-2 (#2106617@0)
                    • go inside -pumpkinpie(南瓜饼); 2005-2-2 {250} (#2106625@0)
                      Only when conn.close(), jdbc tells oracle service port, this session is closed, and then oracle will close all the cursor opened by this conn. if return conn to connection pool, jdbc never tells oracle the session is closed, so the cursors keep open.
                      • if you get the connection using datasource from webserver, oracle won't shutdown the session after connection.close(). And my issue was solved by calling resultset.close() or connection.commit(). thanks -tongcd(MockingBird); 2005-2-2 (#2106725@0)
                • 这个close实际上是回收到connection pool 中去；不是真的close and release resource -ra_95(闹钟-如果还有明天); 2005-2-2 (#2106612@0)
                  • agree. the webserver's connection pool has mechanism to reuse the connection, but from the program's perspective, the connection.close() is good enough to release all used resultset, statement and pending transaction. -tongcd(MockingBird); 2005-2-2 (#2106727@0)
    • coming inside -changshan(偷偷乐); 2005-2-2 {1302} (#2106451@0)
      本文发表在 rolia.net 枫下论坛This may happen in several ways:
      1) If the communication only through the Java JDBC.
      possibly, check the java code for those resultset.close(), statement.close(), and conn.close(). whenever exception catch need to call all the close() in correct order
      2) In the DB side, you may need to check the instance init parameters for the maxium open cursor:
      use sqlplus: logon as the specific DBA user, type "show parameters cursor"


      NAME TYPE VALUE
      ------------------------------------ ----------- ------------
      cursor_sharing string EXACT
      cursor_space_for_time boolean FALSE
      open_cursors integer 300
      session_cached_cursors integer 0

      the default open_cursor is 300, this depends on how many cursors one user can open concurrently

      another inportant thing is the cursor_sharing, if it's "EXACT", it's not good, thich means "select a from tab1 where b=c" and "select a from tab1 where b=d" will open two mem location in the shared pool area.they do not share the parsed sql code. this may waste open cursor number too. ask your dba change it to "FORCE" to avoid develper non-binding variable sql and even case difference


      hope that helps!更多精彩文章及讨论，请光临枫下论坛 rolia.net
      • you may use this to check the db side info -changshan(偷偷乐); 2005-2-2 {49} (#2106455@0)
        select * from v$open_cursor where User_name=user;
        • yes, I used a similar script and found the statement that has been run over 4000 times without closing the resultset. Can you believe it?:) -tongcd(MockingBird); 2005-2-2 (#2106732@0)
      • thank you very much, it is so good to learn -tongcd(MockingBird); 2005-2-2 (#2106731@0)

More Topics

  • 脑洞大开，突发奇想，用力来传播信息
  • 看大家讨论日语，俺也正在认真学习。其实语言问题应该是很快也很容易就解决的，可能比自动驾驶来的还快。TREK里面，全宇宙的人都在大脑里安装了UNIVERSAL TRANSLATOR，外星人之间相互交流无障碍。废话少说，看视频。。。加上个MIC和耳机，不同国家的人就可以无障碍交流。
  • 肉脸真有搞 IT 的？Linux 把俄国中国伊朗踢出去了好像肉脸 IT 没啥感觉似的
  • 请问web developer和software developer技术上有什么区别？
  • Elon Musk 带着川普总统奔向火星了。
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论