Souldn't it be v$sesstat, instead of v$sysstat?

Hi Tom ..

We Had Oracle Installed On Unix Server . but last time it crashed Due to Hardware Problem and we in rush shifted our system on to Windows NT . we exported everything but we are facing one problem

ORA-01000: maximum open cursors exceeded

when we run application which is in Forms 4.5 and try to update or insert records .
Oracle Version is 7.0 . i did read from some site saying increase the size of open_cursors in Init.Ora File , there was no open_cursors in the Init.Ora File . Does Oracle 7.0 Support it . should i add the following Line open_cursors , also how should determine whats the value of open_cursors .

thnx in advance.

Hi Tom,

1) Session_cache_cursor means the closed cache cursor. Am I right ? But from 9.2.0.5 the meaning of this parameter has changed. Could you please tell me the new meaning of this parameter ?

2) In 10g, is there any chance to impact ora-01000 by the new meaning of this parameter session_cache_cursor?

Thanks
Chandan

Hi Tom,

1) As per document 76684.1, there are unparsed (but opened) dynamic cursors and parsed (but opened) dynamic cursors. Could you please explain what is unparsed (but opened) dynamic cursors ?

2) Lets say I have just opened a ref cursor but have not fetched till now. Is it unparsed (but opened) dynamic cursors ?

3) Is there any mechanism or any event/trace which can be used to dump all cursors open in a session ?

Thanks
chandan

Hi Tom,

I am having the problem of ora-01000. That's why I am trying to know the followings :

1) As per document 76684.1, there are unparsed (but opened) dynamic cursors and parsed (but opened) dynamic cursors. Could you please explain what is unparsed (but opened) dynamic cursors ?

2) Lets say I have just opened a ref cursor but have not fetched till now. Is it unparsed (but opened) dynamic cursors ?

I will be highly obliged if you kindly answer the above 2 questions with example. I am just trying to understand properly.

Thanks
Chandan

Tom,
Thanks. You say : after looking at v$open_cursor : find the leak. Can you tell how to spot a leak? We are encountering ORA-1000 error. Howerver max value in v$sesstat does not exceed 300 when I monitor. The param OPEN_CURSORS is 1000. I think it is leak but how do we find it. I think after error nothing remains in v$open_cursor. Is there some place it goes. How to avoid increase OPEN_CURSORS. Thanks.

Hi Tom

I have the following setting in my 9206 database:

SQL> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     TRUE
open_cursors                         integer     1200
session_cached_cursors               integer     100

  1  select s.username, max(a.value)
  2  from v$sesstat a, v$statname b, v$session s
  3  where a.statistic# = b.statistic#
  4  and s.sid (+)= a.sid
  5  and b.name = 'opened cursors current'
  6* group by s.username
SQL>
USERNAME     MAX(A.VALUE)
------------ ------------
BATCHDBA               79
BATCHPR                17
BATCHSCD               12
BATCHSO                13
DNW                    82
DRPUSR                 10
DVUSER                 35
ICC_AIV                 2
INKDBA                 17
NCPM                    7
NETTING                14
OSBEAN_USER             5
OS_GENERIC              2
PPAP                   11
PRISWIM               200
RMS                    21
SICOMDBA                9
SUBCON                454
SYS                     2
TSWEB                 204
TUXLOG2                17
TUXPRIS                38
TUXSICOM              116
<b>VLOGDBA              1763</b>
WWW_BORNEO              2
                        7

26 rows selected.

We are using tuxedo middleware to connect this VLOGDBA to the database and currently there are 100s of sessions, but for this particular session when I try to figure out the open_cursors (from v$open_cursor), I donot find anything:

USERNAME     MACHINE                   VALUE TERMINAL                       SQL_ADDRESS      OSUSER
------------ -------------------- ---------- ------------------------------ ---------------- ------------------------------
VLOGDBA      eux012                     1763                                00               tuxlog2

I anticipate that this is an application side problem and cursors are not getting closed, but am not sure how to pin point the problem. Can you please guide?

Also, how can this value (1763) exceed open_cursors (1200)?

Best Regards
Jatin

In continuation to above, when I check v$open_cursor for this particular SID (142) I find only 81 rows.

