open cursors problem, please help

[chikkodi]

My users are getting Max Open Cursors Exceeded error.

I went through all the stored procedures and made sure that all the cursors are closed properly.

I queried v$open_cursor, as "select sqltext from v$open_cursor"
It not only gave the cursors in the stored procedure I just executed, but also some other sql statements present in the stored procedure.

Does it mean that each sql statement in the stored procedure corresponds to a cursor ? How can we close them then ?

Then I searched this forum, and in one of the earlier threads, it was discussed that this view will give even the sqls that were parsed, hence the number is not accurate.

What contributes to the total cursors present ? Is it the value in v$open_cursors ? If so, how can we flush these sql statements off so that the total number of open cursors remains low ?

Please help

[sambavan]

Check this thread on how to deal with the open cursor problems. I'v posted on the relationsships and the limits on this.
[url]http://www.dbasupport.com/forums/showthread.php?threadid=7116[/url]

You need to bump the OPEN_CURSOR parameter to solve the problem. Then you have to make sure that your applications are closing the cursors promptly. Then you can set the profile on user connections that would be removed if they were to be idle. These are some of the ways to remove the sessions, which inturn remove the cursors.

Sam

[anujpathak]

increase the value of open_cursor in init.ora file


from Anuj