Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: maximum open cursors exceeded

RE: maximum open cursors exceeded

From: Goulet, Dick <DGoulet_at_vicr.com>
Date: Tue, 15 Apr 2003 06:03:38 -0800
Message-ID: <F001.005814BC.20030415060338@fatcity.com>


Murat,

        Have you tried increasing the shared pool size? I remember reading a MetaLink note about max open cursors that said that it may return that same error message when in reality it could not acquire sufficient shared pool space to handle the request.

Dick Goulet
Senior Oracle DBA
Oracle Certified 8i DBA

-----Original Message-----
Sent: Tuesday, April 15, 2003 6:19 AM
To: Multiple recipients of list ORACLE-L

Hi,

      again the same problem. Following results are from the while I do not get any errors. Sometimes, I get ORA-01000: maximum open cursors exceeded. Every query made by the application leaves an open cursor.

What's the problem?

Thanks in advance...

Murat

P.S : I'm not the developer of the application. I can only change the sql statements and have changed every statement to use bind variables.

open_cursors = 300

      The value is : 2566

      The value is : 11

MAXIMUM     COUNT       OPENED_ONCE OPEN  OPENS HITS  HIT_RATIO
300         0           0                 0     0     0     1

OPENS       HITS        HIT_RATIO
736760      736760      1


* SELECT USER_NAME, A.SID, NVL(OSUSER,'UNKNOWN'), NVL(MACHINE,'*'),
NVL(PROGRAM, 'UNKNOWN'), COUNT(B.SID) FROM V$SESSION A, SYS.V_$OPEN_CURSOR B WHERE A.SADDR = B.SADDR GROUP BY USER_NAME, A.SID, OSUSER, MACHINE, PROGRAM;

USER_NAME SID NVL(OSUSER,'UNKNOWN') NVL(MACHINE,'*') NVL(PROGRAM,'UNKNOWN') COUNT(B.SID)

myUSER      10    oracle      jdbcclient  JDBC-1.0-Client   274
myUSER      28    oracle      jdbcclient  JDBC-1.0-Client   276
myUSER      46    oracle      jdbcclient  JDBC-1.0-Client   246
myUSER      68    oracle      jdbcclient  JDBC-1.0-Client   243
myUSER      82    oracle      jdbcclient  JDBC-1.0-Client   273
myUSER      85    oracle      jdbcclient  JDBC-1.0-Client   266
myUSER      88    oracle      jdbcclient  JDBC-1.0-Client   240
myUSER      99    oracle      jdbcclient  JDBC-1.0-Client   245
myUSER      109   oracle      jdbcclient  JDBC-1.0-Client   271
myUSER      113   oracle      jdbcclient  JDBC-1.0-Client   237


* select * from v$sesstat where statistic#=3 AND Sid in (
SELECT A.SID FROM V$SESSION A, SYS.V_$OPEN_CURSOR B WHERE A.SADDR = B.SADDR and user_name = 'MYUSER' GROUP BY USER_NAME, A.SID, OSUSER, MACHINE, PROGRAM
  )

SID STATISTIC# VALUE

10    3     274
28    3     276
46    3     247
68    3     243
72    3     1
82    3     273
85    3     266
88    3     241
99    3     245
109   3     271
113   3     237


* select v.sid,
v.serial#, v.username, v.status, count(*) from v$session v, v$open_cursor c

where v.sid = c.sid AND v.username='MYUSER' group by v.sid, v.serial#, v.username, v.status
SID   SERIAL#     USERNAME    STATUS      COUNT(*)
10    1172  MYUSER      INACTIVE    274
28    5620  MYUSER      INACTIVE    276
46    20004 MYUSER      INACTIVE    247
68    212   MYUSER      INACTIVE    243
72    33546 MYUSER      INACTIVE    1
82    18511 MYUSER      INACTIVE    274
85    50896 MYUSER      INACTIVE    266
88    18614 MYUSER      INACTIVE    241
99    1132  MYUSER      INACTIVE    245
109   5387  MYUSER      INACTIVE    271
113   11210 MYUSER      INACTIVE    237


http://www.telsim.com.tr



Bu e-posta sadece yukarida isimleri belirtilen kisiler arasinda ozel haberlesme amacini tasimaktadir. Size yanlislikla ulasmissa lutfen mesaji geri gonderiniz ve sisteminizden siliniz.Telsim Mobil Telekomunikasyon Hizmetleri A.S. bu mesajin icerigi ile ilgili olarak hic bir hukuksal sorumlulugu kabul etmez.

This e-mail communication is intended for the private use of the persons named above. If you received this message in error, please immediately notify the sender and delete it from your system.Telsim Mobil Telekomunikasyon Hizmetleri A.S. does not accept legal responsibility for the contents of this message.


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: MURAT BALKAS
  INET: murat.balkas_at_o2.net.tr

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_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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Goulet, Dick
  INET: DGoulet_at_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_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).
Received on Tue Apr 15 2003 - 09:03:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US