I am not summing on username, infact it's a max(value) grouped by username. Anyways, it's not one username with multiple sessions, but a single session (sid=142) exceeding open_cursors value:

USERNAME                              SID      VALUE
------------------------------ ---------- ----------
..
..
..
TSWEB                                 592        164
TSWEB                                 844        172
PRISWIM                               960        200
TSWEB                                 875        204
SUBCON                                 92        746
<b>VLOGDBA                               142       1787</b>

1004 rows selected.

Could it be that we are using middle tier (tuxedo) for connection pooling and the above is showing a cumulative summation of connections taking over the same sid (142). But in that case too the max limit of 1200 should raise an ora-1000 error?

Please Guide.

ops$tkyte%ORA10GR2> create or replace package my_pkg
  2  as
  3          cursor c01 is select * from dual; cursor c02 is select * from dual; cursor c03 is select * from dual;
  4          cursor c04 is select * from dual; cursor c05 is select * from dual; cursor c06 is select * from dual;
  5          cursor c07 is select * from dual; cursor c08 is select * from dual; cursor c09 is select * from dual;
  6          cursor c10 is select * from dual; cursor c11 is select * from dual; cursor c12 is select * from dual;
  7          cursor c13 is select * from dual; cursor c14 is select * from dual; cursor c15 is select * from dual;
  8          cursor c16 is select * from dual; cursor c17 is select * from dual; cursor c18 is select * from dual;
  9          cursor c19 is select * from dual; cursor c20 is select * from dual; cursor c21 is select * from dual;
 10          cursor c22 is select * from dual; cursor c23 is select * from dual; cursor c24 is select * from dual;
 11          cursor c25 is select * from dual; cursor c26 is select * from dual; cursor c27 is select * from dual;
 12          cursor c28 is select * from dual; cursor c29 is select * from dual; cursor c30 is select * from dual;
 13          cursor c31 is select * from dual; cursor c32 is select * from dual; cursor c33 is select * from dual;
 14          cursor c34 is select * from dual; cursor c35 is select * from dual; cursor c36 is select * from dual;
 15          cursor c37 is select * from dual; cursor c38 is select * from dual; cursor c39 is select * from dual;
 16          cursor c40 is select * from dual;
 17  end;
 18  /

Package created.

ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 40
  3          loop
  4                  execute immediate 'begin open my_pkg.c' || to_char(i,'fm00') || '; end;';
  5          end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA10GR2> alter system set open_cursors = 30;

System altered.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> show parameter open_cursors;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     30
ops$tkyte%ORA10GR2> select s.username, max(a.value)
  2   from v$sesstat a, v$statname b, v$session s
  3  where a.statistic# = b.statistic#
  4  and s.sid (+)= a.sid
  5  and b.name = 'opened cursors current'
  6  group by s.username
  7  /

USERNAME                       MAX(A.VALUE)
------------------------------ ------------
                                         27
OPS$TKYTE                                44

ops$tkyte%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> begin
  2          for i in 1 .. 40
  3          loop
  4                  execute immediate 'begin open my_pkg.c' || to_char(i,'fm00') || '; end;';
  5          end loop;
  6  end;
  7  /
begin
*
ERROR at line 1:
ORA-01000: maximum open cursors exceeded
ORA-06512: at "OPS$TKYTE.MY_PKG", line 11
ORA-06512: at line 1
ORA-06512: at line 4

Hi Tom

There is no trace of an "alter system" command in alert log file for last 6 weeks. Moreover we have a strict change control and no one other than dba's are supposed to have/use alter system privilege. So, I believe that this assumption is not valid.

Regarding v$open_cursor, we have only 81 rows against this sid (=142) and 3541 against this username (as there are many sessions from this user).

I understand that v$open_cursor view is limited by session_cached_cursors and not open_cursors parameters (i.e. since session_cached_cursor is 100, my count (81) per session for open/closed cursors listed in this view cannot be > 100). Can you tell me what is your thought process for the reported issue when you ask me to check v$open_cussor?

Thanks for all the advice.

Regards
Jatin

