问题

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

什么是open cursor

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

问题分析

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

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

UPDATE TEST SET TEST_STATUS=? WHERE TEST_ID=?

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

点击(此处)折叠或打开

ps = conn.prepareStatement(sql);
ps.setString(1, "ACTIVE");
rs = ps.executeQuery();
while (rs.next())
{
......
sql = "UPDATE TEST " + " SET TEST_STATUS=?"
+ " WHERE TEST_ID=? ";
ps = conn.prepareStatement(sql);
ps.setString(1, "EXPIRED");
ps.setString(2, testId);
ps.execute();
}
}

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;

执行一条语句：

点击(此处)折叠或打开

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

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

点击(此处)折叠或打开

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

再执行plsql(从asktom网站)

点击(此处)折叠或打开

declare
type rc is ref cursor;
l_cursor rc;
begin
for i in 1 .. 2
loop
for j in 1 .. 500
loop
open l_cursor for 'select * from dual xx' || i ||j;
close l_cursor;
end loop;
end loop;
--dbms_lock.sleep(100);
end;
/
SQL> select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
SID USER_NAME SQL_ID SQL_TEXT CURSOR_TYPE
---------- ---------- ------------- ------------------------------------------------------------ -----------------------------------
-
136 SYS 9mpxx61c3w43c select * from dual xx2472 DICTIONARY LOOKUP CURSOR CACHED
136 SYS g30bjwk32s70a select * from dual xx2460 DICTIONARY LOOKUP CURSOR CACHED
136 SYS c7pwjh6s9c8s1 select * from dual xx2451 DICTIONARY LOOKUP CURSOR CACHED
136 SYS b66u3729fh9f1 select * from dual xx2468 DICTIONARY LOOKUP CURSOR CACHED
136 SYS a3t3chatn0b3f select * from dual xx2496 DICTIONARY LOOKUP CURSOR CACHED
136 SYS 8z02s5f2v4cfu select * from dual xx2499 DICTIONARY LOOKUP CURSOR CACHED
...
136 SYS 04608tk6nzztd select * from dual xx2483 DICTIONARY LOOKUP CURSOR CACHED
已选择51行。

SQL> alter session set session_cached_cursors=200;

会话已更改。

再执行plsql(从asktom网站)

点击(此处)折叠或打开

declare
type rc is ref cursor;
l_cursor rc;
begin
for i in 1 .. 2
loop
for j in 1 .. 500
loop
open l_cursor for 'select * from dual xx' || i ||j;
close l_cursor;
end loop;
end loop;
--dbms_lock.sleep(100);
end;
/
SQL> select sid, user_name, sql_id, sql_text, cursor_type from v$open_cursor where sid = 136;
SID USER_NAME SQL_ID SQL_TEXT CURSOR_TYPE
---------- ---------- ------------- ------------------------------------------------------------ --------------------------------------------------------
136 SYS 6f0t5d14qs7as select * from dual xx2331 DICTIONARY LOOKUP CURSOR CACHED
136 SYS 70a87hvxd47uf select * from dual xx2364 DICTIONARY LOOKUP CURSOR CACHED
136 SYS c7pwjh6s9c8s1 select * from dual xx2451 DICTIONARY LOOKUP CURSOR CACHED
136 SYS 7z4gvyu48h982 select * from dual xx2410 DICTIONARY LOOKUP CURSOR CACHED
136 SYS b66u3729fh9f1 select * from dual xx2468 DICTIONARY LOOKUP CURSOR CACHED
136 SYS d7k4574uk8amr select * from dual xx2320 DICTIONARY LOOKUP CURSOR CACHED
136 SYS a3t3chatn0b3f select * from dual xx2496 DICTIONARY LOOKUP CURSOR CACHED
...
136 SYS 04608tk6nzztd select * from dual xx2483 DICTIONARY LOOKUP CURSOR CACHED
已选择201行。
declare
type rc is ref cursor;
l_cursor rc;
begin
for i in 1 .. 2
loop
for j in 1 .. 500
loop
open l_cursor for 'select * from dual xx' || i ||j;
-- close l_cursor;
end loop;
end loop;
--dbms_lock.sleep(100);
end;
/
select a.name, b.value
from v$statname a, v$mystat b
where a.statistic# = b.statistic#
and lower(a.name) like '%' || lower('cursor')||'%'
/
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 2122
opened cursors current 1
pinned cursors current 1
CCursor + sql area evicted 0
session cursor cache hits 81
session cursor cache count 200
cursor authentications 5
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 3124
opened cursors current 1
pinned cursors current 1
CCursor + sql area evicted 0
session cursor cache hits 82
session cursor cache count 200
cursor authentications 5
NAME VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative 4126
opened cursors current 1
pinned cursors current 1
CCursor + sql area evicted 0
session cursor cache hits 83
session cursor cache count 200
cursor authentications 5

ITPub博客

ORA-01000: maximum open cursors exceeded错误

最新文章