Solved

ORA-1037 Maximum cursor memory exceeded

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

758 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

19 Experts available now in Live!

Get 1:1 Help Now