ops$tkyte%ORA10GR2> create or replace procedure recursive( p_number in number )
  2  as
  3      l_cursor sys_refcursor;
  4  begin
  5      if ( p_number = 0 ) then
  6          for x in ( select a.name, b.value,
  7                                    (select count(*)
  8                               from v$open_cursor
  9                              where sid = (select sid
 10                                             from v$mystat
 11                                            where rownum = 1)) open_cursors
 12                       from v$statname a, v$mystat b
 13                      where a.statistic# = b.statistic#
 14                        and a.name = 'opened cursors current' )
 15          loop
 16              dbms_output.put_line( x.name || ' ' || x.value || ' v$open_cursor count = ' || x.open_cursors );
 17          end loop;
 18
 19
 20          return;
 21      end if;
 22      open l_cursor for select * from dual;
 23      recursive(p_number-1);
 24      close l_cursor;
 25  end;
 26  /

Procedure created.

ops$tkyte%ORA10GR2> set define on
ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> show parameter cursor

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing                       string      EXACT
cursor_space_for_time                boolean     FALSE
open_cursors                         integer     300
session_cached_cursors               integer     20
ops$tkyte%ORA10GR2> exec recursive( 200 )
opened cursors current 205 v$open_cursor count = 209

PL/SQL procedure successfully completed.

I agree for the reported issue, I'll check with metalink.

but regarding ..if they don't sync, then I trust v$open_cursors..

I read in manuals that v$open_cursors reports "open and parsed cursors" per session (not currently open but cumulatively per session, that's why we see the count in v$open_cursor > count in v$sesstat for 'opened cursors current')? My question is that how can I say that 81 (in this case) is total open + parsed right now.. may be it is accumulating for last several hrs. ? Please correct me if I am interpreting it wrong.

Second question I want to ask is regarding the caching of cursors. I understand that oracle moves the cursors from library cache to 'session cursor cache' if more than 3 parse requests have been issued for any statement (and we have set SESSION_CACHED_CURSORS to non zero value). So, does v$open_cursors lists these cursors as well from 'session cursor cache' or does it only list from library cache? (I read somewhere that SESSION_CACHED_CURSORS sets the number of cached closed cursors each session can have -- so it means it should not be reported in v$open_cursors view)?

Kindly answer.

Best Regards
Jatin

Hi Tom

I am just curious to know if too many truely opened cursors could lead to shared pool fragmentation, I have the understanding that if cursor is not closed by app,it will not be flushed out of shared pool. so too many of them could lead to fragmentation.
is that understanding correct?
thanks,

ops$tkyte%ORA11GR1> create table t ( x int );

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> @trace
ops$tkyte%ORA11GR1> alter session set events '10046 trace name context forever, level 12';

Session altered.

ops$tkyte%ORA11GR1> begin
  2          for i in 1 .. 10
  3          loop
  4                  insert into t values ( i );
  5                  if mod(i,3) = 0
  6                  then
  7                          execute immediate 'create index t_idx' || i || ' on t (x,' || i || ')';
  8                  end if;
  9          end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

INSERT INTO T VALUES ( :B1 )

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute     10      0.00       0.00          0          5         74          10
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       11      0.00       0.00          0          5         74          10

Misses in library cache during parse: 1
Misses in library cache during execute: 4

Great example,and it is very helpful..

Thanks, tom and it clears my doubts.

Hi Tom
We are using the ORACLE A.S 10g and D.B 10g (10.0.1). We maintain a pool of D.B connection on A.S. Our application is web based. We are properly closing the resultset and statement on java side. But we often receive the error open cursor exceeded. Currently on production we set the limit to 5000.
We use following query to check the currently open cursor
select a.sid, a.value, b.name, b.statistic#
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and a.sid = 555(any sid number)
The count of ‘open cursor’ is increasing it cannot be reduce until we restart our application server.
Because we are using the connection pooling that is why cursors are not closed?
Please guide us what should we do. What other areas we will look.
Thanks for your time

Hi tom
Thanks for pointing out to check the duplicate in open_cursor. Following are question regarding some of duplication occurring
a) One procedure is getting input parameter and returning number as out put parameter. In procedure the input parameter is directly using then why it is showing multiple time. Our understanding is that if in procedure we are using parameter then it is bind.
b) Following query is used in multiple triggers and is showing multiple time should not it be shown once SELECT SYS_CONTEXT('USERENV','HOST'),SYS_CONTEXT('USERENV','OS_USER'),
SYS_CONTEXT('USERENV','SESSION_USER'),SYS_CONTEXT('USERENV','IP_ADDRESS')
INTO V_HOST, V_OS_USER ,V_SESSION_USER, V_IP_ADDRESS FROM DUAL;
c) In trigger following is used and it is also appearing multiple times.
INSERT INTO Temp_A
(pk_id, desc, HOST,OS_USER,SESSION_USER,IP_ADDRESS,LOGGED_DATE,DML_STATEMENT )VALUES (SEQ_LOG_id.NEXTVAL, :N.desc,V_HOST,V_OS_USER,V_SESSION_USER,V_IP_ADDRESS,SYSTIMESTAMP,'INSERT');

