LarsBlog

I recently had to troubleshoot a ORA-01000 error in a WebSphere application.  It seems Oracle has a limit of (usually 1000) cursors that can be open for a single session, and once this limit is reached, the ORA-01000 error is thrown.  A cursor is like an iterator that loops through a result set, so, generally, this error is caused by JDBC resources not being closed properly after use, often in a loop (eg: PreparedStatements, ResultSets).  

During the process of troubleshooting this problem I discovered some useful Oracle SQL.


Firstly, it is useful to know what this per-session cursor limit is for your database:

--retrieve current max_open_cursors setting
select p.value as max_open_cur from v$parameter p where p.name= 'open_cursors'

Secondly, it can be useful to see the total number of cursors in use by each session to see if any have hit this limit.  Note that not all of these sessions are necessarily still active - there may be ones in there that Oracle hasn't yet bothered to clean up even though they're not active anymore.

--total cursors open, by session
select a.value as open_cursors, s.status, s.machine, s.username, s.sid, s.serial#, s.type, s.event
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 s.schemaname = 'MY_SCHEMA'
order by serial# desc

Or, alternatively, see what the maximum number of opened cursors has been for any session:

-- open_cursors vs maximum
select max(a.value) as highest_open_cur, p.value as max_open_cur
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic# and b.name = 'opened cursors current'
and p.name= 'open_cursors' group by p.value;

Finally, in order to be able to re-create such a problem more easily to test a code fix, it can be useful to set your cursor limit to something lower than the default:

--set max open_cusors - Default usually 1000
alter system set OPEN_CURSORS=1000

Don't forget to change it back when you're done!

Useful link: http://www.orafaq.com/node/75