I am also getting an error like ' ora-01000 maximum open cursors exceeded' How to get rid of this error ...I am not a DBA but a aprogrammer..what to do to avoid this error..How to stop this error..pl explain more...

If you write Java programs, you should remind yourself to open AND close the result set and statment after finishing it. Otherwise, your program will consume all the database cursor resources.

As a new DBA this sort of information is invaluable to me. Thanks for provinding this great service.

Tom - I am getting an open cursors max error but it is coming from the gateway with a ORA-2063. This is a gateway to DB2. Looking at document 198850.995 on metalink, the gateway has it's very own open_cursors or hs_open_cursors parameter that also defaults to 50 and can be set to 5000.
Very simply put, is there any reason not to set it sky high at 5000 if I am certain there is no cursor leakage? You choose 1000 to alert you of problems. In this case, there are triggers being fired off in DB2 by a PL/SQL proc that weren't there last week and I assume there's an open cursor opened for each DB2 trigger firing (I don't *know* that of course). I'd like to set it to the maximum rather than have to bump it up over and over again. What do you think?

Hi Tom,
I still don't have very clear the relation between open_cursors and session_cached_cursors
Could you please explain it.

open_cursor -- how many concurrently opened cursors you can have.

session_cached_cursors -- how many cached CLOSED cursors you can have.

it is hard to explain a relationship where none really exists...  The only relationship they really have is -- they both have cursors at the end of their name....

Consider:

ops$tkyte@ORA920> show parameter _cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     50
session_cached_cursors               integer     100

<b>that means, "you cannot have more then 50 open at the same time  - but we might cache 100 of them for you off to the side..."</b>


ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative              26
opened cursors current                  9
session cursor cache hits               0
session cursor cache count             13
cursor authentications                  1

ops$tkyte@ORA920> declare
  2          type rc is ref cursor;
  3
  4          l_cursor rc;
  5  begin
  6          for i in 1 .. 100
  7          loop
  8                  for j in 1 .. 5
  9                  loop
 10                          open l_cursor for 'select * from dual xx' || i;
 11                          close l_cursor;
 12                  end loop;
 13          end loop;
 14  end;
 15  /

PL/SQL procedure successfully completed.

ops$tkyte@ORA920>
ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'

NAME                                VALUE
------------------------------ ----------
opened cursors cumulative             529
opened cursors current                  9
session cursor cache hits             400
session cursor cache count            100
cursor authentications                  1

<b>that shows I've 100 cursors in my "cache" ready to be opened faster then normal -- but I never exceeded my 50 open cursors at a time threshold.</b>

 

:) Thanks Tom

As you told even not with this words is:
This statistics gives you the actual number of truely open cursors:
--For current session
select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current', for current session

--For all sessions
select a.sid, a.value, b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'


Now the question is, in which way V$SESSION_CURSOR_CACHE can be used to evaluate the parameter open_cursors?

Thanks

Thanks

Hi, A friend found a query in Metalink,
Which is your opinion about using it to evaluate the open_cursor and session_cached_cursors value

select
'session_cached_cursors' parameter,
lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from
( select
max(s.value) used
from
v$statname n,
v$sesstat s
where
n.name = 'session cursor cache count' and
s.statistic# = n.statistic#
),
( select
value
from
v$parameter
where
name = 'session_cached_cursors'
)
union all
select
'open_cursors',
lpad(value, 5),
to_char(100 * used / value, '990') || '%'
from
( select
max(sum(s.value)) used
from
v$statname n,
v$sesstat s
where
n.name in ('opened cursors current', 'session cursor cache count') and
s.statistic# = n.statistic#
group by
s.sid
),
( select
value
from
v$parameter
where
name = 'open_cursors'
)
/

:) Thanks, personally I prefer the trigger, it gives more information.

Tom,

can you explain me about :

<quote>
cursors are allocated in chunks (64 at a time) but the array itself that holds the cursors is allocated once.
</quote>

what array are you talking about?

Few more questions from what i observed when i tried to simulate your example

1) i dont see the number of cursor allocation in chunks - why?, see i started with 9 (opened cursors cumulative) then gradually it went increased by 1....

2) in the below observation the number of open cursors (cumulative) have exceeded 300 but i didnt get any ora-01000 maximum open cursors exceeded' error - or is it opened cursors current i need to watch out for?

3) when will this cursors get closed if i dont explicitly close the connection in my program

4) i commented the close l_cursor - but still i didnt see any increase in opened cursors current -

please set me straight,

Thanks for your time.


ge$scott@workflow.us.oracle.com>select a.name, b.value from v$statname a, v$mystat b where a.statistic# = b.statistic#
2 and lower(a.name) like '%' || lower('&1')||'%'
3 ;
Enter value for 1: cursor
old 2: and lower(a.name) like '%' || lower('&1')||'%'
new 2: and lower(a.name) like '%' || lower('cursor')||'%'

NAME VALUE
================================================================ ==========
opened cursors cumulative 9
opened cursors current 1
session cursor cache hits 0
session cursor cache count 0
cursor authentications 3

ge$scott@workflow.us.oracle.com>/
Enter value for 1: cursor
old 2: and lower(a.name) like '%' || lower('&1')||'%'
new 2: and lower(a.name) like '%' || lower('cursor')||'%'

NAME VALUE
================================================================ ==========
opened cursors cumulative 10
opened cursors current 1
session cursor cache hits 0
session cursor cache count 0
cursor authentications 3

ge$scott@workflow.us.oracle.com>/
Enter value for 1: cursor
old 2: and lower(a.name) like '%' || lower('&1')||'%'
new 2: and lower(a.name) like '%' || lower('cursor')||'%'

NAME VALUE
================================================================ ==========
opened cursors cumulative 11
opened cursors current 1
session cursor cache hits 0
session cursor cache count 0
cursor authentications 3
.
.
.
.
ge$scott@workflow.us.oracle.com>/
Enter value for 1: cursor
old 2: and lower(a.name) like '%' || lower('&1')||'%'
new 2: and lower(a.name) like '%' || lower('cursor')||'%'

NAME VALUE
================================================================ ==========
opened cursors cumulative 140
opened cursors current 1
session cursor cache hits 0
session cursor cache count 0
cursor authentications 18

ge$scott@workflow.us.oracle.com>/
Enter value for 1: cursor
old 2: and lower(a.name) like '%' || lower('&1')||'%'
new 2: and lower(a.name) like '%' || lower(' cursor')||'%'

NAME VALUE
================================================================ ==========
opened cursors cumulative 176
opened cursors current 1
session cursor cache hits 0
session cursor cache count 0
.
.
.
.

ge$scott@workflow.us.oracle.com>/
Enter value for 1: cursor
old 2: and lower(a.name) like '%' || lower('&1')||'%'
new 2: and lower(a.name) like '%' || lower('cursor')||'%'

NAME VALUE
================================================================ ==========
opened cursors cumulative 381
opened cursors current 1


ge$scott@workflow.us.oracle.com>sho parameter cursors

NAME TYPE VALUE
==================================== ======= ==============================
open_cursors integer 300
session_cached_cursors integer 0

ge$scott@workflow.us.oracle.com>

init.ora OPEN_CURSORS=512
8.1.7.4
I am troubleshoot a PRO/C program that is erring out with an ORA-01000 error. I
have reviewed the code and it looks to me that all cursors are being closed.
Open cursors is set to 512 on this system. When the program runs
--
select value from v$sesstat
where sid=7 -- this is the sid of the procedure
and statistic#=3
--
VALUE
-----
340 - this value will continue to grow until it reaches 512 then the program
errs out.
--
select
count(*)
from v$open_cursor
where sid=7
---
COUNT(*)
-------
17 -- This value does not increase as the program runs.
--
I have read many of your posts on why the 2 values are different and I think I understand that, but I don't understand why I am getting the ORA-01000 when v$open_cursor only returns count(*) of 17
--

Tom, I had opened up a tar on this, and just now received a response. Could you comment on if this makes sense to you.


Resolution History


18-AUG-03 18:27:02 GMT


Can you easily recover from, bypass or work around the problem? = YES
Does your system or application continue normally after the problem occurs? =
YES
Are the standard features of the system or application still available; is the
loss of service minor? = YES

### Platform and O/S version, including patch set or service pack level? ###
Redhat Advanced Server 2.1

### What version and patch set level of the database are you running? ###
8.1.7.4.0

