RE: open cursors...

Try this....

select a.sql_text
from v$session s, v$open_cursor o, v$sqlarea a where

s.saddr=o.saddr and
s.sid=o.sid and
o.address=a.address and
o.hash_value=a.hash_value and
s.schemaname='????';

Or this......

select value, name from v$sysstat where statistic# in (2,3);

HOWEVER - I currently have an open tar with Oracle regarding a "bug" with Oracle 7.3.4 not cleaning up it's cursors even though they have been closed. OH, and FYI, I can replicate the problem in 8, 8.1.5. and 8.1.6.  So, check the code and make sure they are implicitly closing the cursors.  If they are then you might want to "call Oracle" ha ha ha!!! If you wish, I will contact you should they come up with a solution but I warn you it might be awhile...since the work around is raising the number of open_cursors. Let's just say it's not on their priority list.

take care,

Christine Turner
DBA
IPS Sendero
Scottsdale, Arizona

From: 	Suzy Vordos[SMTP:lvordos_at_uswest.com]
Reply To: 	ORACLE-L_at_fatcity.com
Sent: 	Wednesday, May 31, 2000 2:30 PM
To: 	Multiple recipients of list ORACLE-L
Subject: 	open cursors...

Is there a way to identify which users(s) have an open cursor? We are frequently seeing "max open cursors exceeded", open_cursors is set to 400. Sure, we can increase open_cursors, but I think the source is bad application code, just not sure which user is executing it. The only user_name I've found in v$open_cursor is SYS.

Thanks,
Suzy

--
Author: Suzy Vordos
  INET: lvordos_at_uswest.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).