1

I am using Matlab 2018a to connect to an Oracle DB via JDBC. oracle version is 11.2.

My program is an "engine" that activates a series of user written funcions.

If the number of functions is large I might get an "ORA-01000 maximum open cursors exceeded" error.

currently I'm handling this issue by closing and opening the DB connection but this is a big setback for performance.

What I thought of doing is to get the cursers table before and after the function and just closing the cursors that were opened by the function I ran.

So I extracted the V$SESSION and V$OPEN_CURSORS tables and was suprised to find a bunch of open cursers from my session.

I'm not explicitly opening cursors and also the functions I run does not open cursors explicitly but we are using function that execute SQL queries.

So:

  1. How do I know which cursors were opened by the function I ran? Does cursors have unique IDs that I should use to discriminate them?
  2. Should I only look at the open cursors or should I also be worried about closed cursors? Can they suddenly become open cursors?

Thanks!

DvirH
  • 70
  • 8
  • 1
    The correct solution to this bug , is to properly close all `Statement` and `ResultSet` instances in your code. Typically using a "try with resources" block, or at least a `finally` block. –  Sep 12 '18 at 08:32
  • Please post a [MCVE] including an example of how the functions are connecting to the database and running queries .... trying to interrogate Oracle's internal tables and figure out which cursors you've opened from that and then close them is the wrong approach to take. Instead you should be looking at making sure your functions are reading and closing the cursors and, if you are using a shared pool of connections, returning the connection to the pool. – MT0 Sep 12 '18 at 08:38

1 Answers1

0

How do I know which cursors were opened by the function I ran?

Well the below query will tell you what are the open cursors(you can sepecify username)

select a.value, s.username, s.sid, s.serial#    
from v$sesstat a, v$statname b, v$session s    
where a.statistic# = b.statistic#  and s.sid=a.sid    
and b.name = 'opened cursors current';

Then from the sid you can know the query.

select * from v$sqlarea where sql_id in(
select case when sql_id is null then prev_sql_id else sql_id end  from v$session where sid in (
select  s.sid
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current'
and username='usersomething')
)

Does cursors have unique IDs that I should use to discriminate them?

Well when a select is executed a cursor is opened, the above query will tell you which queries have opened cursor. There are 2 type of cursors implicit cursor and explicit , by running a select 1 from dual this an implicit cursor that oracle open and close it , the explicit is when you declare a cursor (with select) that you open and close (well if you use for .. in ..loop it will it close by it self)

(As per my understanding and with some test I did, when you close the connection oracle will close the cursors)

2-Should I only look at the open cursors or should I also be worried about closed cursors? Can they suddenly become open cursors?

the above errors limits related to the open cursors, a cursor can be opened when a query is being executed again. so you have to check for the open cursors.

you can pass by this error ORA-01000 maximum open cursors exceeded by increasing the number of cursors alter system set open_cursors = 400 scope=both ( by default its seems 50 oracle doc )

this answer has great explanation about reasons behind ORA-01000: maximum open cursors exceeded and some way to resolve it. java.sql.SQLException: - ORA-01000: maximum open cursors exceeded

Moudiz
  • 7,211
  • 22
  • 78
  • 156