Solved

ORA-1037 Maximum cursor memory exceeded

Posted on 2000-05-05
4
513 Views
Last Modified: 2010-05-18
A different organization's Oracle DB is signalling this error (I can't find out the version or anything). The query has worked fine for months and I suspect they changed a parameter and would love to be able to get back to their system management saying "hey your people changed such and such and then look what happens..."

Does anyone know if a particular parameter changes this? What could have happened that would cause this of a sudden?
0
Comment
Question by:rkogelhe
  • 2
4 Comments
 
LVL 3

Author Comment

by:rkogelhe
ID: 2781553
BTW: The reason I can't figure things out is that the connection seems to be through an ODBC driver called "Broadbase".
0
 

Accepted Solution

by:
manager43 earned 150 total points
ID: 2782773
Cause:

An attempt was made to process a complex SQL statement that consumed all available memory of the cursor.

Action:

Simplify the complex SQL statement.

Explanation:

The ORA-1037 error is issued when the server tries to create too many
frames while building a shared cursor. This limit is hard-wired into the code at 32k frames. This number should be large enough to execute just about any query. (Most statements generate 1 to 20 frame segments on average; any statement that generates frame segments in the
thousands should be examined to determine why.)

Diagnosis:

- Capture all VIEW, SYNONYM etc definitions involved in the statement.

- Isolate the statement into SQLPLUS and see if it failes there.

- See if PQO is involved. Try to eliminate it if possible.

- Are any of the following in use:

Partition Views with large numbers of partitions,
Bitmap indexes
Large inlists
0
 
LVL 1

Expert Comment

by:Ammar
ID: 2783735
Caused :
Your application trying to open private SQL areas ....

Diagnosis :
Apply the below command
select NAME,VALUE
from
 v$parameter
where
NAME like 'open_cursors%';

which will display the current value of Open_cursors parameter.
50 it the default of this parameter..so increase the value..
0
 
LVL 3

Author Comment

by:rkogelhe
ID: 2795742
Thanks,

I think it turned out to be caused by a poorly explained partition -and- a large number of 'in' values.

Regards,

Ryan
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  â€¦
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now