### Problem Description and its duration: ###
I am troubleshooting a C program that is erring out with an ORA-01000 error. I
have reviewed the code and it looks to me that all cursors are being closed.
Open cursors is set to 512 on this system. When the program runs
--
select value from v$sesstat
where sid=7 -- this is the sid of the procedure
and statistic#=3
--
VALUE
-----
340 - this value will continue to grow until it reaches 512 the the program
errs out.
--
select
count(*)
from v$open_cursor
where sid=7
---
COUNT(*)
-------
17 -- This value does not increase as the program runs.
--
My question is why are the 2 values different and how to I figure out what
cursors are not getting closed?
--

### Did the error generate a trace file? ###
Choose...

### Error can be generated if SQL statement is run in SQL*Plus/Server Mgr: ###
Choose...

### What is the frequency of the error? ###
Choose...

### What is the impact to your business because of this problem? ###
--

### Are you running any third-party applications? ###
--

### Has the third-party Vendor been contacted? ###
Choose...

Error : ORA-01000
Contact me via : Electronic support services



18-AUG-03 19:43:32 GMT

Hi,

You can view the open cursors from the view v$open_cursor.
select sql_text from v$open)_cursor will give you the
cursors which are open.

You would need to increase the open_cursors value in the init.ora file
to get around this problem.

Thanks,
Harish.

18-AUG-03 19:43:50 GMT

Email Update button has been pressed -- Sending email.

18-AUG-03 19:51:25 GMT

New info : OK,
v$open_cursor count does not increase.
( I repeat select count(*) from v$open_cursor for this SID always returns 17
rows)
value from v$sesstat continues to increase until it hits max open_cursors which
is 512, then the program ORA-01000's.
What am I missing?


18-AUG-03 20:11:57 GMT

Hi,

Would it be possible for you to upload the c code so that we could review it.

Thanks,
Harish.

18-AUG-03 20:12:14 GMT

Email Update button has been pressed -- Sending email.

18-AUG-03 20:39:21 GMT

Country: UNITED STATES
The customer has uploaded the following file via MetaLink:
\\Ciscodb\Projects\Judicial_Branch-State_of_Iowa\Protective_Orders\DA_line\Tar32
48051.996.zip


18-AUG-03 20:55:40 GMT

Working on this.

18-AUG-03 21:05:21 GMT


Hi,

There are two ways to workaround this ORA-01000 error. You can tune cursor
usage at the database level and at the application level.

1. Tuning at the DATABASE LEVEL

There is a parameter you can set in the init.ora that determines the number of
cursors a user can open in a session: OPEN_CURSORS.

To solve the ORA-01000 error, set the OPEN_CURSORS to a higher number . You may need to set it to the maximum of the operating system limit.

Consequences to changing this parameter:
This parameter does not effect performance in any way but Oracle will now need
a little more memory to store the cursors.


2. Tuning at the APPLICATION LEVEL

There are three parameters that affect handling cursors at the application
level: RELEASE_CURSOR, HOLD_CURSOR, MAXOPENCURSORS. You should set these
parameters at the precompiler level.

HOLD_CURSOR by default is NO. This means that after Oracle executes a SQL
statement the links to the cursor cache, memory, and parse locks are released
and marked for reuse.

RELEASE_CURSOR by default is NO. This means that after Oracle executes a SQL
statement, the links to the cursor cache is maintained and not released.

To resolve the ORA-01000 error, you should set HOLD_CURSOR=NO and
RELEASE_CURSOR=YES. This way, after the cursors are used, Oracle will free up
the memory for other cursors.

I did not mean to paste the entire Tar into the document, just the last piece.

So if I understand correctly. A Pro/C program will not release cursors and value in v$sesstat will continue to grow until it reaches OPEN_CURSORS and then ORA-01000 out, unless you precompile with RELEASE_CURSORS=yes.

#include <stdio.h>
#include <stdlib.h>

EXEC SQL INCLUDE "sqlca.h";

static char *userToken="td123";
static char *passToken="td123";
static char *db1Name="Context1";
static char *db2Name="Context2";
static char *db1String="at1d";
static char *db2String="at2d";

void openDBContext(const char * user, const char *pass, const char *DBContext , const char *DBString)
{
EXEC SQL CONNECT :user IDENTIFIED BY :pass AT :DBContext USING :DBString;
}

void isGood(const char *DBContext)
{
EXEC SQL AT :DBContext SELECT 1 FROM DUAL;

if (sqlca.sqlcode)
{
sqlca.sqlerrm.sqlerrmc[sqlca.sqlerrm.sqlerrml] = '\0';
printf("%s: %s\n", DBContext, sqlca.sqlerrm.sqlerrmc);
}
}

int main(int argc, char* argv[])
{
openDBContext(userToken,passToken,db1Name,db1String); isGood(db1Name);
openDBContext(userToken,passToken,db2Name,db2String); isGood(db2Name);
return 0;
}

Okay so I have a java app running on Websphere against my oracle 8.1.7.4.1 database and I am seeing cursors left open. This is a fairly new app and is only in pilot with a small number of users(150) that are migrating from our C/S app. I wrote a quick vb.net app to parse every line of the java app and report on unclosed connections, prepared statements, and resultsets. The report is big and my dev team is working on it.

I can't easily bounce the db and raise the open_cursors parameter and I would rather not flush the shared pool. Can you make any other short-term fix recommendations to deal with the problem or close the cursors?

Thanks in advance.

If pl/sql caches some of the cursors for us then
would they be shown under "open cursor current".

In that case these open cursors are "false positives"
as far as trying to detect the "max open cursors"
problem since they are not really leaks.

Is this statement accurate!

ops$tkyte@ORA920> create or replace procedure p( p_more in boolean default false )
  2  as
  3          l_x   number;
  4  begin
  5          select 1 into l_x from dual;
  6          select 2 into l_x from dual;
  7          select 3 into l_x from dual;
  8          select 4 into l_x from dual;
  9          select 5 into l_x from dual;
 10          if ( p_more )
 11          then
 12                  select 6 into l_x from dual;
 13                  select 7 into l_x from dual;
 14                  select 8 into l_x from dual;
 15                  select 9 into l_x from dual;
 16                  select 10 into l_x from dual;
 17          end if;
 18  end;
 19  /
 
Procedure created.
 
ops$tkyte@ORA920> connect /
Connected.
ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'
 
NAME                                VALUE
------------------------------ ----------
opened cursors cumulative               9
opened cursors current                  1
session cursor cache hits               0
session cursor cache count              7
cursor authentications                  0
 
ops$tkyte@ORA920> exec p
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'
 
NAME                                VALUE
------------------------------ ----------
opened cursors cumulative              17 
opened cursors current                  6     <<<<==== +5
session cursor cache hits               1
session cursor cache count              7
cursor authentications                  5
 
ops$tkyte@ORA920> exec p( true )
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'
 
NAME                                VALUE
------------------------------ ----------
opened cursors cumulative              24  
opened cursors current                 11    <<<==== +5 more
session cursor cache hits               2
session cursor cache count              7
cursor authentications                 10
 
ops$tkyte@ORA920> exec p( true )
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA920> @mystat cursor
ops$tkyte@ORA920> select a.name, b.value
  2  from v$statname a, v$mystat b
  3  where a.statistic# = b.statistic#
  4  and lower(a.name) like '%' || lower('&1')||'%'
  5  /
old   4: and lower(a.name) like '%' || lower('&1')||'%'
new   4: and lower(a.name) like '%' || lower('cursor')||'%'
 
NAME                                VALUE
------------------------------ ----------
opened cursors cumulative              26
opened cursors current                 11    <<< === same +5
session cursor cache hits               3
session cursor cache count              7
cursor authentications                 11
 


yes, accurate 

SELECT b.SID, UPPER(a.NAME), b.VALUE
FROM v$statname a, v$sesstat b, v$session c
WHERE a.statistic# = b.statistic#
AND c.SID = b.SID
AND LOWER(a.NAME) LIKE '%' || LOWER('CURSOR')||'%'
AND b.SID=20
UNION
SELECT SID, 'v$open_cursor opened cursor', COUNT(*)
FROM v$open_cursor
WHERE SID=20
GROUP BY SID
ORDER BY SID

