×

Loading...
Ad by
  • 予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！
Ad by
  • 予人玫瑰，手有余香：加拿大新天地工作移民诚聘求职顾问&行业导师！

@Calgary

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

  • 老程序员第一次用copilot的一点点体会
  • 现在社区游泳池招救生员巡逻，过一会还要不停的点人头，这工作是否很快就被AI代替了，装一个摄像头后面是AI处理系统，这样只招一个等着救人就行了，人头沉下水一定时间立马强光照射，还可以分析水下人的状况来判断是否溺水等
  • 新冠疫苗引发心肌炎? 最新研究这样看
  • 这道几何题有解吗?
  • SAM 2 Demo
    • 枫下论坛主坛 /  工作学习 /  IT技术讨论