Skip to Main Content
  • Questions

  • Erorr ORA-1000, maximum open cursors exceeded.

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Charanjiv .

Asked: May 02, 2000 - 1:21 pm UTC

Last updated: April 26, 2003 - 11:47 am UTC

Version:

Viewed 1000+ times

You Asked

Erorr ORA-1000, maximum open cursors exceeded.
We are running a VC++ application on a Oracle 7.3 server and are
gettting this erorr. We increased the parameter from its default to 200.
But we would like to know how can we find out the noumber and
text of the opem cursors. ?
If i am right open cursors would be uncommited statements ?

Thanx
Charanjiv





and Tom said...

No, open cursors would be cursors you OPENed but never closed or they would be cached cursors that plsql keeps open (if you are using lots of stored procedures, you'll see the cached cursors from these). Cursors stay open across commits normally.

You can see the cursors you have open (all open cursors in the database) by reviewing the dynamic performance view
v$open_cursors. You can use this to track down cursors you have opened in your application but have forgotten to close or to verify the cursors are being cached by plsql for you.

I keep my open cursors set at about 1,000. Cursor entries are aquired 64 at a time, regardless of the open cursor setting, so setting to a high number will not affect memory consumption for all clients -- just those that need it.

See also the documentation for "close_cached_open_cursors" in the server reference guide if you really want plsql to close cached cursors upon commit (this parameter is obsoleted in 8.1 -- a new algorithm is used and this is no longer needed). I don't recommend setting this but some people want to anyway.

followup to comment one below

well, it varies by language.

in jdbc, preparedStatement.close() does it.

In pro*c EXEC SQL CLOSE CURSOR_NAME; does it.

In OCI -- there is an API call to close a statement

and so on.

followup to comment three

No, no it is not.

Open cursors are not uncommitted statements. Open cursors are -- lo and behold -- cursors you have not closed.

If you are in JDBC and you let a statement handle go out of scope (don't have any idea what a loop might have to do with it except that I could use a loop to exhaust open cursors rapidly), you will have open cursors that you can no longer get to to close.

But the same would be true if you opened a file and didn't close it properly. Just consider a statement a file, eventually, when you are done and will never need that file again -- close it. Same with the cursors (statements). When you are done (and not before, if you might reuse it, don't close it) with the statement -- close it and you will not have this error.

This error is 100% caused by a programmer leaking cursors, if you replace the database statements with file opens, you would be running out of file descriptors -- same issue.

Do NOT close each query after it is executed, you should prepare a statement at most ONCE per program execution and then rebind/execute it over and over. If there is a chance that you will execute the statement again, keep it open (don't let it go out of scope!)

See
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1478004815829 <code>
for why the advice to close after each query is executed is potentially bad advice.




Rating

  (11 ratings)

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

Comments

Kavitha, July 23, 2001 - 8:02 am UTC

The explanation is very clear and to the point. But what I can't find here is information on how to clear the open cursors. If that is added to this, then it would be great!

still receive "max cursors open" error

Michael, July 23, 2001 - 8:27 am UTC

I have a question about pro*c cursors under SUN OS 5.6 and Oracle 8.1.5.
I write a pro*c program to implement authentication ,after every user logon DB,immediate execute an sql select (EXEC SQL SELECT ....) to identify his status.I have set OPEN_CURSORS=300 in DB ,but if I have more than 100 users logon,I still receive "max cursors open" error. And I have set HOLD_CURSOR=NO and RELEASE_CURSOR=YES in pro*c config file,have no benefit.
Can I only increase OPEN_CURSORS?

Yes it is

ShyamKumar Reddy, July 24, 2001 - 5:46 am UTC

In JDBC
If you run your query in Loops.
And not closing the Statement Object. This error will occur. So after each query is completed Please close the Statement Object. This error is avoided



MAX open cursors

G.V.N., April 24, 2003 - 4:58 pm UTC

Tom,
We are getting the following error when connecting from a oracle 9i DB (Verson 9.2) to the oracle report server(Reports builder ver. 6.08).
Erorr ORA-1000, maximum open cursors exceeded

My questions
1)Does the value of open_cursors in the oracle ini file include implict cursors?
2)How can I find out he numbers of cursors used by a particular report?


Tom Kyte
April 24, 2003 - 6:15 pm UTC