SID,UPPER(A.NAME), VALUE
20 ,CURSOR AUTHENTICATIONS, 292
20 ,OPENED CURSORS CUMULATIVE, 2247
20 ,OPENED CURSORS CURRENT, 477
20 ,SESSION CURSOR CACHE COUNT, 0
20 ,SESSION CURSOR CACHE HITS, 0
20 ,v$open_cursor COUNT(*), 16

After reading many comments on cursors, I get confused.

- Could you please clarify once and for all what is the difference between 'OPENED CURSORS CURRENT' and COUNT(*) from v$open_cursor? What is the meaning in this case of 477 and 16?

- And which one do we use to diagnose problem when the application has MAXIMUM CURSOR EXCEEDED exception?

Thanks so much

Tom,

We currently have Oracle9.2.0.3 installed on HP-UX.

The following query opens different number of cursors (in v$open_cursor) based on the platform.

select deptno,
       dname,
       cursor (select ename from emp where deptno=b.deptno) 
       as "Employee"
from dept b
/

This query has only two rows (opens only two cursors) in v$open_cursor if I run this query by logging on to the HP server and running from the sqlplus.

HP-UX:SQL> SELECT * FROM V$OPEN_CURSOR WHERE SID=64;
/
SADDR                   SID USER_NAME                      ADDRESS          HASH_VALUE
---------------- ---------- ------------------------------ ---------------- ----------
SQL_TEXT
------------------------------------------------------------
C000000037237C98         64 SCOTT                        C0000000478A4C28  660474943
select deptno,dname,cursor (select ename from emp where dept

C000000037237C98         64 SCOTT                        C000000047422958 4190467353
SELECT "A2"."ENAME" "ENAME" FROM "EMP" "A2" WHERE "A2"."DEPT

If I run the same query from WINDOWS environment connecting 
to the same database through SQLPLUS with the Oracle9i client it opens up as many number connections as there in DEPT table.

SADDR                   SID USER_NAME                      ADDRESS
---------------- ---------- ------------------------------ ----------------
HASH_VALUE SQL_TEXT
---------- ------------------------------------------------------------
C000000037236778         60 SCOTT                        C0000000478A4C28
 660474943 select deptno,dname,cursor (select ename from emp where dept

C000000037236778         60 SCOTT                        C000000047422958
4190467353 SELECT "A2"."ENAME" "ENAME" FROM "EMP" "A2" WHERE "A2"."DEPT

C000000037236778         60 SCOTT                        C000000047422958
4190467353 SELECT "A2"."ENAME" "ENAME" FROM "EMP" "A2" WHERE "A2"."DEPT

C000000037236778         60 SCOTT                        C000000047422958
4190467353 SELECT "A2"."ENAME" "ENAME" FROM "EMP" "A2" WHERE "A2"."DEPT

C000000037236778         60 SCOTT                        C000000047422958
4190467353 SELECT "A2"."ENAME" "ENAME" FROM "EMP" "A2" WHERE "A2"."DEPT


Is there a problem with Oracle9i client or is any setting
that we are missing in our environment.

Thanks
Pushparaj 

Tom,

Thanks for your immediate reply.

The following is the setting for cursors in our environment.

show parameter cursor

NAME TYPE VALUE
------------------------------------ ----------- -------
cursor_sharing string EXACT
cursor_space_for_time boolean FALSE
open_cursors integer 500
session_cached_cursors integer 0

One of our application query is failing with "maximum open cursors exceeded" which has some CURSOR statements in the SELECT statement and retrieves many rows.

The same SQL without any change works fine if I log on to
the unix box and run it from the sqlplus.

But if I run the exact same query without any change
by connecting to the exact same database from windows
environment through SQLPLUS then it fails with "maximum open cursors exceeded".

While it is running, I could see the rows in V$OPEN_CURSOR
keep increasing and when it reaches the 500 count it fails.

But this is not the case if I run it from the SERVER itself.

I can make this query fail by simply putting 500 rows in
DEPT table.

sql> select deptno,loc,cursor(select ename from emp
where deptno = b.deptno) from emp
/

Please advice.

Thanks
Pushparaj

Tom,
I have mistyped in my previous posing.
sql> select deptno,loc,cursor(select ename from emp
where deptno = b.deptno) from emp
/
It should be

sql> select deptno,loc,cursor(select ename from emp
where deptno = b.deptno)
from dept b
/

The dept table has 550 rows and the emp table has 20 rows
and this query fails from windows sqlplus but not if i run
it from the server itself.

Thanks
Pushparaj

Tom,

We are using sqlplusw and please the see the version of it.

SQL*Plus: Release 9.2.0.1.0 - Production on Mon Feb 23 09:25:08 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.3.0 - Production

SQL> select deptno, cursor(select 'testing' from dual
) as "maxopen" from dept
/
...
...
       692 CURSOR STATEMENT : 2

CURSOR STATEMENT : 2

'TESTIN
-------
testing

ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01000: maximum open cursors exceeded

497 rows selected.

Thanks
Pushparaj 

Hi Tom
I am getting this message Maximum open cursor exceeded
And I am using Personal Oracle7 7.3.2.1.1 and I try to increase the Open Cursor to 1000 but ,I did not Find the INI.ORA file
is there another file name instead of INI.ORA in Personal Oracle7 7.3.2.1.1 , and also when I try to run the report through the main program it is exit ,and I try to recompile my main program that it call the report becuase one of my friend told me to recompile the form to solve this problem ,but it does not solve it , what could be the problem ,and where can I increase the open cursor , please answer in details ,with example .

Waiting for your replay

Best Regards
Jamil

Hi Tom
There is no file called as the name init$ORACLE_SID.ORA or any thing like INIT*.ORA becuase I have used a window search by typing this INIT*.ORA and start searching and it gives no result to display .

Best Regards

Jamil

Jamil you will have to search under the directory where Oracle was installed. It is probably under a dbs dir, but it really could be anywhere. It should be initSID.ora where SID is the sid of the database limited to 4 characters. It is there, you just have to search for it.

Found the comments helpfull

Keep up the good work guys

Search for init.ora. You will find it under pfile directory

Tom,

Where can I find the currently setting of MAXOPENCURSORS in UNIX platform? I checked $ORACLE_HOME/precomp/admin directory, but found nothing there.

Thanks as always

Tom,

Where can I find the currently setting of MAXOPENCURSORS in UNIX platform? I checked $ORACLE_HOME/precomp/admin directory, but found nothing there.

Thanks as always

Tom,

At UNIX prompt, I typed in 'proc' but got an error:

oracle@butterscotch:/export/home0/oracle>proc
ksh: proc: not found

What I missed here?

Tom,

We are using JAVA instead of pro*C. Does JAVA also have the same precompiler parameter of MAXOPENCURSORS (I am not a JAVA person, it might make non-sense to you)? Because our applications occasionally got ora-01000 error (the current setting for open_cursors is 500), we want to see what is the current setting of MAXOPENCURSORS. In many Oracle documents, it suggests to increase MAXOPENCURSORS.

Please advice. Thanks.

I have a SQL Loader controlfile that has 200 INTO TABLE statements.

This kept giving ORA-1000 maximum open cursors exceeded so I kept bumping up that parameter.

It finally stopped complaining at 300.

Why would 200 INTO TABLE insert statements need 300 open cursors?

Thanks

Hi Tom

Thanks - You have explained lot about opened cursor and performance hit. Could you please tell how to find out which application and which cursor is open and how long it has been in that state? Is there any way to close this from back-end?

Regards

Tom,
We are running a Java application on Oracle 8.1.7.4 database. In application We have a package, that has got some 20 functions and is used while the end user upload a data file (for various commodity). After a user's new requirement we modified the package and added one more
function (for new commodity) to it. After we made the changes when ever user is trying to upload the file he is getting ORA 1000 , if the file has more than 500 records. I checked the open cursor parameter in init.ora and it was set to 500. I changed it to 1000 and now the error comes if the file has more than 1000 records, upto 1000 its go in fine. I confirm with end user that prior to new changes there was no problem with file upload even the records were
5000. Error message we are getting is :
------------------------------------------------------------
Exception:
java.sql.SQLException: ORA-01000: maximum open cursors exceeded
ORA-06512: at "PARGUS.PADADDDEAL", line 321
ORA-06512: at "PARGUS.PADADDDEAL", line 328
ORA-06512: at "PARGUS.PADADDDEAL", line1530
ORA-06512: at line 1
-----------------------------------------------------------

Package body where it is hitting error is :

----------------------------------------------------------------------
Function getDefaultsForCode(fcode in varchar default null) RETURN INTEGER
as

---> 321 cursor curItemSpec(curcode in varchar) is select * from PADitemspecification
where code=(select code from padcode where paid=curcode and active_yn='Y');

curvarItemSpec curItemSpec%ROWTYPE;

begin

--->328 for curvarItemSpec in curItemSpec(fcode)
loop
ANILINE:=curvarItemSpec.ANILINE;
APIGRAVITY:=curvarItemSpec.APIGRAVITY;
BENZENE:=curvarItemSpec.BENZENE;
CETANE:=curvarItemSpec.CETANE;
CLOUDFILTERPLUGPOINT:=curvarItemSpec.CLOUDFILTERPLUGPOINT;
CLOUDPOINT:=curvarItemSpec.CLOUDPOINT;
COUNTRY:=curvarItemSpec.COUNTRY;
CURRENCY:=curvarItemSpec.CURRENCY;
DELIVERYPORT:=curvarItemSpec.DELIVERYPORT;
FIELDREFINERY:=curvarItemSpec.FIELDREFINERY;
FLASHPOINT:=curvarItemSpec.FLASHPOINT;
GRADE:=curvarItemSpec.GRADE;
LOADINGPORT:=curvarItemSpec.LOADINGPORT;
LOCATION:=curvarItemSpec.LOCATION;
METALS:=curvarItemSpec.METALS;
OCTANE:=curvarItemSpec.OCTANE;
PARAFFIN:=curvarItemSpec.PARAFFIN;
POURPOINT:=curvarItemSpec.POURPOINT;
RVP:=curvarItemSpec.RVP;
SULPHUR:=curvarItemSpec.SULPHUR;
VANADIUM:=curvarItemSpec.VANADIUM;
VISCOSITY:=curvarItemSpec.VISCOSITY;
COLOR:=curvarItemSpec.COLOR;
SPECIFICGRAVITY:=curvarItemSpec.SPECIFICGRAVITY;
LEADCONTENT:=curvarItemSpec.LEADCONTENT;
end loop;

APPROVED_YN :='Y';
BUYER_ID :=null;
-- 14 arp 2003 shampa default changed to 'N'
BUYERCONFIRM := 'N';
CODE := null;
COMMODITY_ID := null;
DEALDATE := null;
DEALTYPE := null;
DELIVERYFROMDATE := null;
DELIVERYMODE := null;
DELIVERYPERIOD := null;
DELIVERYTODATE := null;
DELIVERYYEAR := null;
DIFFPRICE := null;
DIFFTIMING := null;
LOADINGFROMDATE := null;
NEWS_ID := null;
NOTES := null;
PRICE := null;
REGION_ID := null;
SELLER_ID := null;
-- 14 arp 2003 Shampa default changed to 'N'
SELLERCONFIRM := 'N' ;
UNIT_ID := null;
DIFFBASIS := null;
SIZEVOL:=null;

select sizevol into SIZEVOL from padcode where paid=fcode and active_yn='Y';
if (curItemSpec%ISOPEN) then
close curItemSpec;
end if;

return 1;
EXCEPTION
when no_data_found then
return 1;
end;

....
....
....
....
-- funtion to add deal for Electricity
Function AddDealElectricity(
scode in varchar default null,scommodityid in varchar default null,
sregionid in varchar default null,sdealdate in varchar default null,
sprice in varchar default null,sdeliveryperiodid in varchar default null,
sdeliveryyear in varchar default null,sdeliveryfromdate in varchar default null,
sdeliverytodate in varchar default null,
svolume in varchar default null,scountry in varchar default null,
scurrency in varchar default null,sentrytime in varchar default null,
dnotes varchar default null,privatedeal in varchar default null,newsid in varchar default null
)RETURN VARCHAR

as
retFlag integer default null;
addPortflag integer default 0;
begin
---> 1530 retFlag:=getDefaultsForCode(scode);
COMMODITY_ID:=to_number(scommodityid);
REGION_ID:=to_number(sregionid);
NEWS_ID:=newsid;
....
....
-------------------------------------------------------------------------------------------

Function getDefaultsForCode has got implicit cursor which closes itself outside the loop. Tom could you please advise us why we are hitting this problem, if records to be uploaded are greater than open cursor parameter.

Regards
Vivek

Hi,
I have a procedure p. It is using 4 cursors.
I am able to seen this in v$open_cursor. But if I
recreated this procedure oracle flushes open cursors
from v$open_Cursor.

Could you pls. explain ..

Thanks,
Js

ok.

SQL> conn /
Connected.
SQL>
SQL> show parameter open_cur

NAME            TYPE        VALUE
--------------- ----------- ------------------------------
open_cursors    integer     50

SQL>
SQL> get a
  1   Create or replace procedure p
  2   as
  3   x   int;
  4   begin
  5   select 1 into x from dual;
  6   select 2 into x from dual;
  7   select 3 into x from dual;
  8   select 4 into x from dual;
  9   select 5 into x from dual;
 10   select 6 into x from dual;
 11   select 7 into x from dual;
 12   select 8 into x from dual;
 13   select 9 into x from dual;
 14   select 10 into x from dual;
 15   select 11 into x from dual;
 16   select 12 into x from dual;
 17   select 13 into x from dual;
 18   select 14 into x from dual;
 19   select 15 into x from dual;
 20   select 16 into x from dual;
 21   select 17 into x from dual;
 22   select 18 into x from dual;
 23   select 19 into x from dual;
 24   select 20 into x from dual;
 25   select 21 into x from dual;
 26   select 22 into x from dual;
 27   select 23 into x from dual;
 28   select 24 into x from dual;
 29   select 25 into x from dual;
 30   select 26 into x from dual;
 31   select 27 into x from dual;
 32   select 28 into x from dual;
 33   select 29 into x from dual;
 34   select 30 into x from dual;
 35   select 31 into x from dual;
 36   select 32 into x from dual;
 37   select 33 into x from dual;
 38   select 34 into x from dual;
 39   select 35 into x from dual;
 40   select 36 into x from dual;
 41   select 37 into x from dual;
 42   select 38 into x from dual;
 43   select 39 into x from dual;
 44   select 40 into x from dual;
 45   select 41 into x from dual;
 46   select 42 into x from dual;
 47   select 43 into x from dual;
 48   select 44 into x from dual;
 49   select 45 into x from dual;
 50   select 46 into x from dual;
 51   select 47 into x from dual;
 52   select 48 into x from dual;
 53   select 49 into x from dual;
 54   select 50 into x from dual;
 55   select 51 into x from dual;
 56   select 52 into x from dual;
 57   select 53 into x from dual;
 58   select 54 into x from dual;
 59* end;
SQL> /

Procedure created.

SQL> select sql_Text from v$open_Cursor;

SQL_TEXT
------------------------------------------------------------
select sql_Text from v$open_Cursor

SQL> exec p

PL/SQL procedure successfully completed.


SQL_TEXT
------------------------------------------------------------
SELECT 24 from dual
SELECT 31 from dual
SELECT 53 from dual
SELECT 32 from dual
SELECT 45 from dual
SELECT 23 from dual
SELECT 35 from dual
SELECT 29 from dual
SELECT 21 from dual
SELECT 17 from dual
SELECT 30 from dual
SELECT 42 from dual
SELECT 22 from dual
SELECT 10 from dual
SELECT 48 from dual
SELECT 41 from dual
SELECT 33 from dual
SELECT 9 from dual
SELECT 40 from dual
SELECT 44 from dual
SELECT 39 from dual
SELECT 13 from dual
SELECT 12 from dual
SELECT 54 from dual
SELECT 49 from dual
SELECT 37 from dual
SELECT 26 from dual
SELECT 38 from dual
select sql_Text from v$open_Cursor
SELECT 50 from dual
SELECT 28 from dual
SELECT 36 from dual
SELECT 46 from dual
SELECT 51 from dual
SELECT 14 from dual
SELECT 16 from dual
SELECT 15 from dual
SELECT 34 from dual
SELECT 20 from dual
SELECT 25 from dual
SELECT 11 from dual
SELECT 47 from dual
SELECT 18 from dual
SELECT 19 from dual
SELECT 43 from dual
SELECT 52 from dual
SELECT 27 from dual

47 rows selected.


My open cursor is 50 and this procedure is opening 
54 cursors at a time. 

I was waiting for 'ora-01000 maximum open cursors exceeded' .
Why does oracle close some of mine cursors.

Thanks,
Js 

NB don't bother if you don't have the answer handy, I'll try to get the answer myself if I can.

Given that v$open_cursor reports even on closed cursors:

select n.name, m.value
from v$mystat m, v$statname n
where m.statistic# = n.statistic#
and n.name = 'opened cursors current';

NAME VALUE
------------------------------ ----------
opened cursors current 1

(we now that this is the true answer since sqlplus
holds open one cursor at a time)

select count(*) from v$open_cursor
where sid = (select sid from v$mystat where rownum=1);

COUNT(*)
----------
5

What magic query may we run to get the sql statements of the cursors that are really open (possibly in 9i) ?

TIA even if you don't know ;)

Hi Tom,

We are hitting max open cursors. When I look at the java code I see java statements are being along with the connections but not the resultset?

Does this is the reason for getting this error?

One other question is can I get this error because of bad pl/sql coding? If so can you please give us an example of bad pl/sql block which can cause this kind of error.

Thanking you for all your help for the oracle community.

Regards
Venu

Hi Tom,

I am Sorry for not being clear.

I mean to say that when I looked at the java code I see I see java statements are being closed along with the connections but not the resultset?

My question is if we close stmt and connection would that be enough or do we need to close the resultset explicitly.

Thanks for the pl/sql part. I have seen dbms_sql.open cursor in one of the package and it is being closed but there is when others then exception handler which does not contain dbms_sql.close, if this package raises an unhanlded exception would that cursor be left open since it is not being closed when exception is raised?

Also how do I identify sql statements which are not closed in v$open_cursor view other than pl/sql cached sql statements?

Thanking you for valuable input.

Regards
Venu

Hi Tom,

I am sorry for not being clear. I mean to say that in some of the java programs we are closing statements and connections but not the resultsets. Do we have to close the resultssets explicitly? closing connections would not close resultsets?

I saw in one of the package dbms_sql.open_cursor is being used and also it is being closed in the end but there is an exception block with when others then and it does not contain dbms_sql.close_cursor statement. If this procedure throws when others then exception, would that cause cursor to be kept opened as there is no dbms_sql.close_cursor statement?

How do I identify sql statements that are not closed in v$open_cursor view other than pl/sql cached cursor statements?

Thank you for your valuable input regarding pl/sql.

Regards
Venu

Hi Tom,

Thanks for your invaluable reply.

I was monitoring open cursors based on the query below as our application is being hit heavily because of load testing and values are really very high.

select name, sum(value)
from v$sesstat v, v$statname s
where v.statistic# = s.statistic#
and s.name like '%cursors%'
group by name

opened cursors cumulative 476587
opened cursors current 3805

After your input we have bumped the max_open_cursors parameter to 1024. Are these numbers normal as the application is being tested or do I need to increase to much higher value? What steps do we have to take if it breaks again?

Regards
Venu


Thanks
Venu

This is a common error in oracle portal and this discussion is very usefull and make us fully understand about everything relates the error...Tom is great

Hi Tom,
I've just seen code like the following all over this application I'm working on. I was wondering if the Return statement within the loop would cause the cursor to remain open or will it close it? Is there anyway I can find out this info from the db? Thanks a lot.

<code snippet>
For some_rec in some_cur loop
return 'Y';
end loop;
return 'N';
<end code snippet>

Hi Tom,
Thanks for your reply above. Just found this in the PL/SQL manual.

'
When you leave the loop, the cursor is closed automatically--even if you use an EXIT or GOTO statement to leave the loop prematurely or an exception is raised inside the loop.
'

I do agree with you tho, that it is really bad programming. What's wrong with just declaring a variable and setting its value within the loop and returning that back. The joys of supporting an existing appl. Oh well.

We are facing an issue in our application with ora-01000 error regarding cursors. Our cursor parameter is set to 5000 and there are quite a few sessions (all are java applications) that are causing the ORA error.

The application development team are not aware of where exactly the problem is happening in their application. However,we can get the sql from v$sqltext that might be their cursor statement.

From the DBA point of view what would be work around (other than increasing the open_cursors parameter)?
Can we close the cursors from our end?

This is been happening from the last 3 days and it was running great for more than a year.

Thanks,

Hi Tom,

I have a 3 tier architecture. My users connect to the database from websphere. I have set connection pooling at the MT and database is a dedicated connection. The connection pooling is 100 concurrent sessions. My query is :
1. If one of my application connected to the database using sid 11 (connection pooling from mt to db) and did some processing. It opened 120 cursors and then disconnected. Now since, it is a pooled connection, this session is still at the db. Now another user gets this connection and processes about 50 cursors. What would be the value of OPEN Cursors for this sid ? Will it be 170 or 50 ?

The reason, I asked this is that some times the application fails wil ora-1000. The parameter open_cursors is set to 2000 which is too high and I feel, it is a cursor leak issue and hence an application bug.

2. Is there any link between cursor_sharing=similar and open_cursors ? If a plan of the query changes based on the literal values, a child cursor is created and hence a new cursor will be opened which will finally add up to the number of concurrent cursors opened.

3. I have gone through this forum and some other forums as well, on the output fetched by v$open_cursors and "opened cursors current" from v$statname for a particular session.
What I could understand is that the actual number of concurrent opened cursors by a session is reported by v$statname, v$sesstat view and the value reported by v$open_cursor for a particular sid is the cursors, cached for this particular session (no matter closed or open). If this is true, than what is session_cached_cursor in this context ?

Tom, I will appreciate if you can answer me these as it will help me understand these concepts in greated detail.

Thanks
VLS

Tom,
We hit the open cursors issue on PROD this morning and we increased the open_cursors to 1000 from 600. I wrote to the development team to make sure they close all the open cursors but their response is that "Oracle closes all the open cursors after transaction" so why should we check our code ?

I do not think the above statement is correct. Why did we hit the figure in the first place then ?

I am a bit lost, please clarify.

Thanks

Tom,
Thanks, quite happy with your emphatic reply !
How can we find now which pl/sql code is the culprit, especially after increasing the open_cursors value to 1000 and is very likey we maynot hit the issue again.

Regards
Anupam

We are facing a problem in Production from long time.
It is giving maximum cursors exceeded problem.

When I query against the V$OPEN_CURSOR and V$SQL most of the queries are Statements (Java Statements). Those statements are not prepared statements.

My question is whether java statements (without bind variables - not prepared statements) can cause max cursors exceeded problem or not?

In our development and testing environments we are not getting the same problem.
For your info. Production is running on 9.2.0.4 version and others are running on 9.2.0.7 version

It will be great if you can give some solution for this.

From the Oracle manual

MAXIMUM NUMBER Maximum number of cursors to cache. Once you hit this number, some cursors will need to be closed in order to open more. The value in this column is derived from the initialization parameter OPEN_CURSORS.
COUNT NUMBER The current number of cursors (whether they are in use or not)
OPENED_ONCE NUMBER Number of cursors opened at least once
OPEN NUMBER Current number of open cursors
OPENS NUMBER Cumulative total of cursor opens minus one. This is because the cursor that is currently open and being used for this query is not counted in the OPENS statistic.
HITS NUMBER Cumulative total of cursor open hits
HIT_RATIO NUMBER Ratio of the number of times an open cursor was found divided by the number of times a cursor was sought

In my prod instance these are all 0s except maximum which is derived from OPEN_CURSORS

Qn is : Are all the other values 0 since session_cached_cursors parameter is currently not set in our instance ? How can I find out whether the soft parse that is happening in a instance - is a softer soft parse(as to what happens in pl/sql automatically) or a soft parse ? How can I know the frequency of ordinary soft parse(not softer soft parse) happening in a session - like for example due to usage of 'execute immediate' within a pl/sql proc ?

thanks
Anto

Continuation of above post

select * from v$sysstat a where upper(a.name )like '%SESSION CURSOR CACHE HITS%'

also returns 0 - although a lot of soft parses are happening currently as seen from v$sysstat

thanks
Anto

I'm trying to implement a packaged function that generates a value based on a "single row" cursor generated from a given input cursor, something that looks like that:

select T.MyCol,
MyPck.MyFct( cursor( select Column_Name from User_Tab_Columns where Table_Name = 'MYTAB' ) )
from MyTab T ;

The query provided for the input cursor does not take any inputs from the "base query" (MyTab).
Therefore that input cursor should be processed only once per statement (instead of once per row).
The input cursor's outcome, the generated cursor, should be "cached" and get reused for every row of "MyTab".

So function "MyFct" should:
1st: fetch from that provided input cursor,
2nd: create another cursor based on that fetched data,
3rd: close the provided input cursor and
4th: reuse the generated cursor with bind values from "MyTab"'s rows

Steps 1 to 3 (including DBMS_SQL.Open_Cursor, .Parse and .Define_Column) should only be performed in case the generated cursor required for step 4 (invoking DBMS_SQL.Bind_Variable, .Execute, .Fetch_Rows and .Column_Value) doesn't already exist.

The goal is to create/parse that generated cursor once per statement and reuse it once per row of "MyTab".

Now I'm facing a "too many open cursors" issue with that approach and can't find out what goes wrong.

I've done several tests to track down the root cause(s):

It is confirmed that ...:
- ... the provided input cursor gets closed as expected (evidence: subsequent attempts to close that cursor fail).
- ... the "generate cursor branch" is entered only once per statement (evidence: value of packaged counter variable).

If the generated cursor gets closed during each invocation of "MyPck.MyFct" ...:
- ... then the very next subsequent attempt to fetch from this cursor fails (evidence for: no open cursor "left").
- ... and gets reopened (over and over again) then the "number of open cursor exceeded" problem disappears.

So from my current point of view function "MyPck.MyFct" works as expected as it does NOT ...:
- ... leave open cursors behind.
- ... open the generated cursor more than once per statement.

Do I have to take care of something I don't already know (e.g. DBMS_SQL behaviour)?
Or am I facing some kind of subquery caching or "cursor caching" in PL/SQL here?

Please help.

... and it was in *my* code ...

Hi Tom,

Can "Current Open Cursors Count" be more than open_cursor on the database? In my database I have open_cursor set to 800, and I see an alert in Grid 'Metric "Current Open Cursors Count" is at 3880'. Is this a cummulative count? I am not able to comprehend these facts. Any pointers will be appreciated.

Regards

Natalka Roshak explains very well these confusing points :

This is an extract. See her document for full explanations : http://www.orafaq.com/node/758

1) "v$open_cursor shows cached cursors, not currently open cursors, by session.

=> To monitor open cursors, query v$sesstat where name='opened cursors current'. "


2) "The session statistic 'currently open cursors' can include some cursors that the application has closed.

