Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 527
  • Last Modified:

ORA-1037 Maximum cursor memory exceeded

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
rkogelhe
Asked:
rkogelhe
  • 2
1 Solution
 
rkogelheAuthor Commented:
BTW: The reason I can't figure things out is that the connection seems to be through an ODBC driver called "Broadbase".
0
 
manager43Commented:
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
 
AmmarCommented:
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
 
rkogelheAuthor Commented:
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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now