ITPub博客

首页 > 数据库 > Oracle > ORA-01000: maximum open cursors exceeded错误

ORA-01000: maximum open cursors exceeded错误

原创 Oracle 作者:dbs101 时间:2011-05-30 17:20:43 0 删除 编辑

问题

今天遇见java.sql.SQLException: ORA-01000: maximum open cursors exceeded。


什么是open cursor

OPEN CURSOR是指一个session同时打开的cursor数目:

问题分析

1.首先看看哪些session用了最多的cursor。

点击(此处)折叠或打开

  1. @getnumberopencursor.sql
  2. set linesize 120
  3. col machine format a30
  4. set heading on
  5. select o.sid, osuser, machine, count(*) num_curs
  6. from v$open_cursor o, v$session s
  7. where user_name = '&username' and o.sid=s.sid
  8. group by o.sid, osuser, machine
  9. order by  num_curs desc;


2.查看是不是同样的语句。

点击(此处)折叠或打开

  1. select * from v$open_cursor where sid = 695 order by sql_text


下面的这条update语句执行了很多次。

点击(此处)折叠或打开

  1. UPDATE TEST SET TEST_STATUS=? WHERE TEST_ID=?


3.开发查了查代码,发现有一段循环代码调用,并在循环中创建PreparedStatement.

点击(此处)折叠或打开

  1.        ps = conn.prepareStatement(sql);
  2.        ps.setString(1, "ACTIVE");
  3.        rs = ps.executeQuery();
  4.        while (rs.next())
  5.        {
  6.    ......
  7.               sql = "UPDATE TEST " + "   SET TEST_STATUS=?"
  8.                      + " WHERE TEST_ID=? ";
  9.               ps = conn.prepareStatement(sql);
  10.               ps.setString(1, "EXPIRED");
  11.               ps.setString(2, testId);
  12.               ps.execute();
  13.            }
  14.        }


4. java对象和oracle对象的对应关系
Connection 对应oracle 的session
Statement 对应oracle的cursor

5. 测试v$open_cursor的变化
col user_name format a10
select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;

执行一条语句:

点击(此处)折叠或打开

  1. select distinct sid from v$mystat SQL> select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
  2.        SID USER_NAME  SQL_ID        SQL_TEXT                                                     CURSOR_TYPE
  3. ---------- ---------- ------------- ------------------------------------------------------------ ---------------------------------
  4.        136 SYS        7d3s4yhub46g3 select * from test where rownum <20                          OPEN
  5.        136 SYS        6ubd0c60r1tr9 select distinct sid from v$mystat                            DICTIONARY LOOKUP CURSOR CACHED
  6.        136 SYS        459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME'      DICTIONARY LOOKUP CURSOR CACHED
  7.        136 SYS        7fqyv7yaqusur SELECT /* OPT_DYN_SAMP */


再执行一条语句:select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;


点击(此处)折叠或打开

  1. SQL> select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
  2.        SID USER_NAME  SQL_ID        SQL_TEXT                                                     CURSOR_TYPE
  3. ---------- ---------- ------------- ------------------------------------------------------------ -----------------------------------
  4.        136 SYS        6ubd0c60r1tr9 select distinct sid from v$mystat                            OPEN
  5.        136 SYS        459f3z9u4fb3u select value$ from props$ where name = 'GLOBAL_DB_NAME'      DICTIONARY LOOKUP CURSOR CACHED
  6.        136 SYS        7fqyv7yaqusur SELECT /* OPT_DYN_SAMP */



再执行plsql(从asktom网站)

点击(此处)折叠或打开

  1. declare
  2.             type rc is ref cursor;
  3.  
  4.             l_cursor rc;
  5. begin
  6.             for i in 1 .. 2
  7.             loop
  8.                     for j in 1 .. 500
  9.                     loop
  10.                           open l_cursor for 'select * from dual xx' || i ||j;
  11.                           close l_cursor;
  12.                   end loop;
  13.           end loop;
  14. --dbms_lock.sleep(100);
  15. end;
  16. /
  17. SQL> select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
  18.        SID USER_NAME  SQL_ID        SQL_TEXT                                                     CURSOR_TYPE
  19. ---------- ---------- ------------- ------------------------------------------------------------ -----------------------------------
  20. -
  21.        136 SYS        9mpxx61c3w43c select * from dual xx2472                                    DICTIONARY LOOKUP CURSOR CACHED
  22.        136 SYS        g30bjwk32s70a select * from dual xx2460                                    DICTIONARY LOOKUP CURSOR CACHED
  23.        136 SYS        c7pwjh6s9c8s1 select * from dual xx2451                                    DICTIONARY LOOKUP CURSOR CACHED
  24.        136 SYS        b66u3729fh9f1 select * from dual xx2468                                    DICTIONARY LOOKUP CURSOR CACHED
  25.        136 SYS        a3t3chatn0b3f select * from dual xx2496                                    DICTIONARY LOOKUP CURSOR CACHED
  26.        136 SYS        8z02s5f2v4cfu select * from dual xx2499                                    DICTIONARY LOOKUP CURSOR CACHED
  27. ...
  28.        136 SYS        04608tk6nzztd select * from dual xx2483                                    DICTIONARY LOOKUP CURSOR CACHED
  29. 已选择51行。



