Solved

ORA-1037 Maximum cursor memory exceeded

Posted on 2000-05-05
4
518 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

Enroll in June's Course of the Month

June's Course of the Month is now available! Every 10 seconds, a consumer gets hit with ransomware. Refresh your knowledge of ransomware best practices by enrolling in this month's complimentary course for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

734 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