| Open cursor limit exceeded [message #146110] |
Tue, 08 November 2005 13:05  |
w9510055
Messages: 2
Registered: November 2005
|
Junior Member |
|
|
Hi,
I am using Oracle8.1.7 JDBC thin driver to access Oracle8.0.5 database; and am currently experiencing the "Open cursor limit exceeded" problem.
I am pooling connections created by DrvierManager, not using the javax.sql package. I do not use autocommit for connections. In the java code, I make sure the resultset and statement are closed after each transaction, and the connection is committed before it is put back into the pool. Still the open-cursors on each pooled connection keep growing, until reaching the server limit of 2048.
I tried the java.sql.Connection.setHoldability(). Oracle8.1.7 JDBC seems not support this method.
Anybody knows how to prevent a connection (session)'s open cursors from growing? Will the Oracle implementation of javax.sql package makes the situation better?
Thanks,
Harry
|
|
|
|
| Re: Open cursor limit exceeded [message #146113 is a reply to message #146110] |
Tue, 08 November 2005 13:23  |
w9510055
Messages: 2
Registered: November 2005
|
Junior Member |
|
|
How do you read this,
SQL> select count(*) from v$open_cursor;
COUNT(*)
---------
396
SQL> select sum(value) from v$sesstat v, v$statname s where v.statistic# = s.statistic# and s.name like '%opened cursors current%';
SUM(VALUE)
----------
1027
Which number does Oracle use to raise the "Open cursor exceeds limit" error ?
|
|
|
|
| Re: Open cursor limit exceeded [message #150858 is a reply to message #146110] |
Sun, 11 December 2005 06:54 |
maoz
Messages: 1
Registered: December 2005
|
Junior Member |
|
|
Hi,
I'm facing similar problem here (oracle 8.1.7 database).
The number of allowed opened cursors is per session, therefor you can see the most 'loaded' session using the following query (the first row is the one you need):
select sid, count (*)
from v$open_cursor
group by sid
order by count (*) desc;
In general, an Oracle system admin can clear the sql_area once in a while, and this way avoid the 'Open cursor exceeds limit" error, but there must be some way to prevent it from the code.
Maoz
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