SQL> alter session set session_cached_cursors=200;

会话已更改。

再执行plsql(从asktom网站)

点击(此处)折叠或打开

  1. declare
  2.             type rc is ref cursor;
  3.  
  4.             l_cursor rc;
  5. begin
  6.             for i in 1 .. 2
  7.             loop
  8.                     for j in 1 .. 500
  9.                     loop
  10.                           open l_cursor for 'select * from dual xx' || i ||j;
  11.                           close l_cursor;
  12.                   end loop;
  13.           end loop;
  14. --dbms_lock.sleep(100);
  15. end;
  16. /
  17. SQL> select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
  18.        SID USER_NAME  SQL_ID        SQL_TEXT                                                     CURSOR_TYPE
  19. ---------- ---------- ------------- ------------------------------------------------------------ --------------------------------------------------------
  20.        136 SYS        6f0t5d14qs7as select * from dual xx2331                                    DICTIONARY LOOKUP CURSOR CACHED
  21.        136 SYS        70a87hvxd47uf select * from dual xx2364                                    DICTIONARY LOOKUP CURSOR CACHED
  22.        136 SYS        c7pwjh6s9c8s1 select * from dual xx2451                                    DICTIONARY LOOKUP CURSOR CACHED
  23.        136 SYS        7z4gvyu48h982 select * from dual xx2410                                    DICTIONARY LOOKUP CURSOR CACHED
  24.        136 SYS        b66u3729fh9f1 select * from dual xx2468                                    DICTIONARY LOOKUP CURSOR CACHED
  25.        136 SYS        d7k4574uk8amr select * from dual xx2320                                    DICTIONARY LOOKUP CURSOR CACHED
  26.        136 SYS        a3t3chatn0b3f select * from dual xx2496                                    DICTIONARY LOOKUP CURSOR CACHED
  27. ...
  28.        136 SYS        04608tk6nzztd select * from dual xx2483                                    DICTIONARY LOOKUP CURSOR CACHED
  29. 已选择201行。
  30. declare
  31.             type rc is ref cursor;
  32.  
  33.             l_cursor rc;
  34. begin
  35.             for i in 1 .. 2
  36.             loop
  37.                     for j in 1 .. 500
  38.                     loop
  39.                           open l_cursor for 'select * from dual xx' || i ||j;
  40. --                          close l_cursor;
  41.                   end loop;
  42.           end loop;
  43. --dbms_lock.sleep(100);
  44. end;
  45. /
  46. select a.name, b.value
  47. from v$statname a, v$mystat b
  48. where a.statistic# = b.statistic#
  49. and lower(a.name) like '%' || lower('cursor')||'%'
  50. /
  51. NAME                                                                  VALUE
  52. ---------------------------------------------------------------- ----------
  53. opened cursors cumulative                                              2122
  54. opened cursors current                                                    1
  55. pinned cursors current                                                    1
  56. CCursor + sql area evicted                                                0
  57. session cursor cache hits                                                81
  58. session cursor cache count                                              200
  59. cursor authentications                                                    5
  60. NAME                                                                  VALUE
  61. ---------------------------------------------------------------- ----------
  62. opened cursors cumulative                                              3124
  63. opened cursors current                                                    1
  64. pinned cursors current                                                    1
  65. CCursor + sql area evicted                                                0
  66. session cursor cache hits                                                82
  67. session cursor cache count                                              200
  68. cursor authentications                                                    5
  69. NAME                                                                  VALUE
  70. ---------------------------------------------------------------- ----------
  71. opened cursors cumulative                                              4126
  72. opened cursors current                                                    1
  73. pinned cursors current                                                    1
  74. CCursor + sql area evicted                                                0
  75. session cursor cache hits                                                83
  76. session cursor cache count                                              200
  77. cursor authentications                                                    5




来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/25105315/viewspace-696667/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-18

  • 博文量
    92
  • 访问量
    438031