Thanks

Hello friends -

(Did I double post somehow? Sorry!)

Hibernate 3.2 / C3P0 0.9 connecting to an Oracle 10g database.

I'm doing everyhing I know how to do in order to close my cursors out at an application level, and I keep on seeing the Max Cursors exceeded errors. I went ahead and set Hibernate logging to debug, a small portion of which I am attaching below. It sure tells me that it is closing my prepared statemetns and result sets.

Curiously, if you scan the log file you can see that the build up of cursors is gradual, and does sometimes decrease over time, just more of a two steps forward, one step back pattern; i.e.,

****************************************************
15:47:56,008 DEBUG AbstractBatcher:410 - about to open PreparedStatement (open PreparedStatements: 3, globally: 3)
15:47:56,008 DEBUG SQL:111 - select tinstantal0_.ACCT_ID as col_0_0_, tinstantal0_.PER_ID as col_0_1_, tinstantal0_.PREM_ID as col_0_2_, tinstantal0_.SP_ID as col_0_3_, tinstantal0_.SP_TYPE_CD as col_0_4_, tinstantal0_.ENTITY_NAME as col_0_5_, tinstantal0_.EMAILID as col_0_6_, tinstantal0_.PHONE as col_0_7_, tinstantal0_.PHONE_TYPE as col_0_8_, tinstantal0_.OD_OPTION_SW as col_0_9_, tinstantal0_.ODE_OPTION_SW as col_0_10_, tinstantal0_.ADDRESS1 as col_0_11_ from InstantAlert.T_INSTANT_ALERT_LOOKUP_VW tinstantal0_ where phone=?
15:47:56,040 DEBUG AbstractBatcher:426 - about to open ResultSet (open ResultSets: 3, globally: 3)
15:47:56,040 DEBUG IteratorImpl:113 - attempting to retrieve next results
15:47:56,040 DEBUG IteratorImpl:120 - retrieved next results
15:47:56,040 DEBUG IteratorImpl:133 - assembling results
15:47:56,040 DEBUG IteratorImpl:113 - attempting to retrieve next results
15:47:56,040 DEBUG IteratorImpl:120 - retrieved next results
15:47:56,040 DEBUG IteratorImpl:152 - returning current results
15:47:56,040 DEBUG IteratorImpl:133 - assembling results
15:47:56,040 DEBUG IteratorImpl:113 - attempting to retrieve next results
15:47:56,040 DEBUG IteratorImpl:120 - retrieved next results
15:47:56,040 DEBUG IteratorImpl:152 - returning current results
15:47:56,040 DEBUG IteratorImpl:133 - assembling results
15:47:56,040 DEBUG IteratorImpl:113 - attempting to retrieve next results
15:47:56,040 DEBUG IteratorImpl:120 - retrieved next results
15:47:56,040 DEBUG IteratorImpl:152 - returning current results
15:47:56,040 DEBUG IteratorImpl:133 - assembling results
15:47:56,040 DEBUG IteratorImpl:113 - attempting to retrieve next results
15:47:56,040 DEBUG IteratorImpl:120 - retrieved next results
15:47:56,040 DEBUG IteratorImpl:152 - returning current results
15:47:56,040 DEBUG IteratorImpl:133 - assembling results
15:47:56,040 DEBUG IteratorImpl:113 - attempting to retrieve next results
15:47:56,040 DEBUG IteratorImpl:120 - retrieved next results
15:47:56,040 DEBUG IteratorImpl:152 - returning current results
15:47:56,040 DEBUG IteratorImpl:133 - assembling results
15:47:56,040 DEBUG IteratorImpl:113 - attempting to retrieve next results
15:47:56,040 DEBUG IteratorImpl:116 - exhausted results
15:47:56,040 DEBUG IteratorImpl:86 - closing iterator
15:47:56,040 DEBUG AbstractBatcher:433 - about to close ResultSet (open ResultSets: 4, globally: 4)
15:47:56,040 DEBUG AbstractBatcher:418 - about to close PreparedStatement (open PreparedStatements: 4, globally: 4)
******************************************************