=> One way for application developers to tell if an application is closing all its cursors is to do a single test run, on a dedicated development box, while monitoring "opened cursors cumulative" in v$sesstat for the session that's running the test. Then set OPEN_CURSORS to a value a little bit higher than the peak cursors open during your test run, start a new session, and run through multiple iterations of the same test run. If your application still has a cursor leak, you will see the value of OPEN_CURSORS going up, and you may hit an ORA-1000 after a reasonable number of iterations. (Don't set OPEN_CURSORS too low or it may be used up by recursive SQL; if your single test run opens very few cursors, consider making your test run longer rather than setting OPEN_CURSORS unreasonably low.)

Hope this will help people like me who did not undertood clearly Tom's answers about these 2 points.

Hi Tom,

I amjust trying the understand the meaning of the statistic "opened cursors current". Does it mean the no. of cursors currently open in a session ? I don't think so. Let me explain first.

Lets consider the following procedure first. I have created in database. It is 10.2.0.3

create or replace procedure p( p_more in boolean default false )
as
l number;
begin
select 1 into l from dual;
if ( p_more ) then
select 2 into l from dual;
end if;
end;
/

Now

I issue the following :

alter system set open_cursors=4 scope=memory;

Then I connect to database using a user and do the following :

SQL> show parameter open_cur

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
open_cursors                         integer
4
SQL> SELECT name,value FROM v$statname s, v$mystat m
  2  WHERE name LIKE '%cursor%' AND m.STATISTIC#=s.STATISTIC#;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                18
