Sep 13, 2006

ORA-1000 MAXIMUM NUMBER OF CURSORS EXCEEDED after Java application migrated

ORA-1000 MAXIMUM NUMBER OF CURSORS EXCEEDED
in Java application orcurred after application migrated for iPlanet to JBoss infrastructure.

It is a very very common error message. I remember I was asked this question in a job interview a couple of years ago. But I cannot remember what I explained. Anyway I got the offer. Ok, I'd like to give it a summary, even I still have a question left.

--
--Troubleshooting Steps:
--
1. Login database as DBA to check current setting of open_cursors
> show parameter open_cursors

2. Check who open too many cursors

set line 140
col username format a16
col machine format a26
SELECT vs.username, vs.sid,vs.serial#, vs.machine,Count(*) "Open_Cursors"
FROM v$open_cursor voc, v$session vs
WHERE voc.sid= vs.sid
GROUP BY vs.username, vs.sid,vs.serial# ,vs.machine
ORDER BY 5;

3. Get the entire queries

SELECT vst.HASH_VALUE, vst.SQL_TEXT, vst.PIECE, Count(vst.SQL_TEXT)
FROM v$open_cursor voc, v$sqltext vst, v$session vs
WHERE voc.sid= vs.sid
AND vs.username='CMGWAOE'
and voc.HASH_VALUE=vst.HASH_VALUE
and voc.ADDRESS=vst.ADDRESS
GROUP BY vst.HASH_VALUE,vst.SQL_TEXT ,vst.PIECE
/

4. Narrow down where these queries are called or from.

5.Review Java code with developer.

Make sure to close "ResultSet" objects before closing "PreparedStatement"/"CallableStement" objects.

if forgot to close the "ResultSet", some java application server(JBoss) may not have ability to close the (Oracle) cursors when close statement.(iPanet does)
If connection pooling is using, this issue may get even worse.

6. After enhance java code, problem went away.

But further question is, why child cursors still are open in shared-pool after parent cursor closed???