1) it covers all cursors -- inside the kernel there is no such thing as implicit or explicit -- that is purely a coding thing. it counts all open cursors.

2) look in v$sesstat for open_cursors

How to find out # of open cursors?

Saminathan Seerangan, April 25, 2003 - 12:37 am UTC 

Dear Tom, 

How to find out the number of open cursors?

For example, i set open_cursors value to 10 but # of records in v$open_cursor view is 51. 

SQL> show parameter open_cursors
NAME                  TYPE        VALUE
--------------------- ----------- -------------------
open_cursors          integer     10

SQL> select count(*) from v$open_cursor;
  COUNT(*)
----------
        51

SQL> select count(distinct sql_text) from v$open_cursor;

COUNT(DISTINCTSQL_TEXT)
-----------------------
                     26

COUNT(DISTINCTSADDR)
--------------------
                   8

COUNT(DISTINCTHASH_VALUE)
-------------------------
                       26

Tom, Could you please explain little bit? 

TIA

Thanks Tom, Got the answer from this forum itself

Saminathan Seerangan, April 25, 2003 - 1:26 am UTC

open_cursor only for connected session
=======================================
select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3;

all the open cursors for the database
=====================================
select sum(a.value), b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by b.name;

MAX open cursors

G.V.N., April 25, 2003 - 9:47 am UTC

1) If the open_cursors in the oracle ini file is set to 50 that means a session can use a max of 50 cursors. The value of open_cursors in the ini file is for a particular session - right?
2) Every select statement in a report or
every statements like
for i in (select ... from table_name) ....adds to the number of open cursors
Is that right?
3) If I have a procedure as shown below (copied from one of your other listings)

set timing on
declare
l_dummy dual.dummy%type;
begin
for i in 1 .. 10000
loop
select dummy into l_dummy from dual this_is_implicit;
end loop;
end;
/

How many cursors are opened? Is it 1 or 10000
Since implict cursors are opened, fetched and closed automatically - We should use no more than 1 cursor, Is that right?

Is there a way for a developer(not DBA) to find out the number of cursosrs used? If we have to query the tables v$sesstat, v$statname you would either need to have DBA privileges or select privilege on these tables


Thank you


Tom Kyte
April 25, 2003 - 3:20 pm UTC

1) correct
2) yes and no. that select in your example is only open inside the cursor for loop. it is closed outside of it.
3) 2 -- one for the plsql block itself and one for select dummy from dual.


you need select privs on those objects if you want that information.

MAX open cursors

G.V.N., April 25, 2003 - 4:46 pm UTC

Thank you

v$sesstat

reader, April 26, 2003 - 2:47 am UTC

tom,

> 2) look in v$sesstat for open_cursors

ge$cpmdev@deveccpm>sho parameters open_cursors

NAME TYPE VALUE
==================================== ======= ==============================
open_cursors integer 300
ge$cpmdev@deveccpm>select count(*) from v$sesstat where SID=13;

COUNT(*)
==========
248

ge$cpmdev@deveccpm>select sum(VALUE) from v$sesstat where SID=13;

SUM(VALUE)
==========
2103335632

1) what is this value? ... why is it such a big number?

2) my session (SID=13) has used 248 cursors - right?

Thank you !




Tom Kyte
April 26, 2003 - 8:31 am UTC

v$sesstat contains lots of statistics -- join it to v$statname to see them by name.

The first query -- count(*) -- shows we are tracking 248 separate statistics for you.

The seconds query adds up how much pga memory, max pga memory, uga memory, max uga memory, sorts, sorts to disk, CURSORS, etc etc etc etc you did. It is meaningless.

this does not show sid 13 has 248 cursors.


select a.name, b.value
from v$sesstat b, v$statname a where a.statistic# = b.statistic#

how many cursors are used by my session?

reader, April 26, 2003 - 8:39 am UTC

how to know, how many cursors are used by my session?

thanks

Tom Kyte
April 26, 2003 - 11:43 am UTC

join v$mystat to v$statname and look at open cursors.

or add "and sid = ..." to the above query.

Use the following query

Saminathan Seerangan, April 26, 2003 - 9:35 am UTC

For your session,

select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3;

all the open cursors for the instance

select sum(a.value), b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
group by b.name;

Tom Kyte
April 26, 2003 - 11:47 am UTC

use statnames, not numbers -- we change them over time i've discovered.

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library