Return-Path: Received: from ensim.rackshack.net (root@localhost) by orafaq.net (8.11.6/8.11.6) with ESMTP id h3EHtoF12079 for ; Mon, 14 Apr 2003 12:55:50 -0500 X-ClientAddr: 209.68.248.164 Received: from newsfeed.cts.com (newsfeed.cts.com [209.68.248.164]) by ensim.rackshack.net (8.11.6/8.11.6) with ESMTP id h3EHtnA12073 for ; Mon, 14 Apr 2003 12:55:49 -0500 Received: from fatcity.com (uucp@localhost) by newsfeed.cts.com (8.9.3/8.9.3) with UUCP id IAA83211; Mon, 14 Apr 2003 08:35:07 -0700 (PDT) Received: by fatcity.com (26-Feb-2001/v1.0g-b72/bab) via UUCP id 005806FD; Mon, 14 Apr 2003 07:58:38 -0800 Message-ID: Date: Mon, 14 Apr 2003 07:58:38 -0800 To: Multiple recipients of list ORACLE-L X-Comment: Oracle RDBMS Community Forum X-Sender: "Goulet, Dick" Sender: root@fatcity.com Reply-To: ORACLE-L@fatcity.com Errors-To: ML-ERRORS@fatcity.com From: "Goulet, Dick" Subject: RE: Open Cursors, again Organization: Fat City Network Services, San Diego, California X-ListServer: v1.0g, build 72; ListGuru (c) 1996-2001 Bruce A. Bergman Precedence: bulk Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit Richard, True, you did not suggest a query, but I had one that I retrofitted with your suggestion: SELECT USERNAME, A.SID, NVL(OSUSER,'UNKNOWN'), NVL(MACHINE,'*'), NVL(PROGRAM, 'UNKNOWN'), value FROM V$SESSION A, v$sesstat c WHERE value > 0 and a.sid = c.sid and statistic# = 3 GROUP BY USERNAME, A.SID, OSUSER, MACHINE, PROGRAM; When I ran this in one of my database with a known cursor limit of 100 I had a number of rows that said there where 300+ cursors open for that session. Obviously something it amiss!! Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -----Original Message----- Sent: Friday, April 11, 2003 6:34 PM To: Multiple recipients of list ORACLE-L That might be me. :) I mentioned v$sesstat but I didn't provide any query. Which query did you use? Note, statistic# 2 is cumulative and 3 is current. -----Original Message----- Sent: Friday, April 11, 2003 3:19 PM To: Multiple recipients of list ORACLE-L Folks, This last week we had a thread on open cursors running around. I try to keep my mailbox clean to stay under my limits, so I've lost them thread. Anyway someone suggested using V$sesstat instead on V_$OPEN_CURSORS because it was sometimes in error. Well I think someone may have made a mistake as well. The open cursor limit on my DB is 100, but this query returned a value of 204 for a particular user who is not having a problem. On the other hand getting into V_$OPEN_CURSORS gives me a value of 2, which makes all the sense in the world. I've a funny feeling that V$SESSTAT is holding the cumulative number of cursors, not the current ones. Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: DGoulet@vicr.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Ji INET: richard.ji@mobilespring.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: DGoulet@vicr.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru@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).