Solved

ORA-1037 Maximum cursor memory exceeded

Posted on 2000-05-05
4
516 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

730 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