I was trying to help a coworker track down the causes of an ORA_01000 (maximum open cursors exceeded) error that he is investigating. We were reviewing some information that we obtained from metalink (Note:76684.1) that explains the values that can be fetched from the V$OPEN_CURSORS and V$SYSSTAT tables. I understand the different types of queries (implicit, explicit and dynamic) and how various programmatic errors can prevent these types of cursors from being closed. So far so good.

Then he asked me a question for which I couldn't answer or help him find the answer. How do you determine the number of dynamically parsed but not open cursors in another database session? I was able to explain to him how you could join V$OPEN_CURSORS to V$SESSION to determine the rows in V$OPEN_CURSORS for a particular session for a particular user. We were able to monitor V$SYSSTAT in another session and watch the total number of open cursors grow as the errors progressed. But, since dynamically parsed but unopened queries do not show up in V$OPEN_CURSOR, how do we find them? The number of them would be nice; seeing the text of those cursors would be great.

We are debugging the code being run within our application, so it really isn't that easy to stop half way through and look at these values in the V$ tables from within the application's database session. I'd hate to have to tell him that he must put code in the application to query these tables at various points in the code executed during the workflow and check them there. It sure would be nice to be able to query for them in another session.

Also, we have many developers and users accessing the database at all times, so it would be difficult to kick everyone else out and only have ourselves connected to the database.

Any suggestions?

Thanks in advance,

Heath