Skip to Main Content
  • Questions
  • ORA-01000: maximum open cursors exceeded

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Azman.

Asked: May 22, 2001 - 11:12 pm UTC

Last updated: June 28, 2023 - 12:55 pm UTC

Version: 8.1.6

Viewed 100K+ times! This question is

You Asked

Hi Tom,
I running Oracle 8.1.6 under Solaris 2.6.

We are using third-party application server and one common user-id to login to the server. We currently encountering the above error messages and are having difficulty in identifying the program that caused this.

Our open_cursors is set to 3000.

We know that the view v$open_cursor is helpful in listing out all the cursors, however it also includes dynamic cursors PARSED and NOT CLOSED. So it is difficult for us to track the actual curson that is sill open.

On the other hand v$sysstat where statistic# = 3 only provides the no. of currently opened cursor.


Is there a script that can be run to determine how many open cursors
exist and which program is causing the open cursors?

Regards

and Tom said...

use v$sysstat to identify sessions with more then 2,900 open cursors -- then, since you have that sid, you can goto v$open_cursor and see the cursors. You KNOW that 2900 of the 3000 they have in there are OPEN cursors -- the fact that 100 of them might be logically closed in the application isn't relevant anymore -- you can see the 2900 that are OPENED in the application (that the application is "leaking")

followup to comment one below

correct -- that was a typo. should be V$SESSTAT to get the session level counts of open cursors. thanks.

Rating

  (21 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

ORA-01000: maximum open cursors exceeded

Prince Kumar, June 11, 2001 - 5:06 pm UTC

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

ORA-01000: maximum open cursors exceeded

Sam, October 04, 2005 - 8:14 am UTC

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.


Tom Kyte
October 04, 2005 - 4:24 pm UTC

yes, it did - way way back when.

what was your old setting, that would be the one to use.

ora-01000

chandan, April 30, 2008 - 6:14 pm UTC

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
Tom Kyte
April 30, 2008 - 8:56 pm UTC

1) session_cached_cursors controls two things from 9205 on

a) the size of the plsql cursor cache - when you say "close cursor" in plsql, it doesn't close the cursor, it caches it open. The size of that cache is limited by this parameter. prior to 9205 - that cache was limited instead by open_cursors

b) the size of the cache the client program can maintain with soft pointers into the SGA to make opening a closed cursor "faster" (what I've called a softer soft parse)

2) no, not anymore than in 9i or 11g - it doesn't really affect that, that is not what it is about. It controls the size of the plsql cursor cache and the client "softer soft parse" cache - neither of which will incur the ora-1000 max open cursors exceeded. (the plsql cursor cache will empty itself before allowing the session to raise that, the session cursor cache is just a set of soft pointers into the SGA, they do not occupy an open cursor slot in the session)

ora-01000

chandan, May 01, 2008 - 12:50 pm UTC

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
Tom Kyte
May 01, 2008 - 3:01 pm UTC

just query v$open_cursor

do you have a specific program/situation you are trying to solve here? Maybe we can cut to the chase and give you what you need instead of going back and forth and back and forth.

ora-01000

chandan, May 01, 2008 - 8:06 pm UTC

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 Kyte
May 01, 2008 - 9:53 pm UTC

1) do you use dbms_sql, that is what that is all about

2) that is just an open cursor

query v$open_cursor, look for the "leak"

How to spot a leak

Ajoshi, August 11, 2009 - 11:59 pm UTC

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.
Tom Kyte
August 13, 2009 - 9:16 am UTC

... I think after error nothing
remains in v$open_cursor. ...

only if the application closes that session - otherwise they would be there.

You might have to sample v$open_cursor from time to time to observe a "leak" happening.

value in v$sesstat exceeds open_cursors

Jatin, November 30, 2009 - 10:48 pm UTC

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
Tom Kyte
December 01, 2009 - 3:42 am UTC

you are summing over username?

Easy to answer: This username has more than one session, each session has less then 1200 open cursors.

Add to your query the sid, session id, to see counts BY SESSION, not by user.

In continuation to above...

Jatin, November 30, 2009 - 11:40 pm UTC

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

Its max not sum

Jatin, December 01, 2009 - 4:13 am UTC

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.
Tom Kyte
December 01, 2009 - 10:20 am UTC

sorry, you are correct, it was max - not sum() - apologize


I am not aware of anything personally regarding tuxedo and this - but it is possible that someone changed the open cursors after these were open

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




and yes, it should raise the error if these were opened after the change:

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



but it could be a quirk of XA and Tuxedo together - You might ping support to see if there are any known issues, I could not find any in a quick search.

Does a count in v$open_cursor correspond to that number? Do you really see that many open cursors?

No trace Of alter system

Jatin, December 01, 2009 - 11:27 pm UTC

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
Tom Kyte
December 02, 2009 - 6:53 am UTC

... I understand that v$open_cursor view is limited by session_cached_cursors ...

no it isn't. sessoin_cached_cursors limits the number of cursors PL/SQL will cache open (as of 9.2.0.5 and above). But it does not limit the number of cursors that can be open in v$open_cursor, open_cursors does that.


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.



