I’'m using Jive Messenger 2.1.3 and Oracle 10.1.0.4. When deleting many users I got ORA-01000. I checked opened cursors in the database with the following query
select user_name, b.status, osuser, machine, a.sql_text
from gv$session b,
gv$open_cursor a
where a.sid = b.sid
I found a lot open cursors deleting rows from jive tables.
That cursor doesn’'t appear to have anything to do with Jive Messenger. Could another process be using up all the cursors in your database?
Regards,
Matt
The problem happened deleting about 100 users through the Jive Admin Console.
Not sure if this has any relevance to the topic but we are using Oracle 10.1.0.3 over here and I’'ve done load tests that will (through the Smack API) creates 10k users, runs them through testing, and then deletes all 10k users. I have never run into the cursor problems.
Granted in your situation you are talking about going through the admin console. If the problem truely is in the admin console, it should take you but a few seconds to find it. Just look for any ResultSet or PreparedStatement (hopefully they aren’'t using normal Statements) and make sure that every instance of those that are opened are closed. And that they are closed before the connection is closed.
For optimization reasons of the oracle 10g system, the connection.close() does not automatically always close down all the prepared statements and especially not the result sets since the database now has a bunch of internal logic that knows how to reuse those things for other connections as well if it detects they are frequent enough to warrent caching.