| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: maximum open cursors exceeded
Craig:
Could you tell me what kind of tools are you using?
Thanks,
Eric
-----Original Message-----
Sent: Tuesday, April 22, 2003 5:17 PM
To: Multiple recipients of list ORACLE-L
Hi,
If your application is written in Java with JDK 1.3.1 or higher I have a tool that will help you find the cursors that have been left open. So far I have had excellent feedback on the tool for solving these types of problems - I would be interested in your feedback.
Cheers,
Craig.
-----Original Message-----
Sent: Tuesday, 15 April 2003 8:19 PM
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
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
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: Craig Munday INET: Craig.Munday_at_ecard.com.au 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: Fang, Bingbiao (Eric) INET: bfang_at_caiso.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 22 2003 - 20:06:38 CDT
 |
 |