My thought process (and yes, I see now you told me 81 before, but I tend to not re-read the entire thread, I go on what was last said) was "did they sync up, if not, I trust v$open_cursor"

I'll refer you to support to see if they can dig up any XA related issues to the sesstat view.

I don't think you are exceeding open cursor, I think the statistic is for whatever reason incorrectly reported.

yet another doubt..

jatin, December 03, 2009 - 5:28 am UTC

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
Tom Kyte
December 04, 2009 - 2:59 pm UTC

v$open_cursor has cursors that are opened - it might be one from the plsql cursor cache, but they are open (to be parsed, you have - a cursor handle, that cursor is open)

session cached cursors lets us do a softer soft parse. When you close the cursor and we decide to 'session cache it', we keep a link to the object in the shared pool, but the cursor is not open. When you reparse that sql text again, we reference that link to the shared pool (bypassing the conventional parse of the string, the semantic parse of the resulting parse tree and the hashing to find the sql in the shared pool) and verify that the object we used last time is still there and usable. And yes, you might see these in v$open_cursor.


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

i'm not entirely sure how to answer that - I'm not sure why you find the 81 number unusual or remarkable.

open cursors lead to shared pool fragmentation?

jianhuang zheng, December 03, 2009 - 7:56 am UTC

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,
Tom Kyte
December 04, 2009 - 3:20 pm UTC

... is that understanding correct? ...

no, it is not.

Just because a program has a cursor open does not mean it cannot be invalidated and flushed from the shared pool. Happens all of the time.

For example:

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.



tkprof says:

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


we had to hard parse the first time, we parsed a total of one times in the application, but we really parsed FOUR MORE times during the execution of that insert statement - because we forced the sql to become invalid.

That sql could have been flushed out by some other "bad" program as well - one that filled the shared pool with lots of literal (no bind) sql and filled it all of the way up and forced things to be flushed out.

Thanks,

A reader, December 05, 2009 - 3:53 am UTC

Great example,and it is very helpful..

very helpful.

jianhuang zheng, December 05, 2009 - 3:55 am UTC

Thanks, tom and it clears my doubts.

waseem, May 18, 2011 - 7:01 am UTC

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

Tom Kyte
May 18, 2011 - 10:19 am UTC

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.


I'll beg to differ with you on that. Those three facts do not align with each other.

Instead of simply counting cursors - query v$open-cursor and look for 'duplicates', those will be the cursors you are leaking.

You are leaking them (probability 99.999999999999%) due to bad exception handling. You've opened a cursor in a method, you hit an error - you fly out of that method (hence losing the handle to the cursor) and catch the exception at a higher level. You would need to catch the exception down at that method, release the cursor and throw the exception again.

open cursor exceeded

A reader, May 19, 2011 - 5:23 am UTC

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

Tom Kyte
May 19, 2011 - 8:40 am UTC

a) you get a duplicate if you explicitly open a cursor with a given sql text AND THEN open another cursor with that same text and so on.

give me an example to work with here. By "procedure" do you mean plsql? show me some sample code

b) each trigger would have its own cursor handle, that is ok here. plsql caches cursors PERFECTLY, if you are using static sql in plsql - you can ignore those in v$open_cursor. Look for your applications sql.

the cursors plsql holds open will be closed if you run out of slots - they are not the problem. Look for your APPLICATION sql, that will be the problem - look for sql that is NOT static sql in plsql.

Hibernate / C3P0 / Oracle 10G / Cursor Madness

brian, July 21, 2011 - 3:19 pm UTC

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
Tom Kyte
July 22, 2011 - 2:04 pm UTC

query v$open-cursor, it'll show you what is still open.

You have an exception block somewhere in your code that is leaking a cursor handle, I've seen it so often that I'm 99.999999999% sure this is the case.


have a routine that you can call to dump out to your debug log a list of cursors you have open. call this routine frequently - and in your error log, you should see the list of cursors that are still open and growing.


(also, how is your statement caching setup for your jdbc driver? could it be to blame?)

Review

samcha, January 18, 2013 - 2:12 am UTC

not good ......ohhhhahhh

A reader, February 12, 2014 - 11:53 am UTC

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

how many cursors in use for table

Durairaj, December 16, 2020 - 8:25 am UTC

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
Connor McDonald
December 17, 2020 - 4:40 am UTC

Tables and cursors are not related.

I could have 100,000 tables in my database and have very few cursors.

I could have 1 table in my database, and have 1000's of cursors.

Cursors are more or less synonymous with the number of queries you have active in your application.

find the actual SQL caused ORA-01000 error

Paul, June 28, 2023 - 4:24 am UTC

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>

Chris Saxon
June 28, 2023 - 12:55 pm UTC

I don't know if there's a way to find this historically. In any case AWR & ASH are based on samples, so there's no guarantee they would capture the info you need.

Plus the statement that triggered the error may not be the one that caused it. If apps are failing to close cursors, then arguably it's the older, still open cursors that are the problem. Not the 500th to be opened.

As discussed in this thread, use v$sesstat to find current sessions with lots of cursors open, then query v$open_cursor for these sessions to find what they are. If this is an ongoing problem, set up something to alert you when a session is reaching the limit (e.g. 400+ open cursors) & investigate.