What Oracle setting prevents open cursors from closing?

1
votes

I have two Oracle 11G installations. I can run a script that imports a 37MB database into one of them with no problems (installation A). In the other (installation B), when I run the exact same script with the exact same file it gets "ORA-01000: maximum open cursors exceeded". I increased the max open cursors to 20,000 but when the script gets to row 20,000 then it stops with the same ORA-01000 error. The installation that is working has max open cursors set to 300.

Obviously there is no problem with the script or the SQL because it works in one Oracle database. So there must be a setting in the other Oracle instance that prevents open cursors from closing. What could it be?

Installation A works. Database = Oracle Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options. Driver = Oracle JDBC driver 11.2.0.1.0.

Installation B does not work. Database = Oracle 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. Driver = Oracle JDBC driver 11.2.0.3.0.

1
oraclecursor
I think the question should be: "Why does Oracle opens a new cursor for (apparently) each record? You can compare all Oracle parameters with this view SELECT * FROM V$PARAMETER. Have a look at Oracle parameter cursor_sharing – Wernfried Domscheit
Hi, Can you please describe how were you able to solve this problem. This will be of great help. we are also in a similar situation. Thanks in advance. – Ajai Gopal
It has been a long time. I can't remember how it was solved. – Rubicksman

1 Answers

0
votes

I would recommend checking what cursors are open in a session. If you see 10.000 statements, all alike, with open cursors, there is something wrong in the code.

I myself find the easiest way to do this:

connect sys

alter system flush shared_pool; /* Removes everything can be finished, the trees hinder my view of the forest. */

/* Overall overview. */
select *
from v$sqlarea

/* Open cursors per session. */

select *
from v$open_cursor