opened cursors current                                                    2
session cursor cache hits                                                 3
session cursor cache count                                               13
cursor authentications                                                    0

SQL> exec p;

PL/SQL procedure successfully completed.

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                21
opened cursors current                                                    3
session cursor cache hits                                                 4
session cursor cache count                                               15
cursor authentications                                                    1

SQL> exec p

PL/SQL procedure successfully completed.

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                23
opened cursors current                                                    3
session cursor cache hits                                                 6
session cursor cache count                                               15
cursor authentications                                                    1

SQL> exec p(true);

PL/SQL procedure successfully completed.

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                26
opened cursors current                                                    4
session cursor cache hits                                                 7
session cursor cache count                                               15
cursor authentications                                                    3

You can see that after executing "exec p(true);" the value of "opened cursors current" is 4 which is quite logical because of pl/sql caching.

Now I issue the follwoing 

alter system set open_cursors=3 scope=memory;

The do the same thing once again

SQL> show parameter open_cursor

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
open_cursors                         integer
3
SQL> SELECT name,value FROM v$statname s, v$mystat m
  2  WHERE name LIKE '%cursor%' AND m.STATISTIC#=s.STATISTIC#;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                18
opened cursors current                                                    2
session cursor cache hits                                                 3
session cursor cache count                                               12
cursor authentications                                                    2