Followed shortly there after:

******************************************************
15:47:56,118 DEBUG AbstractBatcher:410 - about to open PreparedStatement (open PreparedStatements: 3, globally: 3)
15:47:56,118 DEBUG SQL:111 - select tinstantal0_.ACCT_ID as ACCT1_20_, tinstantal0_.PER_ID as PER2_20_, tinstantal0_.PREM_ID as PREM3_20_, tinstantal0_.SP_ID as SP4_20_, tinstantal0_.SP_TYPE_CD as SP5_20_, tinstantal0_.ENTITY_NAME as ENTITY6_20_, tinstantal0_.EMAILID as EMAILID20_, tinstantal0_.PHONE as PHONE20_, tinstantal0_.PHONE_TYPE as PHONE9_20_, tinstantal0_.OD_OPTION_SW as OD10_20_, tinstantal0_.ODE_OPTION_SW as ODE11_20_, tinstantal0_.ADDRESS1 as ADDRESS12_20_ from InstantAlert.T_INSTANT_ALERT_LOOKUP_VW tinstantal0_ where sp_id=? and (phone_type=? or phone_type=? or phone_type=?) order by length(phone_type)
15:47:56,133 DEBUG AbstractBatcher:426 - about to open ResultSet (open ResultSets: 3, globally: 3)
15:47:56,133 DEBUG Loader:1197 - result row: EntityKey[us.fl.ci.tlh.uu.notification.data.cis.TInstantAlertLookup#component[acctId,perId,premId,spId,spTypeCd,entityName,emailid,phone,phoneType,odOptionSw,odeOptionSw,address1]{spTypeCd=E-GS-ND, phone= 422-1009, odOptionSw=N, premId=0844034610, odeOptionSw=N, phoneType=OLD PHONE1, acctId=6137085610, perId=1278743610, address1=623 Arbor Station Ln, spId=0844034454, emailid= , entityName=Arbor Station Apts}]
15:47:56,149 DEBUG AbstractBatcher:433 - about to close ResultSet (open ResultSets: 4, globally: 4)
15:47:56,149 DEBUG AbstractBatcher:418 - about to close PreparedStatement (open PreparedStatements: 4, globally: 4)
15:47:56,149 DEBUG TwoPhaseLoad:130 - resolving associations for [us.fl.ci.tlh.uu.not}]
15:47:56,149 DEBUG TwoPhaseLoad:226 - done materializing entity [u]
15:47:56,149 DEBUG StatefulPersistenceContext:860 - initializing non-lazy collections
15:47:56,149 INFO TInstantAlertLookupDAO:219 - Iterating through list of numbers
15:47:56,149 INFO TInstantAlertLookupDAO:333 - about to parse out customer names
15:47:56,149 DEBUG AbstractFlushingEventListener:134 - processing flush-time cascades
15:47:56,149 DEBUG AbstractFlushingEventListener:177 - dirty checking collections
15:47:56,149 DEBUG AbstractFlushingEventListener:108 - Flushed: 0 insertions, 0 updates, 0 deletions to 44 objects
15:47:56,149 DEBUG AbstractFlushingEventListener:114 - Flushed: 0 (re)creations, 0 updates, 0 removals to 0 collections
15:47:56,149 DEBUG Printer:106 - listing entities:
15:47:56,149 DEBUG Printer:113 - us.fl.ci.tlh.uu.notification.data.cis.TInstantAlertLookup{ LINES REMOVED TO SCRUB OUT SENSITIVE INOFORMATION.}
15:47:56,165 DEBUG Printer:110 - more......
15:47:56,165 DEBUG AbstractBatcher:410 - about to open PreparedStatement (open PreparedStatements: 3, globally: 3)
15:47:56,165 DEBUG SQL:111 - select tinstantal0_.ACCT_ID as col_0_0_, tinstantal0_.PER_ID as col_0_1_, tinstantal0_.PREM_ID as col_0_2_, tinstantal0_.SP_ID as col_0_3_, tinstantal0_.SP_TYPE_CD as col_0_4_, tinstantal0_.ENTITY_NAME as col_0_5_, tinstantal0_.EMAILID as col_0_6_, tinstantal0_.PHONE as col_0_7_, tinstantal0_.PHONE_TYPE as col_0_8_, tinstantal0_.OD_OPTION_SW as col_0_9_, tinstantal0_.ODE_OPTION_SW as col_0_10_, tinstantal0_.ADDRESS1 as col_0_11_ from InstantAlert.T_INSTANT_ALERT_LOOKUP_VW tinstantal0_ where phone=?
15:47:56,180 DEBUG AbstractBatcher:426 - about to open ResultSet (open ResultSets: 3, globally: 3) -- SEE THINGS ARE SORT OF GETTING CLOSED OFF AS EXPECTED SOMETIMES!
********************************************************


