Re: maximum open cursors exceeded

Sorry, but very simple: You program forgets to close cursors. More likely, the program attempts to reuse a cursor, but in stead of doing this properly, it will create a new cursor. If you see that

select sid,hash_value,count(*)
from v$open_cursor
group by sid,hash_value
having count(*) > 10 /* or some other value bigger than 1 */ order by count(*)

has many rows with large count(*), it is likely that your application forgets to close or to properly reuse cursors, although it CAN be done on purpose by the programmer.

/Bjørn.

MURAT BALKAS wrote:

>
>
>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.
>
>* init.ora parameters
>
>open_cursors = 300
>
>* SELECT COUNT (*) FROM sys.V_$OPEN_CURSOR WHERE user_name ='myuser'
>
> The value is : 2566
>
>* SELECT count( distinct address) FROM sys.V_$OPEN_CURSOR WHERE user_name
>='myuser'
>
> The value is : 11
>
>* SELECT * FROM V$SESSION_CURSOR_CACHE
>
>MAXIMUM COUNT OPENED_ONCE OPEN OPENS HITS HIT_RATIO
>300 0 0 0 0 0 1
>
>* SELECT * FROM V$SYSTEM_CURSOR_CACHE
>
>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.
>--------------------------------------
>
>
>
>

-- 
Bjørn Engsig, Miracle A/S
Member of Oak Table Network <http://www.oaktable.net>
Bjorn.Engsig@MiracleAS.dk - http://MiracleAS.dk


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: =?ISO-8859-1?Q?Bj=F8rn_Engsig?=
  INET: bjorn_at_miracleas.dk

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