SQL> exec p;

PL/SQL procedure successfully completed.

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                21
opened cursors current                                                    3
session cursor cache hits                                                 3
session cursor cache count                                               12
cursor authentications                                                    2

SQL> exec p

PL/SQL procedure successfully completed.

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                23
opened cursors current                                                    3
session cursor cache hits                                                 4
session cursor cache count                                               13
cursor authentications                                                    3

SQL> exec p(true);

PL/SQL procedure successfully completed.

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                26
opened cursors current                                                    3
session cursor cache hits                                                 5
session cursor cache count                                               13
cursor authentications                                                    3

But here what I expected that before running "exec p(true);", the value of "opened cursors current" is 3 which is maximum. So when I run
"exec p(true);", it should have thrown error . But it did not. 

1) Could you please explain why it has not thrown any error ?
2) From the above test , what I can understand that in 1st test, there was a room that's why it did open one more extra cursor. But in 2nd case,since there was no romm , it resued existing one for LRU list . It was possible since it was implicit cursor . But this understanding is also not correct because of the following test.


Now lets consider the follwoing test procedure 1st :

create or replace procedure p( p_more in boolean default false )
as
l number;
cursor c1 is select 1 from dual;
cursor c2 is select 2 from dual;
begin
open c1;
open c2;
close c2;
close c1;
end;
/

Now I issued the following:

alter system set open_cursors=3 scope=memory;

Now I connect to database and do the following:

SQL> show parameter open_cur

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
open_cursors                         integer
3
SQL> SELECT name,value FROM v$statname s, v$mystat m
  2  WHERE name LIKE '%cursor%' AND m.STATISTIC#=s.STATISTIC#;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                18
opened cursors current                                                    2
session cursor cache hits                                                 3
session cursor cache count                                               13
cursor authentications                                                    0

SQL> exec p;

PL/SQL procedure successfully completed.

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                22
opened cursors current                                                    3
session cursor cache hits                                                 4
session cursor cache count                                               14
cursor authentications                                                    0


But once again I am confused. Before running p procedure the value of "opened cursors current" is 2. Now to run procedure P , I have to open 2 explicit cursor which ,in turn, will exceed the value of open_cursors which is 3. So when I run procedure P , I was expecting error, but it
did not happen. Why ?

Deba

Thanks for the reply. I have few things to clarify.

1) So from the above, it is clear that, value of "opened cursor current" does not give always true picture. Lets say if the value of open_cursor is 100 and value of "opened cursor current" for a paicular session is 99, then it does not not mean that this session is going to hit ora-01000 as pl/sql cache can reclaim space by itself. It may hit or may not. Am I right ?

2) Let see the another test case where value of "opened cursor current" gets more than value of open_cursor.

SQL> show parameter open_cur

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
open_cursors                         integer
3
SQL> SELECT name,value FROM v$statname s, v$mystat m
  2  WHERE name LIKE '%cursor%' AND m.STATISTIC#=s.STATISTIC#;

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                18
opened cursors current                                                    2
session cursor cache hits                                                 3
session cursor cache count                                               13
cursor authentications                                                    0

SQL> exec p;

PL/SQL procedure successfully completed.

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                22
opened cursors current                                                    4
session cursor cache hits                                                 4
session cursor cache count                                               14
cursor authentications                                                    0