Eventually, however, I reach my breaking point; i.e.,

*******************************************************
15:52:11,430 DEBUG AbstractBatcher:410 - about to open PreparedStatement (open PreparedStatements: 300, globally: 300)
15:52:11,430 DEBUG SQL:111 - select tinstantal0_.ACCT_ID as ACCT1_20_, tinstantal0_.PER_ID as PER2_20_, tinstantal0_.PREM_ID as PREM3_20_, tinstantal0_.SP_ID as SP4_20_, tinstantal0_.SP_TYPE_CD as SP5_20_, tinstantal0_.ENTITY_NAME as ENTITY6_20_, tinstantal0_.EMAILID as EMAILID20_, tinstantal0_.PHONE as PHONE20_, tinstantal0_.PHONE_TYPE as PHONE9_20_, tinstantal0_.OD_OPTION_SW as OD10_20_, tinstantal0_.ODE_OPTION_SW as ODE11_20_, tinstantal0_.ADDRESS1 as ADDRESS12_20_ from InstantAlert.T_INSTANT_ALERT_LOOKUP_VW tinstantal0_ where sp_id=? and (phone_type=? or phone_type=? or phone_type=?) order by length(phone_type)
15:52:11,524 DEBUG AbstractBatcher:418 - about to close PreparedStatement (open PreparedStatements: 301, globally: 301)
15:52:11,524 DEBUG JDBCExceptionReporter:92 - could not execute query [select tinstantal0_.ACCT_ID as ACCT1_20_, tinstantal0_.PER_ID as PER2_20_, tinstantal0_.PREM_ID as PREM3_20_, tinstantal0_.SP_ID as SP4_20_, tinstantal0_.SP_TYPE_CD as SP5_20_, tinstantal0_.ENTITY_NAME as ENTITY6_20_, tinstantal0_.EMAILID as EMAILID20_, tinstantal0_.PHONE as PHONE20_, tinstantal0_.PHONE_TYPE as PHONE9_20_, tinstantal0_.OD_OPTION_SW as OD10_20_, tinstantal0_.ODE_OPTION_SW as ODE11_20_, tinstantal0_.ADDRESS1 as ADDRESS12_20_ from InstantAlert.T_INSTANT_ALERT_LOOKUP_VW tinstantal0_ where sp_id=? and (phone_type=? or phone_type=? or phone_type=?) order by length(phone_type)]
java.sql.SQLException: ORA-01000: maximum open cursors exceeded

