Q: Tools and Monitor maximum open cursors

Thread view:

Ken1 - 01 Apr 2004 10:14 GMT

I am using oracle 8.1.7 and are maintaining a rather
large system which were developed without closing
ResultSets and Statements in a correct way.

Sometimes we get the ORA-01000 maximum opened
cursors exceeded., but this happens suddenly
sometimes after days and sometimes after months
depending of what functions are used in the system.

As the system contains hundreds of classes and
hundreds of dynamic generated web pages the task
of fixing all the code takes a long time.

Is there anyone who have done the same task and have
written any tools that search though java code
to find these places where either or both ResultSets
and Statements or not closed.

I am also wondering if anyone have written any tools
that can supervise the connections and report, either
to log or by e-mail when the maximum opened cursors
are "near" to happen and report the session/connections
currently opened cursors SQL statements so these can be
fixed?

/Kenneth

Reply to this Message

Robert Klemme - 01 Apr 2004 13:04 GMT

> I am using oracle 8.1.7 and are maintaining a rather
> large system which were developed without closing
[quoted text clipped - 8 lines]
> hundreds of dynamic generated web pages the task
> of fixing all the code takes a long time.

Did you check the code of the generator that generated all those web
pages? Or are you talking about JSP?

> Is there anyone who have done the same task and have
> written any tools that search though java code
> to find these places where either or both ResultSets
> and Statements or not closed.

I guess that's quite difficult since in worst case the close could be in
another method and thus it would be very hard to find it.

I would have to think about this a bit more, but it could be feasible to
write a wrapping JDBC layer that tracks ResultSet creations (together with
a stack trace) and issues a warning if the item is not closed after n
seconds. It is a bit of effort but not too difficult, just lots of
methods to implement. It might be the most feasible thing to do in your
situation. And you can generate most of the delegate methods
automatically (Eclipse can do that e.g.).

> I am also wondering if anyone have written any tools
> that can supervise the connections and report, either
> to log or by e-mail when the maximum opened cursors
> are "near" to happen and report the session/connections
> currently opened cursors SQL statements so these can be
> fixed?

Difficult...

Regards

robert

Reply to this Message

Andree Große - 02 Apr 2004 09:43 GMT

> I am using oracle 8.1.7 and are maintaining a rather
> large system which were developed without closing
[quoted text clipped - 5 lines]
> depending of what functions are used in the system.
> ...

You have to fix your source over all your DB-classes.
Note that every Statement-Object holds an Cursor on
SQL-Server-Side. Be sure to close Statement-Objects
every time as follows:

Statement stmt = null;
try {
stmt = con.createStatement(...); //or whatever
} catch (SQLexception e) {
...
} finally {
if (stmt != null) {
try {
stmt.close();
} catch (Exception e) {}
}
}

If there is a ResultSet under the Statement-Object it
will be closed automatically.

HTH A.G.

Reply to this Message

winslave - 06 Apr 2004 18:26 GMT

I would recommend source navigator for such a task.
http://sourcenav.sourceforge.net/

> > I am using oracle 8.1.7 and are maintaining a rather
> > large system which were developed without closing
[quoted text clipped - 28 lines]
>
> HTH A.G.

Reply to this Message

Q: Tools and Monitor maximum open cursors

Free Magazines