SQL> exec p(true);

PL/SQL procedure successfully completed.

SQL> /

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
opened cursors cumulative                                                26
opened cursors current                                                    6
session cursor cache hits                                                 5
session cursor cache count                                               14
cursor authentications                                                    1

SQL> show parameter open_cur

NAME                                 TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
open_cursors                         integer
3

Here the explanation is that 2 cursors were open initially. Then in 1st run it did open another 2 more cursors ( must be closing any previous one because it can not exceed the value 3 ). Then in 2nd run everything got from acche. Then in 3rd run, it did another 2 more cursor.

But how this is possible ? we can open at the maximum 3 cursors in one session because the value of open_cursors is 3. Could you please explain this ?

Thanks
Deba

Hi Tom,

I have forgot to paste teh code of teh procedure in the above post. The code is as follows :

cursor c4 is select 4 from dual;
begin
open c1;
open c2;

if (p_more) Then
open c3;
open c4;
close c3;
close c4;
end if;

close c2;
close c1;
end;
/

Please consider this post as the continuation of the above.

Thanks
deba

Hi Tom,

Thanks for the reply. I have seen some threads about "_close_cached_open_cursors" in some sites. They are changing the value of this to TRUE to disable pl/sql cursor caching in order to resolve the problem of Ora-01000.

This is not clear to me. What I have uderstood from the above examples and your explanation that pl/sql cache does not cause ora-01000 because if possible then Oracle can reclaim space from pl/sql cache if required, otherwise Oracle does not do that ( Oracle can not ignore the open cursor which is valid ). So disabling cache cursor will not resolve the problem, rather disabling cache cursor will cause performance degradation. Am I right ?

Please let me know your valuable input about this ( though I know that you are always against chaning any undocumented parameter ).

Thanks
Deba

Hi Tom

look - you have (probably a java program) leaking cursors - dump v$open_cursor

I have a similary problem. In a java based application I have some sections of code where bind variables were not used and above all they are used in a for loop. When some time there are to many rows than i got Open cursors exceeded error. I know we have to resolve hard parsing problem but how can i detect a sql statment which causes the open cursor message.

what do you mean by dump the v$open_cursor

regards
Amir Riaz

Hi tom

How can i find the last executable statement which caused the open_cusor parameter exceed.

We have an application running on Weblogic that will return ORA-01000 every few weeks.

open_cursors is set to 1500.

I'm using the query below to find the cursor leak. I run it a few times every day and check the SQL that come to the top.

select o.hash_value, o.dups, sql_fulltext
from v$sql s,
(select hash_value, count(*) dups
from v$open_cursor
where user_name = 'COLLAB'
and sid in (133, 139)
group by hash_value) o
where o.hash_value = s.hash_value
order by o.dups desc;

The SID come from the db sessions created by the connection pool. I choose the sessions to monitor by running this other query, that floats to the top the sessions with many open cursors:

select c.username,
a.sid,
sum(a.value) "opened cursors current"
from v$sesstat a, v$statname b, v$session c
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and c.sid = a.sid
and c.username = 'COLLAB'
group by c.username, a.sid
order by c.username, sum(a.value) desc;

Tom, please let me know if you see any dumb mistakes or ways to improve this query.

Thanks.

Hi tom,
I have set 2000 for open_cursor.
In prod env with 8 million record i am getting ORA-01000: maximum open cursors exceeded.
As you suggested to resolve the ORA-01000 error, i need to set HOLD_CURSOR=NO and RELEASE_CURSOR=YES. This way, after the cursors are used, Oracle will free up the memory for other cursors.
But i want to know whether these values are set by Application developer in there code or i as a DBA need to set these value in some ora file.
Also please can you suggest value for MAXOPENCURSORS with this number of records
Please let me know in either case where these values are set & how.
I will be highly thankful to you

Refer to your comments on 17-Sep-2005:

"thousands of static cursors declared in the package body spec of body outside of a procedure/function that are opened but never closed."

Would you give me an example to explain this? Do yo mean that a cursor declared in the package also opened?

Thanks,
David

Why do people use quotation marks around text and then not use copy and paste?
The quote above should be :
"THOUSANDS of static cursors declared in tha package spec of body outside of a procedure/function that are opened but never closed."

Tom has typo'd "tha" and above reader has corrected the typo and expected Tom to find the quote.

Tom, you said open_cursors will have 50 open cursors at the max. But in my system the parameter shows open_cursors integer 1000. which means I can open 1000 cursors same time. Is it true?

Hi Tom,
I really appreciate for giving us information on open cursor issues. We have an application which needs to go to production but in the test environment we are easily reaching the maximum open cursors. It haven't given us ORA-ERROR but we are sure that it will happen soon. Our application has code something like this

Ps1 = null
Ps2 = null
Ps3 = null
Rs1 = null
Rs2 = null
Rs3 = null
try{

Ps1 = con.prepareStatment(query1)
Rs1 = Ps1.executeQuery()
while(Rs1.next())
{
Ps2=con.prepareStatement(query2)
Rs2=Ps2.executeQuery()

Ps3=con.prepareStatement(query3)
Rs3=Ps3.executeQuery()
}
}
catch(){}
finally{
Ps1,Ps2,Ps3,Rs1,Rs2,Rs3,con CLOSE
}

Is something wrong in this code.Sorry if I miss-spelled something wrong.

Hi Tom,

I am getting this error in java application even i was closed all the ResultSet.

Can you please tell me your comments.

Hi Tom,

open_cursors : 1200

Below is the cursor used in package which run in parallel 90 streams.The total execution time is 15 min .What i have seen below query take 6-7 min.And operation is showing buffer busy waits.Around 40,000 rows are there.we don't gather stats on this table as it get insert and delete in batch process itself.IS there any way to get better performance ????

CURSOR XFST_cursor IS
SELECT ln_txndata(cod_acct_no,
cod_prod,
ref_txn_no,
ref_usr_no,
cod_txn_mnemonic,
cod_txn_literal,
cod_drcr,
cod_sc,
cod_cc_brn,
dat_txn,
dat_post,
dat_value,
cod_bank,
cod_to_acct_no,
cod_routing_no,
ref_chq_no_txn,
ref_doc_no,
txt_txn_desc,
cod_cc_brn_txn,
cod_userno,
cod_auth_id,
ctr_batch_no,
ref_sys_tr_aud_no,
ref_sub_seq_no,
ref_txn_no_org,
ref_usr_no_org,
amt_txn_acy,
amt_txn_tcy,
amt_txn_lcy,
rat_conv_tclcy,
rat_conv_aclcy,
cod_acc_ccy,
cod_txn_ccy,
'V',
cod_cust)
FROM xf_st_ln_input_txn
WHERE dat_value <= l_dat_proc_ln
AND cod_acct_no > m_ln_eod_restart.cod_acct_no -- check this condition as acct no is character
AND (((flg_credit = 'Y') AND
(trunc(cod_txn_mnemonic / 100) IN (65, 67))) OR
(trunc(cod_txn_mnemonic / 100) NOT IN (65, 67)))
AND cod_prod BETWEEN LN_GLOBAL.cod_prod_ln_from AND
LN_GLOBAL.cod_prod_ln_to
AND flg_process = 'N'
AND EXISTS
(SELECT 1 FROM ln_btp_stream
WHERE cod_acct_no = xf_st_ln_input_txn.cod_Acct_no
AND cod_stream_id = pi_stream_id
AND flg_processed = 'N')
ORDER BY cod_acct_no, dat_value, dat_txn;

Hi Tom,

I have no idea why the value has been set to 1200(need to discuss with DBA).Though we have some process which run in 160 parallel streams.is it very bad for performance???? What will be the ideal value ???

ln_txndata is an object type

table xf_st_ln_input_txn have max 40,000 rows.

Unfortunately don't have tkprof.

we have only read access to production so we cant see the plan as well.

32 cores CPU

Yes the only process running

how i can check IO bandwidth ???

how we can know leaks cursors ??

we have 2 nodes.So total cpu is 64.

The reason behind setting open_cursor to 1200 is because we have 1000 user which can generate report.And we use crystal for report generation which excute procedure(open cursor) in DB based on session. And there is possible that it can reach to 1000.

Now question is what is the other way to stop cursor leak in batch process without changing the value of open_cursor ????

Hi Tom,

How i can check if hits "max open files in the OS" during the batch process is running ??