at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:187)
at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:241)
at oracle.jdbc.ttc7.Oopen.receive(Oopen.java:118)
at oracle.jdbc.ttc7.TTC7Protocol.open(TTC7Protocol.java:498)
at oracle.jdbc.driver.OracleStatement.open(OracleStatement.java:678)
at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2499)
at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:452)
at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:382)
at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
at org.hibernate.loader.Loader.getResultSet(Loader.java:1812)
at org.hibernate.loader.Loader.doQuery(Loader.java:697)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
at org.hibernate.loader.Loader.doList(Loader.java:2232)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
at org.hibernate.loader.Loader.list(Loader.java:2124)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1149)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
at us.fl.ci.tlh.uu.notification.persistence.cis.TInstantAlertLookupDAO.populateExternalNotificationBasedOnSpid(TInstantAlertLookupDAO.java:208)
at us.fl.ci.tlh.uu.notification.business.ExternalNotificationBusinessHelper.populateNotificationsWithCisInformation(ExternalNotificationBusinessHelper.java:312)
at us.fl.ci.tlh.uu.notification.quartz.scheduledtasks.electric.GetOutageInformation.execute(GetOutageInformation.java:111)
at org.quartz.core.JobRunShell.run(JobRunShell.java:216)
*************************************************************

Bam! This isn't a one to one; i.e., I can usually process around 3,000 records before I hit the magical 300 value for number of allowable open cursors. Based on other comments in this thread, I scanned for other errors, but it doesn't look as if anything is being reported, I certaily have catch / logging blocks in the affeced areas.

Is there any chance I could start to accumulate open cursors if I did something like this:

Iterator it = theQueryIRun.iterate();

while (it.hasNext()){
if (checkForCondition()){
break;
}

}

i.e., if I don't loop through everyhing in my result set? There are some data specific patterns that cause my code to occassionally break out from the loop. Are these unevaluated result sets, perhaps the source of my open cursors?

Any insight is appreciated!

brian

not good ......ohhhhahhh

ORA-01000: Maximum Open Cursors Exceeded"

i am not able to connect sys user in this situation. then how i will change the open_cursor parameter value

Hi Tom,
Hope you are doing good.
I have a one doubt please to be clarification anyone.
I have one table maximum how many cursor use for inside table.


thanks,
Durairaj Gopal

Greetings!

Application encountered this ORA-01000: maximum open cursors exceeded error in the previous night. Is there any way to find out the session and the actual SQL caused caused this error. ?

Currently, database parameter - open_cursors setting is 500. I suspect application may not be closing the cursor after it is done with processing on Java side and may be causing this cursor leak.

I started off with this query but it gives me the total count as of that point in time from AWR snapshot. I want to know the session and the SQL as well ?

SQL> l
  1* select snap_id, dbid, INSTANCE_NUMBER, STAT_ID, STAT_NAME, VALUE  from  dba_hist_sysstat where stat_name = 'opened cursors current' and value > 400 order by snap_id
SQL> /

   SNAP_ID       DBID INSTANCE_NUMBER    STAT_ID STAT_NAME                           VALUE
---------- ---------- --------------- ---------- ------------------------------ ----------
     76646 2522763952               2 2301954928 opened cursors current                429
     76705 2522763952               2 2301954928 opened cursors current                431
     76742 2522763952               2 2301954928 opened cursors current                434
     76779 2522763952               2 2301954928 opened cursors current                408
     76797 2522763952               2 2301954928 opened cursors current                412

SQL>

Hello all,
We are running 19.24 cdb/pdb on rhel8.
Please forgive some terminology mistakes, still getting up to speed on EFCore.
One of application is using the new ef core, i forget the version but im pretty sure its the newest due to a bug in the prior release not closing cursors. The app .net team said the sql is wrote dynamically, which when monitoring the session it was reusing sql so I don't think there is a cursor leak but still digging.
Also, the number of cursors opened increase the more files that are dropped off.
My question is, I started with open cursor at 500 the app cooked through them, setting session cursor cache did boost performance and let it get through more files but still hit the max. I set open cursor to 2000 and still hit the max at 230 files, it has 5k files needing processed. This is the first load so it wouldn't be the normal.
What are the risks of setting open cursor to 10k?
It was set to 50 prior to this new implementation so other apps in this instance are minimal concern.
Thanks
Dave