Re: too many cursors open

On Mar 14, 12:02 pm, DA Morgan <damor..._at_psoug.org> wrote:
> dean wrote:
> > Oracle 10g, Windows.
>
> > We are getting a periodic error in an application that says we are
> > opening too many cursors, and I am trying to debug this. Is there a
> > SQL call to get a count of this number of cursors, so that one can see
> > exactly which part of the app is doing the mischief? Or is there
> > another approach? We don't see the error in our test machines, only at
> > a client site.
>
> > Any help appreciated.
>
> > Dean
>
> SQL> select object_name from dba_objects
> 2 where object_name like 'GV%CURSOR%';
>
> OBJECT_NAME
> -----------------------------------------
> GV_$SQL_SHARED_CURSOR
> GV$SQL_SHARED_CURSOR
> GV_$OPEN_CURSOR
> GV$OPEN_CURSOR
> GV_$SESSION_CURSOR_CACHE
> GV$SESSION_CURSOR_CACHE
> GV_$SYSTEM_CURSOR_CACHE
> GV$SYSTEM_CURSOR_CACHE
> GV_$SQL_CURSOR
> GV$SQL_CURSOR
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

Thanks for that. I was looking at data from the following query: select count(*), SQL_TEXT
from sys.v_$open_cursor
where SID = 67
group by SQL_TEXT
order by count(*) desc

and I could not find anything that seemed to be a cursor leak. I compared the OPEN_CURSORS variable on our test machines with the client one and they are both set to 300, yet we don't have an issue. Is there some flavor of auto-extend cursors, or some other setting, that can interfere with this setting? I checked the spfile and the running values, both of them 300. Received on Wed Mar 14 2007 - 15:57:06 CDT