is there any suggestion to change the code so that it will not leak cursor???

The logic behind of running parallel stream for some process in 180 or some process on 60 by reading the SAR.If during the period of batch process if ideal process less than 80% then we increase the stream.And we do node binding as well.let say 60 stream out of which 30 will run in instance 1 and remaining 30 will run in instance 2.

Thanks alot Tom.

Hi Tom,

Thanks alot .Yes i am programmer but unfortunately i don't how to correct it.though we don't have any problem except performance issue.

I am uing Java code to process thousands of files and each file requires a PL/SQL call. Each PL/SQL call appears to create a new cursor.
1. Closing the prepared statement I am using to execute the PL/SQL call, does not appear to release its associated cursor.

2. Moving the statement out of the file processing loop (declaring it once and using it many times - using bind variables), does not appear to reduce the number of cursors created in the session.

3. If I encounter an exception, I still need to continue processing the remaining files using the prepared statement. One thought was to close the statement, set it to null and then reopen it in the exception handler.

Example:

String callStmt = "begin " + " insert_xml" + "(?,?,?); end;";
CallableStatement stmt = mConn.prepareCall(callStmt);

try {
stmt.setString(1, xmlFile.getParent());
stmt.setString(2, xmlFile.getName());
stmt.setString(3, version);
stmt.execute();
stmt.close();
stmt = null;
}
catch (SQLException sqle) {
// Close the current statement as it goes out of scope in the exception
// This prevents a cursor leak.
if (stmt != null) {
stmt.close();
stmt = null;
}
throw sqle;
}

Apparently I do not understand how to construct a cursor that will be re-used in this context. I attempted to move the creation of the statement outside of the file processing loop. Since I am using bind variables, I expected the statement to be re-used over and over again. But the same number of cursors was generated: one for each call to the PL/SQL procedure.

How can I construct a cursor that will be reused?

Using this forum, I closed the statement in the exception handler. So I WAS leaking a cursor, but now (in the example) I am not. So thank you for that.

The SQL I am using to count cursors is:

select c.sid, c.address, c.hash_value, sql_text, count(*) as "Cursor Copies" from v$open_cursor c group by
c.sid,c.address, sql_text, c.hash_value having count(*) >= 5;

1. I started off with the code constructed similarly to the example I posted (without the exception handler). That code is called in the file processing loop. So the statement is parsed multiple times and each call to insert_xml() creates a new cursor. This is clearly the wrong way to do this. When processing 507 files, I end up with 507 cursors.

2. So I changed the code to parse the statement once outside of the file processing loop. Inside the loop I use the statement, calling it many times. I was expecting the statement to be re-used. But I still ended up with 507 cursors. So I switched the code back and posted my original question.

3. The SQL text each cursor is: insert into xml_map( load_date, doc_name, location, ent (I assume v$open_cursor.sql_text is truncated). The cursor_type = OPEN-RECURSIVE. Believe it or not, that SQL has nothing to do with the call to to insert_xml(). So apparently the call to the PL/SQL procedure is not the culprit.

Thank you for forcing me to validate my results. I am posting this, embarassing though it may be, as an example of using v$open_cursor to locate cursor problems.

I am a bit puzzled that changing the code did not seem to make a difference to the overall cursor count. Is that because it is a PL/SQL call?

The code in the anonymous block is:

CREATE OR REPLACE procedure insert_xml(pDir varchar2, pName varchar2, pVersion varchar2) as

lCreateDirectory varchar2(512) := 'create or replace directory XMLDIR as ''' || pDir || '''';

begin
execute immediate lCreateDirectory;
if pVersion = '1.0.1' then
insert into 101_xml values (xmltype(BFILENAME('XMLDIR',pName),nls_charset_id('AL32UTF8')));
elsif pVersion = '1.0.2' then
insert into 102_xml values (xmltype(BFILENAME('XMLDIR',pName),nls_charset_id('AL32UTF8')));
end if;
end;

The code in the anonymous block was not the problem. As you pointed out, the leak was coming from somewhere else in the code. Using the v$open_cursor query, I found the leak and corrected it by doing exactly what you suggested: moving the offending statement out of the loop, parsing it once, and calling it many times with bind variables.

Questions:
1. The call to the anonymous block (the call itself - not necessarily what it does), flawed as you noted because it was being called repeatedly by a statement in a loop and parsed each time, does not appear to create a cursor. This is why moving it around (my attempts at improving the code) did not affect the number of cursors. So calls to PL/SQL procedures from Java do not necessarily create cursors? SQL statements create cursors?

2. Let's assume that instead of calling an PL/SQL procedure I was executing a SQL statement. What if I was using the statement in the file processing loop? I expect exceptions to occur as I process files. Do I need to close the statement in the exception handler? Closing the statement inside the loop would be a problem unless I re-opened it. So in an exception situation, does an unclosed statement that exeutes a SQL statement always go out of scope and cause a cursor leak? Or is the point really to make sure that you close statements when you are done with them?

ops$tkyte%ORA11GR2> declare
  2          n number := dbms_utility.get_cpu_time;
  3  begin
  4          for i in 1 .. 1000
  5          loop
  6                  execute immediate 'create or replace directory mydir as ''/tmp'' ';
  7          end loop;
  8          dbms_output.put_line( (dbms_utility.get_cpu_time-n) || ' cpu hsecs' );
  9  end;
 10  /
421 cpu hsecs

PL/SQL procedure successfully completed.

Elapsed: 00:00:04.33
ops$tkyte%ORA11GR2> 

1) it is a cursor - everything is. You are closing it, that is why you don't see it.

So in the example code the statement get closed each time. When I moved the statement out of the loop, the cursor was re-used. In both cases the cursor count would not increase.

2) You don't want to leak a cursor - so you would tend to close it in an exception handler if you were going to close it during normal completion of the code - yes.

But in this case, a single exception on a given file should not stop the processing of sub-sequent files. So should the statement be closed and then re-opened in the exception handler?

3) It does a COMMIT!!! for heavens sake - it does a commit!!!!! did you know that?

Obviously not. Thanks.

4) it is a huge security, a HUGE security hole

I inheirited this code. I will rectify that.

prepare statment

loop
begin
   do something
exception
   deal with it
end 
end loop
close statement

Tom,

I am not a DB guy primarily, but my application uses the Oracle DB.

We have taken care in our Java code to close all ReultSets and Statements after their use. Yet, we have today got the Max Cursors Exceeded error.

I was wondering if the Connection object also needs to be released - the concern is that the Connection objects are being re-used, so why release them? Also, does and open connection indicate an open cursor?

Kindly let me know, since my application is in production and we need to ensure it does not go down.

Thank,
Chetan

try
{
   prepare
   bind
   execute
   close
}
exception
{
   print out a message saying "hey guys, we had an exception"
}

Tom,

I'm trying to demonstate getting a cursor leak ("ORA-01000: maximum open cursors exceeded") error using only sqlplus and PL/SQL (not Java, Pro-C, .NET, etc.).

My first attempt below didn't work (it finished successfully without getting the error), but I suspect it is because I'm just implicitly closing the cursor on the next iteration of the loop, when the same variable is being reassigned with the next open cursor.

1) That would explain why I'm not getting the ORA-01000, correct?

2) How would you code a demonstration of getting the ORA-01000 error with only PL/SQL?

Thanks,
Jack


SQL*Plus: Release 10.2.0.3.0 - Production on Tue Oct 1 20:47:27 2013

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.

Enter password: 

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> show parameter cursors

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
open_cursors                         integer     300
session_cached_cursors               integer     50

SQL> !cat max_cursors.sql
SET SERVEROUTPUT ON SIZE UNLIMITED FORMAT WRAPPED
DECLARE
  TYPE rc IS REF CURSOR;
  lc_cursor   rc;
  ln_count    PLS_INTEGER := 0;
BEGIN
  FOR i IN 1 .. 1000 LOOP
    ln_count := ln_count + 1;
    OPEN lc_cursor
     FOR 'select * from dual xx';
    --CLOSE lc_cursor;
  END LOOP;
  dbms_output.put_line ('Finished (loop count: ' || ln_count || ')');
EXCEPTION
  WHEN OTHERS THEN
    dbms_output.put_line ('(occurred at loop count: ' || ln_count || ')');
    RAISE;
END;
/

SQL> @max_cursors.sql
Finished (loop count: 1000)

PL/SQL procedure successfully completed.